Handy Scripts to Find Dates and Weeks in SQL

Question

Post reply

Handy Scripts to Find Dates and Weeks in SQL

ned-958591

SSC Journeyman

Points: 84
More actions
December 4, 2008 at 12:23 pm

#73271

After building a custom schedule tool, I had to discover ways to find things such as first day of the month, last day of the month, week of the month, if the given day was the last instance of a weekday in the month, etc. Here is a simple script showing some of these items. Hope it helps.
[font="Courier New"]declare @dt datetime = '10/24/2008'
--date provided...
select @dt [EvalDate]
-- First Day of the Month...
select cast(convert(varchar(6), @dt, 112) + '01' as datetime) [FirstDayOfMonth]
-- Last Day of the Month...
select dateadd(d, -1, dateadd(m, 1, cast(convert(varchar(6), @dt, 112) + '01' as datetime))) [LastDayOfMonth]
-- Week in Year...
select datepart(wk, @dt) [WeekInYear]
-- Week in Month...
select datediff(week, convert(varchar(6), @dt, 112) + '01', @dt) + 1 [WeekInMonth]
-- Determine whether the given day is the last weekday of its kind in the month (ex. Last Friday of the month)...
select case when @dt between dateadd(d, -7, dateadd(m, 1, cast(convert(varchar(6), @dt, 112) + '01' as datetime))) and dateadd(d, -1, dateadd(m, 1, cast(convert(varchar(6), @dt, 112) + '01' as datetime))) then 1 else 0 end [LastWeekDayOfMonth]
-- All scripts as a single table for comparison...
select @dt [EvalDate],
cast(convert(varchar(6), @dt, 112) + '01' as datetime) [FirstDayOfMonth],
dateadd(d, -1, dateadd(m, 1, cast(convert(varchar(6), @dt, 112) + '01' as datetime))) [LastDayOfMonth],
datepart(wk, @dt) [WeekInYear],
datediff(week, convert(varchar(6), @dt, 112) + '01', @dt) + 1 [WeekInMonth],
case when @dt between dateadd(d, -7, dateadd(m, 1, cast(convert(varchar(6), @dt, 112) + '01' as datetime))) and dateadd(d, -1, dateadd(m, 1, cast(convert(varchar(6), @dt, 112) + '01' as datetime))) then 1 else 0 end [LastWeekDayOfMonth]
[/font]
Have Fun

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Conversion of datetime to varchar and back is the worst available option in terms of performance.

Not to mention possible collation problems.

_____________
Code for TallyGenerator

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Ned,

It is good of you to post up solutions which might be useful to others.

To elaborate on what Sergiy said, because datetime format is numeric at heart, doing conversions to character data and back places more of a load on the CPU than working with dates using the native date functions such as DATEPART, DATEDIFF, and DATEADD.

Don't be embarassed about not knowing this. The great thing about participating in these forums is that you learn something new every day. My first solution to stripping the time off a date was to use CAST(LEFT(@date,11) as datetime). :w00t: Like your solutions, it produces the correct results, and doesn't bring the server to its knees, but there are more efficient methods.

The following examples show how you can determine the first and last date of the month using the date functions. There are also some other threads, both here and in the other T-SQL forums, that detail other solutions to problems like finding specific days of the week. I encourage you to do a little research, then re-evaluate your solutions using these functions and post the revised versions back here, or possibly submit it as an article.

Best regards,

Bob

-----------------------------------------------------------------------------------------------------------

declare @dt datetime

select @dt = getdate()

select @dt as [Subject Date]

select datediff(dd,0,@dt)as [Difference in days from Date 0 to @dt]

set @dt = dateadd(dd,0,datediff(dd,0,@dt))

select @dt as [Difference converted back to DateTime by using DateAdd]

set @dt = dateadd(dd,1-datepart(dd,@dt),@dt)

select @dt as [First Date of Subject Month]

-- adding/subtracting integers to datetime = adding/subtracting days

set @dt = dateadd(mm,1,@dt)-1-- adds a month then subtracts a day

select @dt as [Last Date of Subject Month]

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Bob Hovious (12/7/2008)
-- adding/subtracting integers to datetime = adding/subtracting days
set @dt = dateadd(mm,1,@dt)-1-- adds a month then subtracts a day
select @dt as [Last Date of Subject Month]

Note that this only works if you've already grabbed the first day of the month with the previous function.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

The mighty Garadin is correct. My last couple of examples were step-by-step, each dependent on the last. You can of course nest the steps into one expression, but it seemed like it would be harder to read and understand if you were new to the functions.

How about this, Seth?

------------------------------------------------------------------------------------------------------------------

declare @dt datetime

select @dt = getdate()

select dateadd(dd,1-datepart(dd,@dt),datediff(dd,0,@dt))

as [First Date of Subject Month]

select dateadd(mm,1,dateadd(dd,1-datepart(dd,@dt),datediff(dd,0,@dt)))-1

as [Last Date of Subject Month]

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Why do all of those calculations when it is this simple?

Declare @subjectDate datetime;

Set @subjectDate = current_timestamp;

Select @subjectDate

,dateadd(month, datediff(month, 0, @subjectDate), 0) As FirstOfMonth

,dateadd(month, datediff(month, -1, @subjectDate), -1) As LastOfMonth;

Set @subjectDate = '20080220';

Select @subjectDate

,dateadd(month, datediff(month, 0, @subjectDate), 0) As FirstOfMonth

,dateadd(month, datediff(month, -1, @subjectDate), -1) As LasOfMonth;

Now, if you want to get previous or next - we can add/subtract from the subject month, as in:

Declare @subjectDate datetime;

Set @subjectDate = current_timestamp;

Select @subjectDate

,dateadd(month, datediff(month, 0, @subjectDate), 0) As FirstOfMonth

,dateadd(month, datediff(month, -1, @subjectDate), -1) As LastOfMonth

,dateadd(month, datediff(month, 0, @subjectDate) - 1, 0) As PreviousFirstOfMonth

,dateadd(month, datediff(month, -1, @subjectDate) - 1, -1) As PreviousLastOfMonth

,dateadd(month, datediff(month, 0, @subjectDate) - 1, 0) As PreviousFirstOfMonth

,dateadd(month, datediff(month, -1, @subjectDate) - 1, -1) As PreviousLastOfMonth

,dateadd(month, datediff(month, 0, @subjectDate) + 1, 0) As NextFirstOfMonth

,dateadd(month, datediff(month, -1, @subjectDate) + 1, -1) As NextLastOfMonth;