Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > How to get first Monday of Week/Month – TSQL

How to get first Monday of Week/Month – TSQL


Following queries can be used to get First Monday of a week/month:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Monday of Current Week

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

            'Monday of Current Week'

UNION ALL

— First Monday of the Month:

SELECT      DATEADD(WEEK, DATEDIFF(WEEK, 0,

            DATEADD(DAY, 6 DATEPART(DAY, GETDATE()), GETDATE())), 0),

            'First Monday of Current Month'

Result Set:

———————– —————————–

2011-05-16 00:00:00.000 Monday of Current Week

2011-05-02 00:00:00.000 First Monday of Current Month

 

(2 row(s) affected)

You can get the Start of Day and End of Day using below queries:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Living in Today…

SELECT      DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0),

            'Start of Day'

UNION ALL

SELECT      GETDATE(), 'Now'

UNION ALL

SELECT      DATEADD(MILLISECOND, 3,

            DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)),

            'End of Day'

Result set will be:

———————– ————

2011-05-18 00:00:00.000 Start of Day

2011-05-18 21:09:14.887 Now

2011-05-18 23:59:59.997 End of Day

 

(3 row(s) affected)

 

Hope This Helps!

Vishal

  1. joshoa
    20.05.2013 11:56 | #1

    it’s realy helpful!!! 10x a lot:)

  2. harshit upreti
    15.12.2013 12:37 | #2

    hi vishal .. why you did 6- in case of monday ?
    what if i want to calculate other days ?

  3. fionasiah
    29.06.2015 09:50 | #3

    how do you get the first month of next month?

  1. No trackbacks yet.