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
Use COALESCE(completedDate ,lastSavedDate ,receivedDate )
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2020 at 3:38 pm
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
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
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2020 at 6:15 pm
the three fields are datetime and I'd like to drop time
OK, then try
CAST(coalesce(completeddate,lastsaveddate,receiveddate) as DATE)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2020 at 3:15 am
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