Post reply

convert

  • February 6, 2014 at 3:04 am

    #283282

    I am trying to set a variable so it displays the date 6 months ago unseperated so yyyymmdd

    I read that 112 on get date function did this but

    declare @dateinput date

    set @dateinput = convert(varchar, getdate(), 112)

    select @dateinput

    did not work.

    the below gives me the date 6 month ago but it is seperated..

    declare @minus6month date

    declare @dateinput date

    set @dateinput = convert(varchar, getdate(), 112)

    set @minus6month = DATEADD(mm, -6, @dateinput)

    select @minus6month

    how do i remove the '-'?

    Thanks

  • February 6, 2014 at 3:08 am

    #1686504

    Once you've done a conversion it's a varchar, not a date. Cast (or assign) back to a date datatype and formatting is stripped away. Formatting a date is only relevant when converting a date to a string

    DECLARE @minus6month VARCHAR(50);

    SET @minus6month = CONVERT(VARCHAR(50), DATEADD(mm, -6, GETDATE()), 112);

    SELECT @minus6month AS FormattedDateString;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2014 at 3:14 am

    #1686505

    Ah right ok. I never knew about formatteddatestring

    Cheers!

  • February 6, 2014 at 3:15 am

    #1686506

    SQLSteve (2/6/2014)

    I never knew about formatteddatestring

    That's just a column alias. You could alias the column 'Whatchamacallit' if you like, won't make any difference.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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