Convert DateTime with NULL value

Question

I am running this query

select * from dbo.CHARGES  m
LEFT JOIN Docs z ON z.DocId=m.DocId
AND CHARGE_DATE=CAST(z.DocDate + z.DocTime AS DATETIME)

And getting Conversion failed when converting date and/or time from character string, since some rows of DocDate, DocTime have null value

Here DocTime is Varchar(5)

How can i run this query by ignoring NULL or wrong values?

"since some rows of DocDate, DocTime have null value" Nope, thats not it. It is because the data you have in the columns creates a string that does not convert nicely to a datetime. — Mikael Eriksson
– Mikael Eriksson, Commented Oct 25, 2013 at 12:07
What format is DocDate date in? You may need to use SQL-Server's CONVERT rather than CAST so you can specify a style, and convert each part individually. e.g. CONVERT(DATETIME, z.DocDate, 103) + CONVERT(TIME, z.DocTime) — GarethD
– GarethD, Commented Oct 25, 2013 at 12:14
A better question though would be, why are you storing time as VARCHAR(5)? Why not use the TIME datatype?, or if you are storing the DATE and TIME why not just store a single DATETIME column? Storing dates and times as strings is an abhorrent practice and if it is not too late consider redesigning your table! — GarethD
– GarethD, Commented Oct 25, 2013 at 12:15
@GarethD, The DocDate value is like 2006-10-25 00:00:00.000 and the system is in production and designed 3 years back. Now we could not change the design — Billa
– Billa, Commented Oct 25, 2013 at 13:09
@RemusRusanu, The question is how can i run this query by ignoring NULL or wrong values — Billa
– Billa, Commented Oct 25, 2013 at 13:11

GarethD · Accepted Answer · 2013-10-25 13:39:12Z

If your strings are in the format yyyy-MM-dd hh:mm then your convert expression will be something like:

SELECT  CONVERT(DATETIME, ISNULL(DocDate, '1900-01-01') + ' ' + ISNULL(DocTime, '00:00'), 121)

However, it is probably adivisable to check that it actually is a date before you try to convert it:

SET DATEFORMAT YMD;

SELECT  DocDate,
        DocTime,
        Formatted = CASE WHEN ISDATE(ISNULL(DocDate, '1900-01-01') 
                                + ' ' + ISNULL(DocTime, '00:00')) = 1

                            THEN CONVERT(DATETIME, ISNULL(DocDate, '1900-01-01') 
                                + ' ' + ISNULL(DocTime, '00:00'), 121)
                        ELSE NULL
                    END
FROM    (VALUES
            ('2013-10-01', '17:30'),-- CORRECT FORMAT
            ('2013-10-01', NULL),   -- NULL TIME
            ('2013-13-10', '17:30'), -- INVALID DATE
            ('2013-01-05', 'XX:30'), -- INVALID TIME
            (NULL, '17:00')         -- NULL DATE
        ) t (DocDate, DocTime);

Note, I have set the dateformat even though it is set within the convert, this is for the benefit of ISDATE(), if the date format is not set this way, it may think that 2013-13-10 is a valid date (13th October 2013), but will through an error when it comes to the convert.

If/When you upgrade to SQL-Server 2012 you can simply use TRY_CONVERT:

SET DATEFORMAT YMD;
SELECT  DocDate,
        DocTime,
        Formatted = TRY_CONVERT(DATETIME, ISNULL(DocDate, '1900-01-01') 
                                + ' ' + ISNULL(DocTime, '00:00'), 121)
FROM    (VALUES
            ('2013-10-01', '17:30'),-- CORRECT FORMAT
            ('2013-10-01', NULL),   -- NULL TIME
            ('2013-13-10', '17:30'), -- INVALID DATE
            ('2013-01-05', 'XX:30'), -- INVALID TIME
            (NULL, '17:00')         -- NULL DATE
        ) t (DocDate, DocTime);

Examples on SQL Fiddle

I don't condone this approach, and (as I have in a comment) would strongly advise correcting the problem (which is storing data as the wrong type) rather than jumping through hoops to work around data errors.

Rafael Marcos · Accepted Answer · 2013-10-25 12:08:28Z

2

Try this code, the query depend of your necessity.

With Null Values

select * from dbo.CHARGES  m
LEFT JOIN Docs z 
    ON z.DocId=m.DocId
    AND (
        z.DocDate IS NULL
        OR z.DocTime IS NULL
        OR CHARGE_DATE=CAST(z.DocDate + z.DocTime AS DATETIME)
    )

Without Null Values

select * from dbo.CHARGES  m
LEFT JOIN Docs z 
    ON z.DocId=m.DocId
    AND (
        NOT z.DocDate IS NULL
        AND NOT z.DocTime IS NULL
        AND CHARGE_DATE=CAST(z.DocDate + z.DocTime AS DATETIME)
    )

answered Oct 25, 2013 at 12:08

Rafael Marcos

2421 silver badge8 bronze badges

2 Comments

Alec. Over a year ago

I really don't understand this answer.

Rafael Marcos Over a year ago

In this solution, the filter of date is just used when both field is not null.

Collectives™ on Stack Overflow

Convert DateTime with NULL value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related