There are innumerable numbers of questions asked in different forums about datetime concepts. I found so many replies using the functions such as Cast, Convert, Month () and Year () in where clause. There is a disadvantage with these functions. If the datetime column has index, these functions will not utilize the indexes properly, which often results in index scans. We can avoid these scans and replace with seeks, if the queries are properly written. Some queries will use functions such as DateName, DatePart etc, which will depends on regional settings and DateFirst value.
In general, by using calendar table, we can solve these questions very simply. However, if we don’t have such table in database, it will become quite difficult in some queries. Here I am presenting the top 10 questions, which will be asked very frequently and the solutions for those questions using Calendar table and without using Calendar table. Before using any of these queries, which uses the calendar table, please run the below script initially in your database, which creates a calendar table.
CREATE TABLE auxcalendardates
(
caldate DATETIME
);
WITH n
AS (SELECT 0 AS num
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9),
numbers
AS (SELECT Row_number() OVER (ORDER BY (SELECT NULL)) AS NUMBER
FROM n n,
n n1,
n n2,
n n3,
n n4,
n n5,
n n6)
INSERT INTO auxcalendardates
SELECT Dateadd(DAY, NUMBER - 1, '1/1/1753')
FROM numbers
WHERE NUMBER <= 3012154
Here are the top 10 questions
- First date of Current Month
- Nth Weekday in current Month
- 1st Business day of Current Month
- Next N Business days
- Constructing a date with given Year,Month and day
- Year's first and Last dates.
- Given Year and Month's First and Last Dates
- Next weekday from today
- Stripping the Time portion
- Working days between 2 dates
1. First date of Current Month
select DATEADD(MOnth,datediff(Month,0,getdate()),0)
2. Nth Week day in Current Month
This will return the nth weekday of the current Month. This will takes 2 parameters, Week number and Week Day. Week day will take 0-6 as input values (0 – Monday, 1 – Tuesday, 2 – Wednesday, 3- Thursday, 4-Friday, 5- Saturday and 6- Sunday).
DECLARE @N INT = 1
DECLARE @Weekday INT = 0
--Without using Calendar Table
SELECT Dateadd(DAY, ( 7 + @Weekday - Datediff(dd, 0, nthweekbegin)%7 ) % 7,
nthweekbegin)
FROM (SELECT Dateadd(DAY, ( @N - 1 ) * 7, Dateadd(MONTH, Datediff(MONTH, 0,
Getdate()
), 0))
AS nthweekbegin)t
--Using Calendar Table
;WITH cte
AS (SELECT caldate,
Datediff(dd, 0, caldate) % 7
AS wkday,
Row_number() OVER (PARTITION BY Datediff(dd, 0, caldate)%7 ORDER
BY
caldate) AS
rn
FROM auxcalendardates
WHERE caldate >= Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0)
AND caldate < Dateadd(MONTH, Datediff(MONTH, 0, Getdate()) + 1,
0))
SELECT caldate
FROM cte
WHERE rn = @N
AND wkday = @Weekday
3. 1st Business day of Current Month
This will return the first business day, i.e. (Which is not Saturday and Sunday) of the current month.
--Without using Calendar Table
SELECT Dateadd(DAY, CASE
WHEN ( Datediff(dd, 0, firstday)%7 ) IN ( 5, 6 ) THEN 7 -
Datediff(dd, 0, firstday)%7
ELSE 0
END, firstday)
FROM (SELECT Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0) AS firstday)t
--Another way without using Calendar Table
SELECT Dateadd(DAY, CAST(Substring('0000021', ( Datediff(dd, 0, firstday)%7 ) +
1, 1)
AS INT), firstday)
FROM (SELECT Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0) AS firstday)t
--With using Calendar Table
SELECT TOP 1 caldate
FROM auxcalendardates
WHERE caldate >= Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0)
AND Datediff(dd, 0, caldate) % 7 <= 4
ORDER BY caldate
4. Next N Business days
This will return the Next N Business days, i.e. (Which are not Saturdays and Sundays) from today.
DECLARE @NoofWorkingDays INT = 7;
WITH n
AS (SELECT 0 AS num
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9),
numbers
AS (SELECT Row_number() OVER (ORDER BY (SELECT 1)) AS num
FROM n n1,
n n2,
n n3,
n n4)
SELECT TOP (@NoofWorkingDays) Dateadd(DAY, num, Datediff(dd, 0, Getdate()))
FROM numbers
WHERE ( Datediff(dd, 0, Getdate()) + num )% 7 <= 4
--By using Calendar Table
SELECT TOP (@NoofWorkingDays) caldate
FROM auxcalendardates
WHERE caldate > Datediff(dd, 0, Getdate())
AND Datediff(dd, 0, caldate) % 7 <= 4
ORDER BY caldate
5. Constructing a Date with given Year, Month and Day
This will take 3 parameters, Day, Month and Year and will return the datetime.
DECLARE @Day INT = 23
DECLARE @Month INT = 12
DECLARE @Year INT = 2010
SELECT Dateadd(DAY, @Day - 1, Dateadd(MONTH, @Month - 1 + ( @Year * 12 - 22800 )
, 0))
6. Given Year’s First and Last Dates
This will take the Year as input and gives the First and Last Dates of that year as output.
DECLARE @cYear INT = 2005
SELECT Dateadd(YEAR, @cYear - 1900, 0) AS firstdayofyear,
Dateadd(YEAR, @cYear - 1899, 0) - 1 AS lastdayofyear
7. Given Year and Month’s First and Last Dates
This will take the Year and month as input and gives the First and last dates of the specified year and month as output.
DECLARE @cYear INT = 2005
DECLARE @cMonth INT = 2
SELECT Dateadd(MONTH, @cMonth - 1 + ( @cYear * 12 - 22800 ), 0) AS
firstdayofyearmonth,
Dateadd(MONTH, @cMonth + ( @cYear * 12 - 22800 ), 0) - 1 AS
lastdayofyearmonth
8. Next weekday from today
This will return the next specified weekday from the current date. This will take Weekday as a parameter, Values to pass for week day are 0 – Monday, 1 – Tuesday, 2 – Wednesday, 3- Thursday, 4-Friday, 5- Saturday and 6- Sunday.
DECLARE @Wkday INT = 0
SELECT CASE
WHEN @Wkday = Datediff(dd, 0, Getdate()) % 7 THEN
Datediff(dd, 0, Getdate()) + 7
ELSE Dateadd(DAY, ( 7 + @Wkday - ( Datediff(dd, 0, Getdate()) % 7 ) )%7
,
Datediff(dd, 0, Getdate()))
END
--By using Calendar Table
SELECT TOP 1 caldate
FROM auxcalendardates
WHERE caldate > Datediff(dd, 0, Getdate())
AND Datediff(dd, 0, caldate) % 7 = @Wkday
9. Stripping the time portion
This will strips the time portion of the specified datetime
SELECT Dateadd(DAY, Datediff(dd, 0, Getdate()), 0)
10. Working days between 2 dates
This will provides the Working dates(Excluding Saturday and Sunday) between 2 specified dates.
DECLARE @StartDate DATETIME = '1/1/2001'
DECLARE @EndDate DATETIME = '1/31/2001'
--Without using calendar table
;WITH cte
AS (SELECT @StartDate AS stdate
UNION ALL
SELECT stdate + 1
FROM cte
WHERE stdate < @EndDate)
SELECT *
FROM cte
WHERE Datediff(dd, 0, stdate)%7 BETWEEN 0 AND 4
--By using calendar Table
SELECT *
FROM auxcalendardates
WHERE caldate BETWEEN @startdate AND @enddate
AND Datediff(dd, 0, caldate)%7 BETWEEN 0 AND 4
About the author
|
|
RamiReddy is a .NET Developer, who has a passtion towards t-sql. He has been working with .net/sql server for the Last 4 years. He spends his spare time helping people in the ASP.NET and SQL Server Forums. See Ramireddy's profile.
|
|