Archive

Posts Tagged ‘start day’

How to get first Monday of Week/Month – TSQL

18.05.2011 3 comments

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