Convert Number of Days to Year

Transact-SQL
1

I have a nvarchar column called
XDate and it has some dates formatted and days

4/16/2017
4/10/2017
42803
3/15/2017

I want to convert all of these to a date format especially the one in days.

2 
DECLARE @t table
        (
         varcharColumn varchar(100)
        );
INSERT @t
    VALUES
        ('4/16/2017'),
        ('4/10/2017'),
        ('42893'),
        ('3/15/2017');
SELECT
        CASE WHEN IsDate(t.varcharColumn) = 1 THEN Cast(t.varcharColumn AS date)
             WHEN IsNumeric(t.varcharColumn) = 1
             THEN DateAdd(dd, Cast(t.varcharColumn AS int), 0)
             ELSE t.varcharColumn
        END dateColumn
    FROM
        @t t;

1 Like
3

I have more than just those entries I have 190 of them. Can you write it for the entire 190? Thanks

4 
SET DATEFORMAT MDY
SELECT CASE WHEN XDate LIKE '[1-9]/[1-9]/[12][0-9][0-9][0-9]'
              OR XDate LIKE '[1-9]/[0-2][0-9]/[12][0-9][0-9][0-9]'
              OR XDate LIKE '[0-3][0-9]/[1-9]/[12][0-9][0-9][0-9]'
              OR XDate LIKE '[0-3][0-9]/[0-2][0-9]/[12][0-9][0-9][0-9]'
-- Add any additional "acceptable" date string formats
                 THEN CONVERT(date, XDate)
            WHEN XDate NOT LIKE '%[^0-9]%' 
                 THEN DATEADD(Day, CONVERT(int, XDate), '01/01/1900') -- NEED THE CORRECT EPOCH START DATE
            ELSE NULL
            END
5

Actually it worked sorry. Thank you sooo much!!!!!!!!!

6

Dunno which one you used, but if is was mine it would be prudent to include otorre_riversidedpss.org's IsDate(XDate ) = 1 because my simple regex will also allow invalid dates, and that would cause a runtime error (unless you are sure you don't have, and can never have, invalid dates in your NVarchar)

Check for any NULL values too - i.e. invalid date conversion attempts

NULL might be preferable? Otherwise SQL is going to try to convert any duff value into a date I fear?

1 Like
7

Used Jotorre version but see what you mean thanks