Post reply

Convert date time stamp across multiple fields in case statement

  • January 10, 2020 at 2:33 pm

    Go to Answer

    #3714370

    Given the availability of collected data, I'm trying to combine multiple date fields into an Information Date, it seems to work but I'd like to drop the time stamp.

    case when completedDate is not null then completedDate   -- need to think about how to clean this up 
         when lastSavedDate is not null then lastSavedDate  
         when receivedDate  is not null then receivedDate  
    	 else null end as infoDate,

  • January 10, 2020 at 3:05 pm

    #3714393

    Use COALESCE(completedDate ,lastSavedDate ,receivedDate )

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 10, 2020 at 3:38 pm

    #3714443 

    convert(date,coalesce(completeddate,lastsaveddate,receiveddate),' ') [infoDate],

    Msg 8116, Level 16, State 1, Line 47

    Argument data type varchar is invalid for argument 3 of convert function.

    Was struggling with how to drop the time stamp, still not able to get there with coalesce

  • January 10, 2020 at 3:55 pm

    #3714460

    Check out the documentation for the CONVERT function.  The third argument has to be an integer, so ' ' is invalid.

    John

  • January 10, 2020 at 4:12 pm

    #3714481

    rickyschroder wrote:

    Was struggling with how to drop the time stamp, still not able to get there with coalesce

    TIMESTAMP columns cannot be converted to times. What is the data type of your source columns?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 10, 2020 at 6:15 pm

    #3714527

    the three fields are datetime and I'd like to drop time

  • January 10, 2020 at 6:50 pm

    Answer

    #3714537

    OK, then try

    CAST(coalesce(completeddate,lastsaveddate,receiveddate) as DATE)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 11, 2020 at 3:15 am

    #3714627

    TY Phil that git me to the finish line

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

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