Archive

Posts Tagged ‘First day week’

SQL Tip #2 How to Find First Day of Week /Month/Quarter/Year – How is work actually?

March 16, 2016 Leave a comment

TSQL Tip:-How to Find First Day of Week /Month/Quarter/Year – How is work actually

We come across always to find first day of Week /Month/Quarter/Year.

We simply google it and find the result and modify the result given by google as per our requirements.

Do we check how actually it works in TSQL?

Let’s first find the First Day of Week/Month/Quarter/Year, and then we analyze how it works.

First Day of Week:-

SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0) FirstDayOfWeek

FirstDayOfweek

How it works?

First we applying DATEDIFF(WEEK,0,GETDATE()), which gives number of week from today’s date to ‘0’ Date.

What is ‘0’ Date, by default when we put 0 then it converts into date as ’01/01/1900′

We can test by executing below statements:-

ZeroConversation

In above screen both queries giving same number of weeks.

After getting number of weeks we will add same number of weeks into 0 , i.e. date ’01/01/1900′

FirstDayOfweek_Breakdown

Anything added to first day of default date based on week then it will give first day week of the current week.

Let’s do same thing for Month.

Month Difference:-

MonthDifference

After difference add 1394 months into 0 or ‘01/01/1900’

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)FirstDayOfMonth1

OR

SELECT DATEADD(MONTH,DATEDIFF(MONTH,’01/01/1900′,GETDATE()),0)FirstDayOfMonth2

FirstDayof Month

Same logic applied on Quarter and Year

First Day of quarter

SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,GETDATE()),0)FirstDayOfQuarter1

OR

SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,’01/01/1900′,GETDATE()),0)FirstDayOfQuarter2

FirstDayofQuarter.png

 

First Day of Year

SELECT DATEADD(Year,DATEDIFF(Year,0,GETDATE()),0)FirstDayOfYear1

OR

 

SELECT DATEADD(Year,DATEDIFF(Year,’01/01/1900′,GETDATE()),’01/01/1900′)FirstDayOfYear2

FirstDayofYear

No more google 🙂 It will help to remember when we know how it works 🙂

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Advertisements