Thursday, July 12, 2012

Get The First and The Last Day of The Month

Just saw a question from someone on a mailing list I join asking for how to get the transactions occurring for the last three months (in other words, three months from current date).


First, you may want to do something simple.
To get the first day and the last day of the month, we can use:

SELECT 
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1


If you play around a little bit, you can also get the first day and the last day of the previous month:

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


To get the transactions for the last three months, we just need to modify the number a little bit.

SELECT
DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1



Then, just select the transactions occurring in between these ranges.

SELECT * FROM TableName WHERE TransactDate BETWEEN
DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0) AND
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1

Share:

2 comments:

  1. brrr.......so binusian sekali.....hebat Steven. ilmu g uda di simpan di underground tingkat 7 tuch alias uda hilang ketelan bumi.....:D

    ReplyDelete
  2. Hahaha, gini lagr, nasib kami rakyat jelata bu, masih kerja sebagai kuli :"> Tergantung kerjaannya juga lagr, karena eke kerjaannya ini, yah mesti maen yang ginian :P

    ReplyDelete

About

Related Posts

Frame cannot Load - Content Security Policy Issue

Problem: You are using iframe to load a site from within a website, and the frame does not load. In my case, I am using https for both the...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Follow by Email