Frequently I use SQL with the work I do with Orbis Taskcentre.

There’s some queries which I use quite a lot and have to always look up on the internet or work out how to do, so to save time, here’s my list:

Date Part of Date Time
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

First Day of This Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

First Day of Last Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

First Day of Next Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Last Day of Current Month
DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

Last Day of Last Month
DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Last Day of Next Month
DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),DATEADD(MONTH, 2, GETDATE()))

Select Text Before Space
This also checks to see if a space exists, if there’s no space it returns the full text in order to prevent an error
LEFT(@Column, CASE WHEN charindex(' ', @Column) = 0 THEN LEN(@Column) ELSE charindex(' ', @Column) - 1 END)

Count Workdays Between Two Dates
(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)