Common SQL Queries

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)

Get paid to shop

Dont really like using blogs for advertising, however a few friends and family have not heard of this, so here goes…

Services such as Top Cashback, Quidco etc allow you to get cash back when ever you buy something online.

There’s no guarantee that you will get paid, but every little helps. Typically you get like 5% back off what you buy.

Sometimes you can even get more cash back than your initial investment. For example depositing £25 on William hill got me £57 cash back.

Big wins are things such as sky, virgin media and insurance.

Finally…

This site has been up for many years, so I have decided that it’s about time that this site was used for something interesting…

Hopefully things should appear on here on a more regular basis. Time will tell!