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

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

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: