Datetime Snippets

Date Parts

DECLARE @dt datetime = GETDATE()
SELECT @dt [datetime],
	   CAST(GETDATE() AS Date) [today_dt],
	   DATEPART(q, @dt) [quarter],
	   DATEPART(wk, @dt) [week_num],
	   DATEPART(isowk, @dt) [ISO_WEEK],
	   DATEPART(dy, @dt) [dayofyear_num],
	   DATEPART(m, @dt) [month_num],
	   DATEPART(d, @dt) [day_num],
	   DATEPART(yy, @dt) [year_num],
	   DATEPART(hh, @dt) [hour],
	   DATEPART(mi, @dt) [minute],
	   DATEPART(ss, @dt) [second],
	   DATEPART(ms, @dt) [millisecond]
;

Get Date Only

SELECT CONVERT(varchar, GETDATE(), 112) DateOnly | Result is: YYYYMMDD

SELECT CAST(CONVERT(varchar, DATEADD(DAY, 0, GETDATE()),112) AS bigint) DateOnly | Result is: YYYYMMDD

Get Prior Date

SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()), -1)

Get Time Only

SELECT CONVERT(VARCHAR, GETDATE(), 8)    | Result is: hh:mm:ss
SELECT CONVERT(VARCHAR, GETDATE(), 14)   | Result is: hh:mm:ss:000
SELECT CONVERT(VARCHAR, GETDATE(), 24)   | Result is: hh:mm:ss
SELECT CONVERT(VARCHAR, GETDATE(), 108)  | Result is: hh:mm:ss
SELECT CONVERT(VARCHAR, GETDATE(), 114)  | Result is: hh:mm:ss:000