Date questions...

You all helped me a great deal yesterday with my leading zeros query question, so I'm hoping someone might have an insight into this one! I'm working on a new database (to me anyway, it's been in use for many years at the company) and I have a manipulation/data pull question...

I have a field that's currently YYYYMMDD, and the Data Type is int. So what I need to do for a report is turn the date around to MMDDYYYY and pull just the last week worth of info. With some manupulation (probably not conventional though) I'm able to get the MMDDYYYY in a varchar format, but I'm stumped as to how to pull just the last week worth of info? I tried various forms of - 7 and of course it's not working. I can't say I've had to deal much with dates that weren't already in date/datetime format? Anyone else have ideas on how to convert it or pull the data?

Thanks again to all!
TiffanieHave you looked up the topics cast, convert, and dateadd in BOL. If not, please do so. Once you've done that this may help:



declare @.cdat char(08), @.edat datetime, @.bdat datetime
select @.cdat = '20050401' -- April 1, 2005
select @.edat = convert(datetime, @.cdat)
select @.bdat = dateadd(d,-7,@.edat)
select @.bdat, @.edat

Results:
-------- --------
2005-03-25 00:00:00.000 2005-04-01 00:00:00.000|||First, whoever designed your database to store dates like that is a freaking idiot. Please go find them and tell them so for me...

...are you back now? Good. Thanks.
Now, convert that to a true datetime value like this:
convert(datetime, cast(@.dateint as char(8)))
...then you can check for entries in the last seven days like this:
where datediff(d, convert(datetime, cast(@.dateint as char(8))), getdate()) < 7|||I would concur blindman! However, this db has been in use for almost 16 years with modifications throughout time and of course lots of turnover in personel - so I'm not able to track down and pummel the individual myself! Only been here for about a month now and this db was handed to me on Monday.

I've been reading up on the CAST, CONVERT just don't have much practice yet. I've used date functions in Oracle, just never came across an int date before?

Thanks!
Tiffanie