Datetime help

Question

Post reply

Datetime help

jgoodwin

SSChasing Mays

Points: 648
More actions
August 13, 2003 at 11:50 am

#84115

There must be a smarter way to do this - someone please help me out. The code comments should explain what I am trying to do, but there must be some date functions I'm not familiar with that would make this code much cleaner:
if datepart(dw, getdate()) = 3 and datepart(hh, getdate()) >= 16 begin
--set date begin to > sunday of current week
set @LockDateBegin = rtrim( ltrim( str(datepart(yy, dateadd(d, -2, getdate()) ) ) ) ) + '-' + ltrim( rtrim( datepart(mm, dateadd (d, -2, getdate()) ) ) ) + '-' + ltrim( rtrim( datepart(d, dateadd(d, -2, getdate()) ) ) )
end
else if datepart(dw, getdate()) > 3 begin
--set date begin to > sunday of current week
set @LockDateBegin = rtrim( ltrim( str(datepart(yy, dateadd(d, -(datepart(dw, getdate()) - 1), getdate()) ) ) ) ) + '-' + rtrim( ltrim( datepart(mm, dateadd(d, -(datepart(dw, getdate()) - 1), getdate()) ) ) ) + '-' + rtrim( ltrim( datepart(dd, dateadd(d, -(datepart(dw, getdate()) - 1), getdate()) ) ) )
end
else begin
--set date begin to last sunday through today
set @LockDateBegin = rtrim( ltrim( str(datepart(yy, dateadd(d, -(datepart(dw, getdate()) - 1) - 7, getdate()) ) ) ) ) + '-' + rtrim( ltrim( datepart(mm, dateadd(d, -(datepart(dw, getdate()) - 1) - 7, getdate()) ) ) ) + '-' + rtrim( ltrim( datepart(dd, dateadd(d, -(datepart(dw, getdate()) - 1) - 7, getdate()) ) ) )
end

Viewing 8 posts - 1 through 7 (of 7 total)

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

jpipes SSChampion Points: 12230 More actions · Answer 1

DECLARE @LockDateBegin SMALLDATETIME

SELECT @LockDateBegin =

CASE

WHEN (DATEPART(dw, GETDATE() = 3

AND DATEPART(hh, GETDATE() >= 16) THEN

DATEADD(d, -2, DATEDIFF(d, 0, GETDATE())

WHEN (DATEPART(dw, GETDATE() > 3 THEN

DATEADD(d, (DATEPART(dw, GETDATE() - 1), DATEDIFF(d, 0, GETDATE())

ELSE

DATEADD(d, (DATEPART(dw, GETDATE() - 8), DATEDIFF(d, 0, GETDATE())

END

[/code]

The DATEDIFF(d, o, GETDATE()) returns just the date portion (as an INT) to the DATEADD function.

ccolson@nutrisystem.com Newbie Points: 4 More actions · Answer 2

Here is a line I use to set the date to the Monday of the current week:

SET @LockDateBegin = convert(datetime,convert(char(10),dateadd(day,(2 - datepart(weekday,getdate()) + round((1.0 / datepart(weekday,getdate()) - 0.1),0) * (-7)),getdate()),101))

To get Sunday, just change "2 - datepart" to "1 - datepart"

Good luck.

-CC

zaltug SSC Rookie Points: 48 More actions · Answer 3

quote:
DECLARE @LockDateBegin SMALLDATETIME
SELECT @LockDateBegin =
CASE
WHEN (DATEPART(dw, GETDATE() = 3
AND DATEPART(hh, GETDATE() >= 16) THEN
DATEADD(d, -2, DATEDIFF(d, 0, GETDATE())
WHEN (DATEPART(dw, GETDATE() > 3 THEN
DATEADD(d, (DATEPART(dw, GETDATE() - 1), DATEDIFF(d, 0, GETDATE())
ELSE
DATEADD(d, (DATEPART(dw, GETDATE() - 8), DATEDIFF(d, 0, GETDATE())
END
[/code]
The DATEDIFF(d, o, GETDATE()) returns just the date portion (as an INT) to the DATEADD function.

Some paranthesis were missing in this code. No disrespect to the poster, I am submitting the code after correction.

DECLARE @LockDateBegin SMALLDATETIME

SELECT @LockDateBegin =

CASE

WHEN (DATEPART(dw, GETDATE()) = 3 AND DATEPART(hh, GETDATE()) >= 16) THEN DATEADD(d, -2, DATEDIFF(d, 0, GETDATE()))

WHEN (DATEPART(dw, GETDATE()) > 3) THEN DATEADD(d, (DATEPART(dw, GETDATE() - 1)), DATEDIFF(d, 0, GETDATE()))

ELSE (DATEADD(d, (DATEPART(dw, GETDATE() - 8)), DATEDIFF(d, 0, GETDATE())))

END

print @LockDateBegin

zaltug SSC Rookie Points: 48 More actions · Answer 4

Thanks to a friend of mine:

Declare @lastSunday datetime

Declare @cDate datetime

Set @cDate = GETDATE()

Set @lastSunday = dateadd(d,-6,dateadd(d,(7 - datepart(dw,@cDate)),@cDate))

PRINT @lastSunday

bobsterboy SSCommitted Points: 1549 More actions · Answer 5

I just added some comments and added the variable @DayOfWeek to Zaltug's great script. I bet if someone else looks at this, they'll be able to get it down even more.

Declare @lastSunday datetime

Declare @cDate datetime

Declare @DayOfWeek int -- change to: -6 for Sunday, -5 for Monday,

-- -4 for Tuesday, -3 for Wednesday, - 2 for Thursday,

-- -1 for Friday, 0 for Saturday

Set @DayOfWeek = -6

Set @cDate = GETDATE()

Set @lastSunday = dateadd(d,@DayOfWeek,dateadd(d,(7 - datepart(dw,@cDate)),@cDate))

PRINT @lastSunday

tymberwyld SSCertifiable Points: 7810 More actions · Answer 6

Here's the Code I use in a Stored Proc that calculates over time:

Set @DateFirst = @@DateFirst-- Save the Current Setting

Set DateFirst 1-- Set DateFirst to be Monday

Set @StartDate = DateAdd(d, -(DatePart(dw, @ActivityDate) - 1), @ActivityDate)-- Get the First Monday

Set @EndDate = DateAdd(d, 6, @StartDate) -- Get the Sunday

...

-- Reset the DateFirst

Set DateFirst @DateFirst -- Reset the First day of Week

ursus-129623 SSC Journeyman Points: 78 More actions · Answer 7

I'm currently playing with a query that needs to return all the appointments for a physician three business days ahead of their appointments. I think I can deal with the logic, but when I started playing with the "pretty-report-that-management-can-read" bit, I ran into an odd behavior when using datepart and datename.

I won't bore you with the whole thing, but if I enter the query:

select @@datefirst as 'Datefirst',datename(dw,@@datefirst) as 'WD Name'

,datename(dw,datepart(d,@@datefirst)) as 'DP WD Name'

this is the result I get:

Datefist WD Name DP WD Name

7 Monday Tuesday

If I read BOL correctly, the results should be 7 (default), Sunday, Sunday, right? Why the discrepancy? I'm sure it's a stupid oversight on my part, but I'd like your help in figuring this out before I start calling patients to remind them of their Sunday appointments.