Thursday, October 21, 2010

Helpful SQL Queries

I was writing few Tsql queries for work where I came across the situation while I had to calculate the first and last day of the current month. So, I looked up some Tsql functions to achieve this.

First Day of the Month:

Firstly, lets find out how many days we are into the current month:

DAY(GETDATE()) -- returns 21 as today is 10/21/2010. So if we were to get the first date of the current month we need to add -20 days to todays date. Notice if we add -21 days it will give us the last date of previous month. 9/30/2010 instead of 10/1/2010. So we get:

DATEADD(d,-DAY(GETDATE()) + 1, GETDATE())

If u need to format and get rid of the time portion then just do convert (us format date 101):

CONVERT(varchar(10),
DATEADD(d,-DAY(GETDATE()) + 1, GETDATE()), 101)


Last Day of the Month:

Using the same methodology I came up with this procedure to calculate last day of the month.
Firstly, lets a add a month to current date:

DATEADD(m, 1, GETDATE()) which should give us the same day of the next month (11/21/2010)

Now find out how many days we are into the month on next month:

Day(DATEADD(m, 1, GETDATE()) ) which gives 21.

Now, if we just subtract 21 from next months date it will give us the last day of this month:

DATEADD(d, [-21], [what we got after adding 1m to today's date]
=>DATEADD(d, -Day(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE()))

to get rid off the time you could do:
CONVERT(varchar(10), DATEADD(d, -Day(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE())), 101)

I know this was too big until I found out about zero date in SQL. If you pass 0 as date in SQL server as starting date, it gives you the beginning of the time ( I meant the beginning of SQL server not universe..LOL). So if I were to call the following:

DATEDIFF(m, 0, GETDATE())

it will return 1329 today(10/21/2010). Notice I passed a 0 instead of a date in the second parameter. So, we can rewrite the above quires as:

First Day of the month:
DATEADD(d,-DAY(GETDATE()) + 1, GETDATE())
or,
DATEADD(m,DATEDIFF(mm, 0, getdate()), 0)

Last Day of the month:
DATEADD(d, -Day(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE()))
or,
DATEADD(s, -2, DATEADD(m,DATEDIFF(mm, 0, getdate()) + 1, 0))