first Monday of Week/Month & Next Monday of given date

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

– 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:

– 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)

How to extract day/month/year from a DateTime column

You can do this using two different ways. First is to us DAY(), MONTH() an YEAR() TSQL functions. These functions return an integer representing a day/month or year respectively.
These can be used as:

SELECT      DAY  ( GETDATE() ) AS 'Day',

            MONTH( GETDATE() ) AS 'Month',

            YEAR ( GETDATE() ) AS 'Year'
Result Set:

Day         Month       Year

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

19          5           2011

(1 row(s) affected)

Another way is to use DATEPART() TSQL function. The DATEPART() function can also extract week, hour, minute, second in addition to day, month and year. For a full list of parts that can be extracted using DATEPART() refer BOL.
We can use DATEPART() to extract parts as below:

SELECT      DATEPART(DAY,   GETDATE()) AS 'Day',

            DATEPART(MONTH, GETDATE()) AS 'Month',

            DATEPART(YEAR,  GETDATE()) AS 'Year',

            DATEPART(HOUR,   GETDATE()) AS 'Hour',

            DATEPART(MINUTE, GETDATE()) AS 'Minute',

            DATEPART(SECOND, GETDATE()) AS 'Second'
Result Set:

Day         Month       Year        Hour        Minute      Second

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

19          5           2011        21          6           5

(1 row(s) affected)
Personally I prefer to use DAY(), MONTH() an YEAR() functions as it only need one argument and easier to type… However, when we need to extract hour, minute and so on we need to use DATEPART() function.


To calculate the difference between two dates, you can use DATEDIFF() function. The DATEDIFF() function returns the number of days/month/years and time between two dates.
Syntax:
DATEDIFF (date part, start date, end date)

For example, to calculate time left till Independence Day, you can use:

DECLARE     @Today      DATETIME = GETDATE()

DECLARE     @IDay       DATETIME = '2011-08-15 08:30:00'

SELECT DATEDIFF(DAY, @Today, @IDay),      'Days Left'

UNION ALL

SELECT DATEDIFF(MONTH, @Today, @IDay),    'Months Left'

UNION ALL

SELECT DATEDIFF(YEAR, @Today, @IDay),     'Years Left'

UNION ALL

SELECT DATEDIFF(QUARTER, @Today, @IDay),  'Quarters Left'

UNION ALL

SELECT DATEDIFF(HOUR, @Today, @IDay),     'Hours Left'

UNION ALL

SELECT DATEDIFF(MINUTE, @Today, @IDay),   'Minutes Left'

UNION ALL

SELECT DATEDIFF(SECOND, @Today, @IDay),   'Seconds Left'
Result Set:

———– ————-

86          Days Left

3           Months Left

0           Years Left

1           Quarters Left

2052        Hours Left

123122      Minutes Left

7387278     Seconds Left

(7 row(s) affected)

-- SQL Server 2008 T-SQL find next Monday for a given date

DECLARE @Date DATE = '2014-12-31'

SELECT NextMondaysDate=CONVERT(DATE, DATEADD(dd, (DATEDIFF(dd, 0, @Date)

                                     / 7 * 7) + 7, 0)),

WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @Date)/7*7)+7, 0));