[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

varchar data into date type

Posted on 2012-08-23
27
Medium Priority
?
659 Views
Last Modified: 2012-08-24
I have two formats for data in a field 00/00/00 and 00/00/0000.  When I try to convert to datetime from  varchar ie

CONVERT(datetime, camaextn.mx_data, 6)

I get

Conversion failed when converting datetime from character string.

Does anyone have a case statement to handle this error please as I think the two different formats break the conversion.
0
Comment
Question by:owenja
  • 9
  • 9
  • 3
  • +3
27 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38325514
I always use yyyyMMdd format when converting to/from sql. It's a pretty much universal format and SQL does an implicit conversion of it.
0
 
LVL 9

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 38325516
Try this command
Convert(field,datetime,101)
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38325518
6 is not valid:
CONVERT(datetime, camaextn.mx_data, 6)

instead you could write:
CONVERT(datetime, camaextn.mx_data, 101)

Convert Date - MSDN
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38325519
please check this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

anyhow: if your conversion fails, it means that for at least 1 row, the data is not following the acceptable value for style 6, and indeed:
6 => dd mon yy

00/00/00 means either style 1 (mm/dd/yy) or 3 (dd/mm/yy)
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38325533
Example:
SELECT CONVERT(datetime, '01/01/2012', 101)
SELECT CONVERT(datetime, '12/01/01', 1)

Open in new window

0
 

Author Comment

by:owenja
ID: 38325638
I have both of these formats in the base fields I am working with and extracting the varchar types from

SELECT CONVERT(datetime, '01/01/2012', 101)
SELECT CONVERT(datetime, '12/01/01', 1)

How do I convert them to valid dates in this format 00Aug12  without breaking the conversion ie
case when 00/00/00
then convert(datetime,field,6)
when 00/00/0000
then convert(datetime,field,6)
end
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38325730
you can try:

case when LEN(yourfield) = 8
then convert(datetime,field,1)
when len(yourfield)=10
then convert(datetime,field,101)
end
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38325754
Or:
CONVERT(datetime,LEFT(field,6)+RIGHT(field,2),1)
0
 

Author Comment

by:owenja
ID: 38325838
Afraid none of this works on this field - all I get is -

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38325852
Ok, one thing that is very important is whether you have dd/mm/yy or mm/dd/yy.
To check if there's any value that isn't supposed to be there, check:
SELECT * FROM Table WHERE LEFT(field,2)>12
or
SELECT * FROM Table WHERE SUBSTRING(field,4,2)>12

depending on which is the month. Basically, you have some values that don't conform to a date. You have to find out which.
0
 

Author Comment

by:owenja
ID: 38325906
Hi Cluskitt

Tried those and all contents are dd/mm/yy or dd/mm/yyyy - there is a pretty hefty field size for this as it is generic at 40 chars in the db.  Could there be an additional space at the end of one of the strings or something obscure like that?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38327705
Tried those and all contents are dd/mm/yy or dd/mm/yyyy -
The style should then be 3 and 103.  However you first need to find the invalid data.  You can use ISDATE()  as in:

SELECT *
FROM YourTable
WHERE ISDATE(YourDateColumn) = 0

but ISDATE() is not bullet-proof.  But it may identify a few errors.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38328282
If there is a space, then try:
CONVERT(datetime,LEFT(LTRIM(RTRIM((field)),6)+RIGHT(LTRIM(RTRIM((field)),2),3)
0
 

Author Comment

by:owenja
ID: 38328390
Hi Cluskitt,
Excellent.

Now that works and pulls all info into 2001-01-01 00:00:00.000  format.  Can this be improved slightly to get the 01Aug12 format that I need please? Seems to be number 6 on the format list?

Also I needed to adjust the clause slightly as the ltrim was breaking
CONVERT(datetime,LEFT(LTRIM(RTRIM(mx_data)),6)+RIGHT(LTRIM(RTRIM(mx_data)),2),3)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38328420
To get to another format, you need to reverse the convert. You can see all possible formats here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

To get that format without any spaces, you can try:
CONVERT(char(7),CONVERT(datetime,LEFT(LTRIM(RTRIM(mx_data)),6)+RIGHT(LTRIM(RTRIM(mx_data)),2),3),6)
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38328437
for "01Aug12" format try:
DECLARE @Date DATETIME
SET @Date = GETDATE()

SELECT CAST(DATEPART(dd,@Date) AS VARCHAR(2)) + ' ' + SUBSTRING(DATENAME(month, @Date),1,3) + ' ' + SUBSTRING(CAST(DATEPART(yyyy,@Date) AS CHAR(4)),3,2)

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38328457
Actually, to get it without the spaces, you'd need:
REPLACE(CONVERT(char(7),CONVERT(datetime,LEFT(LTRIM(RTRIM(mx_data)),6)+RIGHT(LTRIM(RTRIM(mx_data)),2),3),6),' ','')
0
 

Author Comment

by:owenja
ID: 38328463
Hi Cluskitt,
I now get 01 Feb - any ideas?
0
 

Author Comment

by:owenja
ID: 38328468
Hi Cluskitt,
If I increase the char to 10 I get the full date 01 Feb 12 but with spaces - unfortunately I dont need the spaces!
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38328493
Use the replace code I provided, with char(10). It was my fault for using char(7). That is the final length, not the length of the conversion.
0
 

Author Comment

by:owenja
ID: 38328539
pourfard - excellent - the only thing wrong is that the result appears as 1Feb12 instead of 01Feb12 - I used a + instead of + ' ' + as I don't need spaces either
cheers
0
 

Author Comment

by:owenja
ID: 38328703
Hi Cluskitt,
Yours seems the almost perfect answer but I need the result with no spaces ie 12Aug12 - can you adjust to get that format please?
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 2000 total points
ID: 38328709
I did say to use the replace code with extended char():
REPLACE(CONVERT(char(10),CONVERT(datetime,LEFT(LTRIM(RTRIM(mx_data)),6)+RIGHT(LTRIM(RTRIM(mx_data)),2),3),6),' ','')
0
 

Author Closing Comment

by:owenja
ID: 38328814
Thanks for all your help on this - been a real education - regards
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38329429
pourfard,

for "01Aug12" format try:
That seems overly complex, why not just simply use the CONVERT style of 6 as in:
SELECT REPLACE(CONVERT(char(10), GETDATE(), 6), ' ', '')

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38329461
That is what I said. Except that he's not using GETDATE() but a string field in his table which has to be converted into date first.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38329643
Cluskitt,

I understand.  My comment was directed at pourfard who was using a datetime in his example code here http:#a38328437
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question