Convert this date to calendar format...?

Question

Convert this date to calendar format...?

tacy.highland

Hall of Fame

Points: 3319
More actions
May 25, 2010 at 12:11 pm

#223027

I need to run a query based off of a rolling year's data, which for the startdate I found online as so:
SELECT DATEADD(year, -1, dateadd(s,-1,dateadd(dd,datediff(dd,0,getdate()),0)))
It's great, gives me today's date but for last year. Unfortunately, I need it in calendar format. I tried just adding the CONVERT with 101 format, but of course that didn't work so well. I'm not so on-top of the date functions and converting and formatting, etc, so I'm a little lost on this....
Help?

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

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

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

Please define "calendar format".

If you're using the date in a query, you'll want it to remain a DATETIME field unless the dates in your table are in some weird format.

--J

tacy.highland Hall of Fame Points: 3319 More actions · Answer 2

It needs to be in calendar format for the query:

05/25/2010

If I could also have it in ldate format, (20100525) that would be great too.....but either would work for me.

jvanderberg SSCertifiable Points: 6762 More actions · Answer 3

CONVERT 101 should work fine then.

SELECT CONVERT(VARCHAR,DATEADD(year, -1, dateadd(s,-1,dateadd(dd,datediff(dd,0,getdate()),0))),101)

Returns "05/24/2009". But, it returns it as a VARCHAR, not a datetime. You should not use a VARCHAR for date comparisons. If the dates in your table are stored as VARCHARs (I hope for your sake they aren't), then you should convert the table VARCHAR dates to DATETIMEs, instead of the other way around.

--J

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

select CONVERT(varchar(11),getdate(),101) -- produces 05/25/2010

select CONVERT(varchar(11),getdate(),108) -- produces 20100525

Converting oxygen into carbon dioxide, since 1955.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 5

select CONVERT(varchar(11),DATEADD(YY,-1,getdate()),101) -- would give you last year.

Converting oxygen into carbon dioxide, since 1955.

tacy.highland Hall of Fame Points: 3319 More actions · Answer 6

Thank you everyone for the quick and knowledgeable responses! This works wonderfully now.

Thank you so much!

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

tacy.highland (5/25/2010)
Thank you everyone for the quick and knowledgeable responses! This works wonderfully now.
Thank you so much!

Tray... I have a concern about your floating year code with the date format you've chosen. Any chance of you posting your code and the CREATE TABLE statement?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)