asked on

Select Statement to convert a varchar to datetime w/ nulls

I wrote a pretty thorough SELECT statement to return some info from our database, which works great, so I modified the code to convert the date fields, which are currently varchars, to datetimes. The varchar dates are formatted as YYYYMMDD. If I run a single select statement w/ the conversion, it seems to work ok. If there are any null values, however, it blows up and I get the error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

The results also show some of the values incorrectly, as "1900-01-01 00:00:00.000", where the record is null, but then it blows up anyways after about 8 lines. Is there an easy way to allow for null values?

Anyone know what I'm doing wrong?

SELECT    
 s.Division,  
 c.Supplier,      
 CONVERT(datetime,p.OrderDate,1) AS 'Order Date',    
 CONVERT(datetime,p.PromiseDate,1) AS 'Promise Date',    
 CONVERT(datetime,c.InstallDate,1) AS 'Install Date',    
 c.Pr_Code AS "Product Code",      
 c.Inv_Num AS "Order Number",      
 c.Line_Num AS "Line",    
 c.Roll_Item_Num AS "Roll or Item Number",      
 c.Style,    
 c.Color,    
 c.Grs_Cost AS "Gross Cost",    
 c.Qty,  
 c.Units,  
 c.Price,      
 c.Line_Tot AS "Line Total",    
 p.RefNum  AS "Reference"    
from dbo.CustLine c JOIN dbo.PurOrder p   
 ON (c.Inv_Num = p.PoNum AND c.Line_Num = p.PoLine) join dbo.header h   
 ON (c.Inv_Num = h.Inv_Num) join dbo.store s  
 ON (h.store = s.store)  
WHERE c.Status = 'O'

Open in new window

Aneesh

>CONVERT(datetime,p.OrderDate,1) AS 'Order Date',

CASE WHEN p.OrderDate is NULL then NULL else CONVERT(datetime,p.OrderDate,1) END AS 'Order Date',

pjnofrills

ASKER

Tried that and got the same result. Also I noticed the value is not a true null, it's just blank: '' I attempted this code below and got the same result.

USE PracticeRFMS
SELECT    
 s.Division,  
 c.Supplier,      
 CASE WHEN p.OrderDate = '' then '' else CONVERT(datetime,p.OrderDate,1) END AS 'Order Date',    
 CASE WHEN p.PromiseDate = '' then '' else CONVERT(datetime,p.PromiseDate,1) END AS 'Promise Date',    
 CASE WHEN c.InstallDate = '' then '' else CONVERT(datetime,c.InstallDate,1) END AS 'Install Date',    
 c.Pr_Code AS "Product Code",      
 c.Inv_Num AS "Order Number",      
 c.Line_Num AS "Line",    
 c.Roll_Item_Num AS "Roll or Item Number",      
 c.Style,    
 c.Color,    
 c.Grs_Cost AS "Gross Cost",    
 c.Qty,  
 c.Units,  
 c.Price,      
 c.Line_Tot AS "Line Total",    
 p.RefNum  AS "Reference"    
from dbo.CustLine c JOIN dbo.PurOrder p   
 ON (c.Inv_Num = p.PoNum AND c.Line_Num = p.PoLine) join dbo.header h   
 ON (c.Inv_Num = h.Inv_Num) join dbo.store s  
 ON (h.store = s.store)  
WHERE c.Status = 'O'

Open in new window

Aneesh

change this part inside the case statement

>CONVERT(datetime,p.OrderDate,1)

CONVERT(varchar(10),p.OrderDate,1)

pjnofrills

ASKER

That ran fine, and I see that there is an order with a promise date listed as "0".

Perhaps the if statement could determine if there are the proper amount of characters?

pjnofrills

ASKER

Apparently the "0" came in from B2B records, so I have no control over that, it's not user error or anything. How do I validate if the OrderDate field has a valid 8 digit number?

Mark Wills

when blank it will give you the system 'zero' date of 1 jan 1900.

CASE WHEN isnull(p.OrderDate,'') = '' then NULL else CONVERT(datetime,p.OrderDate,1) END AS 'Order Date',
CASE WHEN isnull(p.PromiseDate,'') = '' then NULL else CONVERT(datetime,p.PromiseDate,1) END AS 'Promise Date',
CASE WHEN isnull(c.InstallDate,'') = '' then NULL else CONVERT(datetime,c.InstallDate,1) END AS 'Install Date',

Now, a 1 as the convert type is telling the convert statement that the string is formatted like mm/dd/yy and if it isn't formatted that way, then it will error.

you need 112 as the convert type e.g. CONVERT(datetime,c.InstallDate,112) to tell convert that your string is formatted like yyyymmdd

Aneesh

you can check whether the value is valid date using

ISDATE() function ..

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

yes, isdate() will check for a date, but then it could be in any date format. We need to check if it is specifically in yyyymmdd format

ee_rlee

hi, try this

SELECT    
 s.Division,  
 c.Supplier,
 CASE WHEN ISDATE(p.OrderDate)=1 THEN CASE WHEN CONVERT(varchar(10),CAST(p.OrderDate AS datetime),112)=p.OrderDate THEN CAST(p.OrderDate AS datetime) END END AS 'Order Date',
 CASE WHEN ISDATE(p.PromiseDate)=1 THEN CASE WHEN CONVERT(varchar(10),CAST(p.PromiseDate AS datetime),112)=p.PromiseDate THEN CAST(p.PromiseDate AS datetime) END END AS 'Promise Date',
 CASE WHEN ISDATE(c.InstallDate)=1 THEN CASE WHEN CONVERT(varchar(10),CAST(c.InstallDate AS datetime),112)=c.InstallDate THEN CAST(c.InstallDate AS datetime) END END AS 'Install Date',
 c.Pr_Code AS "Product Code",      
 c.Inv_Num AS "Order Number",      
 c.Line_Num AS "Line",    
 c.Roll_Item_Num AS "Roll or Item Number",      
 c.Style,    
 c.Color,    
 c.Grs_Cost AS "Gross Cost",    
 c.Qty,  
 c.Units,  
 c.Price,      
 c.Line_Tot AS "Line Total",    
 p.RefNum  AS "Reference"    
from dbo.CustLine c JOIN dbo.PurOrder p   
 ON (c.Inv_Num = p.PoNum AND c.Line_Num = p.PoLine) join dbo.header h   
 ON (c.Inv_Num = h.Inv_Num) join dbo.store s  
 ON (h.store = s.store)  
WHERE c.Status = 'O'

Open in new window

pjnofrills

ASKER

That's the problem is that some of the records coming in from our distributors may have garbage data in it. The data is *supposed to* be in a yyyymmdd format, but I see a dozen or so records with just a "0" character in it.

I'll try everyone's suggestions when I get into work today, thanks!

pjnofrills

ASKER

Awesome! The statement alone worked and the help putting it all into a function was a nice bonus. I got my report! Thanks.