Working with Date and Time : Sql Server

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

[code language="sql"]
SELECT
CONVERT(VARCHAR(8) , GETDATE() , 108) AS HourMinuteSecond,
CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008
[/code]

Get Time in Hour:Minute - 12 Hours Format

[code language="sql"]
SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime
[/code]

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


Get Date

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

Get Date Time

[code language="sql"]
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]
[/code]

Happy Coding :)