CODEWITHSUNDEEP
sql
odlan yer
odlan yer

Reputation: 771

Convert INT data type into DateTime Format

//This is my script 

SELECT CASE WHEN iPaid_dt>= 1 THEN CONVERT(DATETIME, CONVERT(CHAR(8),iPaid_dt)) ELSE '' END AS [TRANSACTION DATE]
FROM MyTable

//My OutPut is 

1900-01-01 00:00:00.000  
2012-11-11 00:00:00.000  
2012-10-26 00:00:00.000

//Expected Output: 

2012-11-11 00:00:00.000  
2012-10-26 00:00:00.000

//The 1900-01-01 output should be '' only, What is the correct script? note: iPaid_dt is INT with 0 as default value.

Upvotes: 1

Views: 848

Answers (2)

Codesleuth
Codesleuth

Reputation: 10541

You're mixing field types and SQL Server is converting '' to a DATETIME for you. Try this:

SELECT    CASE
              WHEN iPaid_dt > 0
                  THEN CONVERT(NVARCHAR(23),
                        CONVERT(DATETIME, CONVERT(CHAR(8),iPaid_dt)))
              ELSE ''
          END AS [TRANSACTION DATE]
FROM      MyTable

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

A CASE expression must have a single data type that all possible values for all rows must belong to. Since DATETIME has a higher precedence than VARCHAR, all of the possible values are being forced to be DATETIMEs. So the '' is converted back to a DATETIME.

I can't say how to fix it since I don't know why you're doing this conversion in the first place. Maybe NULL would be a better placeholder than ''?

Upvotes: 3

Related Questions