Advertisement

04.17.2008 at 12:44PM PDT, ID: 23332283
[x]
Attachment Details

Select Statement to convert a varchar to datetime w/ nulls

Asked by pjnofrills in MS SQL Server, SQL Server 2005, SQL Server 2008

Tags: Microsoft, SQL Server, 2005, Converting a varchar to datetime w/ nulls, T-SQL, Transact SQL

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?Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

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'
 
Keywords: Select Statement to convert a varchar t…
Title
1 convert datetime to varchar
2 Converting varchar to a Datetime
3 Convert (varchar to datetime) in a no…
4 MySQL DATETIME
5 convert datetime
6 Converting datetime problem
 
Loading Advertisement...
 
[+][-]04.17.2008 at 12:57PM PDT, ID: 21380729

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 01:15PM PDT, ID: 21380893

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 01:19PM PDT, ID: 21380939

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 01:25PM PDT, ID: 21380992

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 01:32PM PDT, ID: 21381065

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 01:41PM PDT, ID: 21381149

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 01:47PM PDT, ID: 21381204

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.17.2008 at 02:01PM PDT, ID: 21381317

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, SQL Server 2005, SQL Server 2008
Tags: Microsoft, SQL Server, 2005, Converting a varchar to datetime w/ nulls, T-SQL, Transact SQL
Sign Up Now!
Solution Provided By: mark_wills
Participating Experts: 3
Solution Grade: A
 
 
[+][-]04.17.2008 at 02:54PM PDT, ID: 21381750

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.18.2008 at 03:41AM PDT, ID: 21384601

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.18.2008 at 05:08AM PDT, ID: 21385011

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628