Solved

datetime format error

Posted on 2014-09-26
12
177 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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

19 Experts available now in Live!

Get 1:1 Help Now