SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Working with Date and Time : Sql Server

on July 4, 2012

Get Time in Hour:Minute or Hour:Minute:Second format (24 hours)

SELECT
  CONVERT(VARCHAR(8) , GETDATE() , 108) AS  HourMinuteSecond,
  CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
  CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008

Get Time in Hour:Minute – 12 Hours Format

 SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime

Reference:http://pcunleashed.com/sql-server/get-12-hour-time-from-datetime-datatype-in-sql-server-2005/

Get Date

  SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY], --US Format
  CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] --British / French Format

Get Date Time

SELECT CONVERT(VARCHAR(20), GETDATE(), 100) [Mon DD YYYY HH:MIAM (or PM)]
, CONVERT(VARCHAR(26), GETDATE(), 109) [Mon DD YYYY HH:MI:SS:sssAM (or PM)]
, CONVERT(VARCHAR(24), GETDATE(), 113) [DD Mon YYYY HH:MM:SS:sss(24h)]
, convert(varchar, getdate(), 120) [yyyy-mm-dd hh:mm:ss(24h)]
, convert(varchar, getdate(), 121) [yyyy-mm-dd hh:mm:ss.mmm]

Happy Coding 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: