Building Date Comparison String

Question

Post reply

Building Date Comparison String

Roger Abram

Hall of Fame

Points: 3683
More actions
November 24, 2009 at 5:47 am

#385842

I'm trying to build a proc where I will use a datetime variable called @PaidThruRun in the Where clause. First I want to set the value of the variable based on what month it is and then hardcode the month and day and add what year it is from getdate().
In any event, the following does not work. I've played around with Cast but can't seem to get the syntax right. Can anyone offer any suggestions as to how to fix the set @PaidthruRun line below? That is, taking '10/31/' and adding the year? @PaidThruRun is a datetime variable.
Thank you.
Roger
If month(getdate()) = '11'
set @PaidThruRun = '10/31/' + year(Getdate())

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

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

dsdeming Ten Centuries Points: 1236 More actions · Answer 1

I usually do something like this:

DECLARE @now datetime

SET @now = CONVERT(datetime, CONVERT(char(8), GETDATE(), 112), 112)

SELECTDATEADD( dd, -1 * DATEPART( dd, @now), @now )

HTH

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Looks like you want the last day of the current month, correct? If so, try this:

select dateadd(mm, datediff(mm, 0, getdate()) + 1, -1)

Roger Abram Hall of Fame Points: 3683 More actions · Answer 3

I actually got it to work by doing this:

If month(getdate()) = '04'

set @PaidThruRun = convert(datetime, ( '03/31/' + convert(varchar,year(Getdate()) )))

dsdeming Ten Centuries Points: 1236 More actions · Answer 4

dsdeming

Ten Centuries

Points: 1236

November 24, 2009 at 12:04 pm

#1083610

What do you do if the month is January?

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Roger Abram (11/24/2009)
I actually got it to work by doing this:
If month(getdate()) = '04'
set @PaidThruRun = convert(datetime, ( '03/31/' + convert(varchar,year(Getdate()) )))

Okay, now it looks like you want the end of the previous month. I apparently misread your first post.

Question, do you plan on hard-codeing all of these into your query? Simpliest way is this:

dateadd(mm, datediff(mm, 0, getdate()), -1)

-- This will return the last day of the previous month.

Roger Abram Hall of Fame Points: 3683 More actions · Answer 6

If month(getdate()) = '01'

set @PaidThruRun = convert(datetime, ( '12/31/' + convert(varchar,year(Getdate()-1) )))

I'm only doing this for four quarters...

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

So, yyyy-12-31, yyyy-03-31, yyyy-06-30, yyyy-09-30?

Then you can check this out:

select dateadd(qq, datediff(qq, 0, getdate()), -1)

This will return the last day of the previous quarter.

Roger Abram Hall of Fame Points: 3683 More actions · Answer 8

select dateadd(qq, datediff(qq, 0, getdate()), -1)

Nice!

dsdeming Ten Centuries Points: 1236 More actions · Answer 9

That is sweet. I love the way it trims out the time as well because the '0' date started at midnight. So this trick will work with any datepart larger than an hour (although it's definitely the long way round to try to find out what yesterday's date was ;-)).

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 10

Actually, if I were doing this and running a query through the end of the previous day, month, quarter, etc I'd do it slightly different.

Using the end of the previous quarter would look like this:

SELECT

...

FROM

...

WHERE

TranDate < dateadd(qq, datediff(qq, 0, getdate()), 0)

This way, if the the column TranDate includes the time portion I will get all records for that last day of the period.