Solved

datetime format error

Posted on 2014-09-26
12
181 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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