Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: Date minus 1 day help please

    Hi all, i have a date in varchar format 080601(Jun 1, 2008)
    i need to minus one day from this to get 080531(May 31,2008)
    how can i do this when the format is in varchar?
    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I hope you have learned to keep dates stored as dates and not as strings. Fortunately for you, this format actually converts to datetime natively....unless you are working with a server that does not have us_english as its default national setting.
    Code:
    declare @dt varchar(6)
    
    set @dt = '080601'
    
    select dateadd(dd, -1, convert(date, @dt)), convert(date, @dt)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DECLARE @d varchar(10) = '080601'

    SELECT DATEADD(d,-1,CONVERT(datetime,@d))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Echo...echo...echo.... Pinch hitting for Pedro Borbon... Manny Mota... Mota... Mota...

  5. #5
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by Brett Kaiser View Post
    DECLARE @d varchar(10) = '080601'

    SELECT DATEADD(d,-1,CONVERT(datetime,@d))
    thanks Brett,
    now i would only like to minus one day if the date is the first of the month
    so basically if the date is 080116(jan 16 2008) i dont want it to minus 1 day for this date

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You should look into the case statement and the day() function.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @x VARCHAR(99) = '080101'
    
    SELECT DATEADD(day
    ,  CASE
          WHEN @x LIKE '%01' THEN -1
          ELSE 0
       END, CONVERT(DATE, @x, 12))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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