SQL: All about Time

SQL Server

How to add time into a datetime variable?

[sql]
declare @startDate = ’05 Nov 2017′
SET @startDate = DATEADD(week, 3, @startDate); –’22 Nov 2017′
–SET @startDate = DATEADD(month, 2, @startDate); –’05 Feb 2017′
–SET @startDate = DATEADD(year, 1, @startDate); –’05 Nov 2018′
[/sql]

How to select day of week from a DATETIME?

[sql]
SELECT DATEPART(dw,’2017-07-23 00:00:00′)
— 1 Sunday
— 2 Monday
— 3 Tuesday
— 4 Wednesday
— 5 Thursday
— 6 Friday
— 7 Saturday
[/sql]

How to get date difference between two DATETIME?

[sql]
select datediff(dd, ‘2017-01-01 00:00:00’, ‘2017-01-04 00:00:00’)
— 3
select datediff(dd, ‘2017-01-01 00:00:00’, ‘2017-01-05 00:00:00’)
–4
[/sql]

How to work with “working day” excluding weekend?

[sql]
WITH AllDates AS
( SELECT TOP (DATEDIFF(DAY, ‘Jan 01 2017’, ‘Jan 31 2017’))
D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), ‘Jan 01 2017’)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b )
SELECT * FROM AllDates WHERE DATEPART(WEEKDAY, D) NOT IN (6, 7)
[/sql]

How to convert to full name month?

[sql]
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MONTH, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
[/sql]

Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

[sql]
select CONVERT(datetime, ’25/10/2017′, 102) as action_date –error
[/sql]
This is because 102 format is yyyy/mm/dd but the input date time ’25/10/2017′ format is dd/mm/yyyy
[sql]
–yy.mm.dd
select convert (varchar,[date],102) from Test_Date

–dd/mm/yy format
select convert (varchar,[date],103) from Test_Date

–dd-mm-yyyy format
select convert (varchar,[date],105) from Test_Date

–mm/dd/yyyy format
select convert (varchar,[date],101) from Test_Date

–mm-dd-yyyy format
select convert (varchar,[date],110) from Test_Date
[/sql]

[sql]
select CONVERT(datetime, ’10/25/2017′, 102) as action_date –works
[/sql]

Get last day of month

[sql]
— get last day of this month
select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()) + 1,0))
[/sql]
[sql]
— get last day of 3 months ago
select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()) – 2,0))
[/sql]

Get previous day

[sql]
— Get previous 30 days
GetDate() – 30
[/sql]