Solved

datetime format error

Posted on 2014-09-26
12
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

628 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