Solved

datetime format error

Posted on 2014-09-26
12
179 Views
Last Modified: 2014-09-26
SQL Server 2005

I was reading an excel file from sql server query and got this error.
"Conversion failed when converting datetime from character string"
There were some issues in dateformat, which I found out manually looking one
by one row.

I am simulating the problem like this

declare @t table( dt varchar(50), tm varchar(50))
insert @t select '25-Sep-2014','06:19:32 pm'
insert @t select '24-Sep-2014','06:17:45 pm'
insert @t select '23-Sep-2014','06:44:35 pm'
insert @t select '23-Sep-2014','06:27:43 pm'
insert @t select '21-Sep-2014','06:19:39 pm'
insert @t select '20-eep-2014','06:19:01 pm'
insert @t select '19-Sep-2014','06:19:07 pm'
insert @t select '20-Sep-2014','06:19:08 pm'
insert @t select '18-Sep-2014','06:19:09 pm'


select convert(datetime,dt + ' ' + tm)
from @t
there will be error   "Conversion failed when converting datetime from character string"

my question is how to find by query which row has invalid format.
0
Comment
Question by:Mehram
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345756
Insert of using CONVERT, use TRY_CONVERT.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345757
So change the last bit of your code to:

select dt, tm, try_convert(datetime,dt + ' ' + tm) as Combined
from @t
where try_convert(datetime,dt + ' ' + tm) is null

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40345758
your code is assuming implicit data type conversion (type), see here:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

to check the date value, you could try to use isdate() function, but which doesn't have the option to specify which "style" to use in the convert function.

you could then change your sql login settings to use a language which has the relevant "style" value as default, or create a function that catches the error to tell...
0
 

Author Comment

by:Mehram
ID: 40345760
same error
0
 
LVL 4

Expert Comment

by:Nuno Rogado
ID: 40345761
Hi,

Change it to:

declare @t table( dt varchar(50), tm varchar(50))
insert @t select '09-25-2014','06:19:32 pm'
select convert(datetime,dt + ' ' + tm)
from @t

It will work.

Best Regards,
Nuno Rogado
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345763
To whom are you commenting, Mehram?

If to me, my code does not produce an error.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:Mehram
ID: 40345771
Hi Guy Hengel [angelIII / a3]
thats what I wanted

select dt, tm, isdate (dt + ' ' + tm)
from @t

dt      tm      (No column name)
25-Sep-2014      06:19:32 pm      1
24-Sep-2014      06:17:45 pm      1
23-Sep-2014      06:44:35 pm      1
23-Sep-2014      06:27:43 pm      1
21-Sep-2014      06:19:39 pm      1
20-eep-2014      06:19:01 pm      0
19-Sep-2014      06:19:07 pm      1
20-Sep-2014      06:19:08 pm      1
18-Sep-2014      06:19:09 pm      1
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345775
So change your last bit of your code to:

select dt, tm, iif(try_convert(datetime,dt + ' ' + tm) is null, 0, 1)
from @t

Open in new window

0
 

Author Comment

by:Mehram
ID: 40345779
Hi phillip Button

I did mention , my sql server version in my question.
0
 

Author Closing Comment

by:Mehram
ID: 40345780
Isdate() worked.

any other idea will be appreciated.

thanks
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345782
Does this work?

select dt, tm, case when try_convert(datetime,dt + ' ' + tm) is null then 0 else 1 end
from @t

Open in new window

0
 

Author Comment

by:Mehram
ID: 40345786
<<select dt, tm, case when try_convert(datetime,dt + ' ' + tm) is null then 0 else 1 end
from @t>>

I am working in SQL Server 2005
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now