CODEWITHSUNDEEP
sqlsql-serverdatetimeisnull
etm124
etm124

Reputation: 2140

CASTing a datetime to string if null value - keeping original datetime formatting

I have the following part of my query:

ISNULL(cast(fd.decision_date as varchar(20)), 'PENDING') as facility,
ISNULL(cast(cd.decision_date as varchar(20)), 'PENDING') as corporate,
ISNULL(cast(cb.creation_date as varchar(20)), 'PENDING') as billing

My values are originally of datetime datatype, but what I want to do is return the word 'PENDING' if the value is NULL.

With the code above, my results are casted to a varchar, thus returning something like:

Aug 20 2013 9:35AM

instead of 2013-08-20 09:35:54

Suggestions would be greatly appreciated.

Upvotes: 0

Views: 2337

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Instead of cast(... as varchar(20)), use convert with a style number, this is exactly what that's for.

SELECT CONVERT(CHAR(19), GETDATE(), 120);

So your query becomes:

COALESCE(CONVERT(CHAR(19), fd.decision_date, 120), 'PENDING') as facility,
COALESCE(CONVERT(CHAR(19), cd.decision_date, 120), 'PENDING') as corporate,
COALESCE(CONVERT(CHAR(19), cb.creation_date, 120), 'PENDING') as billing

Upvotes: 1

Meldor
Meldor

Reputation: 236

Replace cast with convert and appropriate style: Convert syntax & styles, eg:

ISNULL(convert(varchar(20), fd.decision_date, 120), 'PENDING') as facility,

Upvotes: 1

Related Questions