Find last Friday in SQL Server 2008

Microsoft SQL Server has very rich tools for date and time but just due to unawareness people find it difficult to make operation on datetime in SQL Server and used to go to front-end languages like .NET. I used to prefer SQL Server for performance issue. So far I have written quite a few articles on date time operation in SQL Server which is as follow.
If you will go through above given articles, you will come to know that datetime functions are really very handy and useful. Anyway, the main purposes to write this article to show you one another very small T-SQL for find out last Friday of the day.
SELECT DATEADD(day, (DATEDIFF (day, ‘19800104’, CURRENT_TIMESTAMP) / 7) * 7, ‘19800104’) as ‘Last Friday’



It was really very easy. Right?
Actually the logic behind this small query is, there was a Friday on 4th Jan 1980 so I am getting difference between 4th Jan 1980 to current date, dividing it with 7 than multiplying with 7 and whatever number comes, I am adding it to the same date and I will get last Friday.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Advertisements

Find last Friday in SQL Server 2008

Microsoft SQL Server has very rich tools for date and time but just due to unawareness people find it difficult to make operation on datetime in SQL Server and used to go to front-end languages like .NET. I used to prefer SQL Server for performance issue. So far I have written quite a few articles on date time operation in SQL Server which is as follow.
If you will go through above given articles, you will come to know that datetime functions are really very handy and useful. Anyway, the main purposes to write this article to show you one another very small T-SQL for find out last Friday of the day.
SELECT DATEADD(day, (DATEDIFF (day, ‘19800104’, CURRENT_TIMESTAMP) / 7) * 7, ‘19800104’) as ‘Last Friday’



It was really very easy. Right?
Actually the logic behind this small query is, there was a Friday on 4th Jan 1980 so I am getting difference between 4th Jan 1980 to current date, dividing it with 7 than multiplying with 7 and whatever number comes, I am adding it to the same date and I will get last Friday.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Convert SQL Server date to GMT + 5.30 in SQL Server 2008/2005

Today one of my subordinate asked me one question about how can we convert GETDATE() to Indian time as our server is hosted in USA. Question is very simple and bit difficult for novice that is why I thought to put it on blog so everybody else can get benefit if they doesn’t know.
Well rather than GETDATE(), I would prefer UTC datetime. Just find the different between your time zone and UTC timezone and add that time to UTC time and you will get your preferred time of your zone.
First I run following T-SQL in our development server in India to find exact time difference between UTC time and Indian time zone.
SELECT DATEDIFF(minute, GetUTCdate(), GetDate());


It will give you 330 minutes, add this 330 minutes to UTC time and you will get Indian time. If already know hours different between your timezone and UTC time, you directly convert it to minutes, don’t need to run above T-SQL.
Now Add 330 minutes to your UTC time with below T-SQL and you are done.
SELECT DATEadd(minute, 330,GetUTCdate()) as indianTime, GETDATE() as serverTime;


Quite an easy task!!! Isn’t it???
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

DateADD function for Quarter in SQL Server 2005

Recently I have read few questions in forums about limitations of DATEADD function. Support I have one date 30-Mar-09 and I want to find last quarter of it. What should I do?

select CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-30’),106)

above query will display December as last quarter but what If I enter date like 10-Jan-09?

select CONVERT(VARCHAR,dateadd(q, 1, ‘2009-01-10’),106)

It will still show October as a last quarter of date but my need is, if I enter Jan, Feb or March, my quarter month should be December. In that case, I have found one solution which I am going to share with you. If there is any better alternative, I would like my reader to share it here so that people can use it.

select CASE datepart(m,‘2009-07-28’)

WHEN 1 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 2 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 3 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 4 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 5 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 6 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 7 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 8 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 9 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 10 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

WHEN 11 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

WHEN 12 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

END

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com