Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    19

    Unanswered: 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!
    Tiffanie

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Have you looked up the topics cast, convert, and dateadd in BOL. If not, please do so. Once you've done that this may help:

    Code:
     
    
    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

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2005
    Posts
    19
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •