CODEWITHSUNDEEP
sqlsql-servercastingdecimal
Caveman42
Caveman42

Reputation: 709

Pull numbers out of a decimal

Okay so, I have a column that looks something like this:

 20140813000000000

It is displaying a date, in the format of decimal(17,0), but I need to take the first 8 characters out of it to convert it to a date so it can be used. I've tried casting and converting and substring but everything leads to an arithmetic overflow. Any help on getting that date out?

Example of what I am using and the error:

CAST(SUBSTRING(CAST([tblDate] as varchar),1,8) as Date)

Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 70

Answers (2)

Richard Hansell
Richard Hansell

Reputation: 5403

Great that you found a solution to your own problem, but note that this won't work if you ever have values like 00000000000000001 in your table as this will be converted to 1, pass the first test in your CASE statement and then throw an exception as 1 won't CAST to DATE.

This is more long-winded, but safer... (full example included):

DECLARE @TestTable TABLE ([tblDate] NUMERIC(17,0));
INSERT INTO @TestTable VALUES(00000000000000001); --Edge case
INSERT INTO @TestTable VALUES(20140831000000001); --Value to parse
SELECT 
    CASE 
        WHEN SUBSTRING(REPLACE(RIGHT('00000000000000000' + CAST([tblDate] AS VARCHAR(17)), 17), SPACE(1), '0'), 1, 8) = '00000000' THEN NULL 
        ELSE SUBSTRING(CAST([tblDate] AS VARCHAR(17)), 1, 8) 
    END
FROM 
    @TestTable;

Returns:

NULL
2014-08-31

Your answer would throw an exception for the first value.

If you are sure your data will ALWAYS either be all zeroes or contain a date to be parsed then fine...

Upvotes: 1

Caveman42
Caveman42

Reputation: 709

I got it working, I added a case to filter out the 0's to NULL before casting it as a date:

Select
    CAST(CASE
        WHEN SUBSTRING(CAST([tblDate] as varchar),1,8) = 0 THEN NULL
        ELSE SUBSTRING(CAST([tblDate] as varchar),1,8)
    END as DATE)

Upvotes: 1

Related Questions