CODEWITHSUNDEEP
sqlsql-server
Learner
Learner

Reputation: 159

Date time convert and retain blank

I am trying to convert a column value to datetime.

CONVERT(datetime, ColName,105)

This converts all the blank values to default 1900-01-01 00:00:00.000. I want to retain the blank values post conversion too.

Upvotes: 0

Views: 1619

Answers (4)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

First of all, storing dates as strings and using empty strings to represent missing values are very bad ideas. The problem can be fixed once and for all by converting that field into a nullable date or datetime.

Until that's done, or in order to do it easily, you can use the TRY_PARSE function available on all supported SQL Server versions. The earliest supported SQL Server version is 2012. 105 is the italian format, so :

SELECT TRY_PARSE(ColName as datetime USING 'it')

For example

select try_parse( '13-11-2018' as datetime USING 'it')

Will parse the string value into a datetime whose value is 2018-11-13 00:00:00.000. Anything that can't be parsed will return a NULL :

select try_parse( '' as datetime USING 'it')

Will return NULL.

In earlier versions one can use CASE WHEN or the equivalent IIF :

select IIF(TRIM(ColName)='',NULL,CONVERT(datetime, ColName,105))

Returning NULL is the only option because date types like date, datetime etc are scalar types just like int or decimal. There's no empty date or datetime. The only possibility is to have a NULL for a missing value or an actual date.

Trying to return some kind of blank value means that the dates will have to be converted back to strings.

Upvotes: 0

Spider
Spider

Reputation: 524

Blanks values will convert to SQL server default datetime value. Following query will help you not to convert the blank values. However it is better to have NULL instead of a ' '

SELECT CASE WHEN NULLIF(COLUMN,'') = NULL THEN 
            NULL 
      ELSE CONVERT(datetime, dd,105) END 
FROM <TABLE>

Upvotes: 0

kcotman
kcotman

Reputation: 91

If ColName only contains empty (var)char values, you can add NULLIF to the ColName like

CONVERT(datetime, NULLIF(ColName, ''),105)

Upvotes: 4

Jonathan Sayce
Jonathan Sayce

Reputation: 9669

If you're converting to a DATETIME then you won't be able to have a "blank" value, but you could have NULLs instead:

CASE WHEN ColName IS NULL OR LEN(LTRIM(ColName)) = 0 THEN NULL ELSE CONVERT(DATETIME, ColName, 105) END

This will convert any NULLs, blank strings, or whitespace to a NULL DATETIME, and convert everything else to a DATETIME with a value (assuming it's a valid date).

Upvotes: 0

Related Questions