March 6, 2009 at 1:39 pm
Greetings, forum users!
I am still a newbie to SQL, as I have only been working in a 'true' SQL environment (SQL Server 2005) for a few months. Prior to this, I was an avid MS Access '03 user, and was just learning how to design my own queries in MS Access before I was thrust (albeit, willingly) into the world of SQL...
Between co-workers, as well as other forums, I have found several scripts for dynamic date manipulations. I have compiled/modified them to suit most of my reporting needs, and posted them here...
DECLARE @today DATETIME
DECLARE @datefirst TINYINT
SET @today = CONVERT(CHAR(8), GETDATE(), 112) --'YYYYMMDD'
SET @datefirst = @@DATEFIRST
SET DATEFIRST 7
--Now
SELECT GetDate() AS [Now]
--This Week
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 1, @today) AS [First Day of This Week]
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 7, @today) AS [Last Day of This Week]
SELECT DATEADD(Second, 86399, (DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 7, @today))) AS [Last Day of This Week 23:59:59]
--This month
SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today) AS [First Day of This Month]
SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)) AS [Last Day of This Month]
SELECT DATEADD(Second, 86399, (DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)))) AS [Last Day of This Month 23:59:59]
--Last Month
SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(@Today) + 1, @Today)) AS [First Day of Previous Month]
SELECT DATEADD(DAY, - DAY(@Today), @Today) AS [Last Day of Previous Month]
SELECT DATEADD(Second, 86399, (DATEADD(DAY, - DAY(@Today), @Today))) AS [Last Day of Previous Month 23:59:59]
--This Year
SELECT DATEADD(DAY, -DATEPART(DAYOFYEAR, @today) + 1, @today) AS [First Day of This Year]
SELECT CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME) AS [Last Day of This Year]
SELECT DATEADD(Second, 86399, (CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME))) AS [Last Day of This Year 23:59:59]
--Last Year
SELECT DATEADD(Year, -1, (DATEADD(DAY, -DATEPART(DAYOFYEAR, @today) + 1, @today))) AS [First Day of Last Year]
SELECT DATEADD(Year, -1, (CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME))) AS [Last Day of Last Year]
SELECT DATEADD(Year, -1, (DATEADD(Second, 86399, (CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME))))) AS [Last Day of Last Year 23:59:59]
--This next line was in the original code, and I'm not totally sure what it does or why it's at the bottom.
--It appears to function just the same when it's at the top (under 'SET DATEFIRST 7').
--The script also seems to function the same when it is commented out.
SET DATEFIRST @datefirst
I did a DATEADD function to add 86399 seconds to the dates, making them the equivolent of 23:59:59, since that seemed to me to be the easiest way to calculate that time without doing either nested DATEADD functions, or re-writing it entirely.
Each of the statements in the code above seem to be working perfectly for most of my needs. Unfortunately, I am unfamiliar with 'CAST' statements, so I'm not particularly sure what the original author was doing there (I wish I could ; but I digress..
Now, the need has arisen to calculate the nth WeekDayName of any given month; specifically, I am looking for the '1st Tuesday of the current month'. If possible, I would like to use the script for other reporting needs that may arise in the future (3rd Wednesday of Last Month, 15th Tuesday of Last Year, etc.).
Thanks in advance
March 6, 2009 at 2:16 pm
I did a DATEADD function to add 86399 seconds to the dates, making them the equivolent of 23:59:59, since that seemed to me to be the easiest way to calculate that time without doing either nested DATEADD functions, or re-writing it entirely.
I have a question about this. Are you using the time 23:59:59 for the end of a date range search?
For example,
SELECT ...
WHERE ([Date] >= @startDate AND [Date] <= @endDate)
or equivalently
SELECT ...
WHERE ([Date] BETWEEN @startDate AND @endDate)
If so, there may be a small but finite chance that you will miss some rows from the results. It is safer to set the @endDate parameter to midnight of the following day and use the following date range filter:
SELECT ...
WHERE ([Date] >= @startDate AND [Date] < @endDate)
March 6, 2009 at 2:21 pm
andrewd.smith (3/6/2009)
I did a DATEADD function to add 86399 seconds to the dates, making them the equivolent of 23:59:59, since that seemed to me to be the easiest way to calculate that time without doing either nested DATEADD functions, or re-writing it entirely.
I have a question about this. Are you using the time 23:59:59 for the end of a date range search?
For example,
SELECT ...WHERE ([Date] >= @startDate AND [Date] <= @endDate)
or equivalently
SELECT ...WHERE ([Date] BETWEEN @startDate AND @endDate)
If so, there may be a small but finite chance that you will miss some rows from the results. It is safer to set the @endDate parameter to midnight of the following day and use the following date range filter:
SELECT ...WHERE ([Date] >= @startDate AND [Date] < @endDate)
Thanks for the response, Mr. Smith!
Yes, I am using 23:59:59 as the end date for the reports. Any results that occurred after that time are for the following day, and should not be included in my report. The original source DB that my reports are compiled from only tracks down to the whole second, rather than parts of a second (milisecond?), so I guess I'm not seeing how I would be missing any rows...
March 6, 2009 at 3:29 pm
select
FirstMondayOfMonth =
dateadd(dd,(datediff(dd,'17530101',SeventhDayOfMonth)/7)*7,'17530101'),
FirstTuesdayOfMonth =
dateadd(dd,(datediff(dd,'17530102',SeventhDayOfMonth)/7)*7,'17530102'),
FirstWednesdayOfMonth =
dateadd(dd,(datediff(dd,'17530103',SeventhDayOfMonth)/7)*7,'17530103'),
FirstThursdayOfMonth =
dateadd(dd,(datediff(dd,'17530104',SeventhDayOfMonth)/7)*7,'17530104'),
FirstFridayOfMonth =
dateadd(dd,(datediff(dd,'17530105',SeventhDayOfMonth)/7)*7,'17530105'),
FirstSaturdayOfMonth =
dateadd(dd,(datediff(dd,'17530106',SeventhDayOfMonth)/7)*7,'17530106'),
FirstSundayOfMonth =
dateadd(dd,(datediff(dd,'17530107',SeventhDayOfMonth)/7)*7,'17530107')
from
(
Select SeventhDayOfMonth = dateadd(month,datediff(month,0,getdate()),0)+6
) a
March 6, 2009 at 4:05 pm
This script will return the Nth day of a specified weekday in any target month. It's not convenient to use if you to need to embed it directly in a single SELECT statement (use Michael Valentine Jones' for that), but it works ok if you can store the calculated dates in local variables before using them in subsequent queries. The script returns a NULL value if the requested day doesn't exist (e.g. 5th Friday of March 2009).
DECLARE @targetMonth datetime
DECLARE @nthDay int
DECLARE @weekDay int
SELECT @targetMonth = GETDATE() /* specify any date in the target month here */
SELECT @nthDay = 4 /* 1st, 2nd, 3rd, 4th or 5th weekday of month */
SELECT @weekDay = 5 /* 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun */
DECLARE @firstOfMonth datetime
DECLARE @firstOfNextMonth datetime
DECLARE @deltaDays int
DECLARE @dt datetime
SELECT
@firstOfMonth = DATEADD(month, DATEDIFF(month, 0, @targetMonth), 0),
@firstofNextMonth = DATEADD(month, 1, @firstOfMonth),
@deltaDays = (@weekDay - 1) - (DATEDIFF(day, 0, @firstOfMonth) % 7),
@dt = DATEADD(day, @deltaDays + 7 * (@nthDay - (CASE WHEN (@deltaDays >= 0) THEN 1 ELSE 0 END)), @firstOfMonth)
SELECT
@nthDay AS [N],
DATENAME(weekday, @dt) AS [Weekday],
CASE WHEN (@dt >= @firstOfMonth AND @dt < @firstOfNextMonth) THEN @dt ELSE NULL END AS [Date]
March 6, 2009 at 10:23 pm
robert.mason (3/6/2009)
Yes, I am using 23:59:59 as the end date for the reports. Any results that occurred after that time are for the following day, and should not be included in my report. The original source DB that my reports are compiled from only tracks down to the whole second, rather than parts of a second (milisecond?), so I guess I'm not seeing how I would be missing any rows...
If someone ever starts sending you data with milliseconds, you code will produce incorrect results. If some newbie in a hurry sees your code and decides it works for him/her, but they have milliseconds in their data, his/her code will produce incorrect results. Recommend you do it the right way all the time, just in case.;)
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2009 at 8:27 am
Jeff Moden (3/6/2009)
If someone ever starts sending you data with milliseconds, you code will produce incorrect results. If some newbie in a hurry sees your code and decides it works for him/her, but they have milliseconds in their data, his/her code will produce incorrect results. Recommend you do it the right way all the time, just in case.;)
You make a great point, Jeff. Well taken. 😀
So, in order to add the miliseconds, would I just add a .99 using a date add function? I am hesitant to qualify midnight as the end date, as midnight is a different day.
Thanks!
March 10, 2009 at 9:58 am
Before you start adding your miliseconds please remember that a datetime datatype is accurate to 3 miliseconds not 1 milisecond (and there are 999 not 99 of them 🙂 ). The last digit on the milisecond is always 0, 3 or 7.
Also most of your functions go thru VARCHAR conversion to get you the desired effect. I personally do not like this approach and tend to write my date time functions using date specific function only.
for example the following will give you the first day of the month and the first day of a year.
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) , 0)
SELECT DATEADD(year, DATEDIFF(year, 0, getdate()) , 0)
The CAST function is similar to CONVERT but does not give you the formatting option so usefull when dealing with dates.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 10, 2009 at 1:03 pm
robert.mason (3/10/2009)
Jeff Moden (3/6/2009)
If someone ever starts sending you data with milliseconds, you code will produce incorrect results. If some newbie in a hurry sees your code and decides it works for him/her, but they have milliseconds in their data, his/her code will produce incorrect results. Recommend you do it the right way all the time, just in case.;)You make a great point, Jeff. Well taken. 😀
So, in order to add the miliseconds, would I just add a .99 using a date add function? I am hesitant to qualify midnight as the end date, as midnight is a different day.
Thanks!
Don't hesitate... Using Midnight the next day is the correct way to do it if your code looks like this...
SELECT yada-yada
FROM yourtable
WHERE datecolumn >= @StartDate --(with a midnight time)
AND datecolumn < @EndDate +1 (where @Enddate is the desired End Date with a midnight time)
Messing around with decimals like you're trying to do will just messthings up... a lot!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply