Convert Nvarchar to Datetime

Question

Convert Nvarchar to Datetime

mikewillis

Old Hand

Points: 317
More actions
November 13, 2003 at 10:13 am

#83416

Hi everyone,
I have an nvarchar(20) column containing a date and time in the format (DD\MM\YYYY HH:MM:SS). That value actually needs to populate a datetime column in another table.
Is there a simple way of doing that conversion??

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1



DECLARE @d nvarchar(20)

SET @d = '21/12/2003 15:22:15'

SELECT CONVERT(datetime,@d,103)

--Jonathan

mikewillis Old Hand Points: 317 More actions · Answer 2

Thanks Jonathan,

Your script works and I'd tried the convert on several individual dates in that column and it works, but if I run the query against all of the data in the column I get the error

Syntax error converting datetime from character string.

I guess there must be some data not in the datetime format in there so will have to pick through and find it!! Cheers.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
I guess there must be some data not in the datetime format in there so will have to pick through and find it!! Cheers.

Rather than "pick though it," try something like this:



SET DATEFORMAT dmy

SELECT KeyCol, DateCol

FROM YourTable

WHERE ISDATE(DateCol) = 0

--Jonathan

David Burrows SSC Guru Points: 65142 More actions · Answer 4

quote:
SET DATEFORMAT dmySELECT KeyCol, DateColFROM YourTableWHERE ISDATE(DateCol) = 0

Jonathan,

Depends on language settings, eg

English

2003-11-15 1

2003-15-11 0

15/11/2003 0

11/15/2003 1

British English

2003-11-15 0

2003-15-11 1

15/11/2003 1

11/15/2003 0

Far away is close at hand in the images of elsewhere.
Anon.

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:
quote:
SET DATEFORMAT dmySELECT KeyCol, DateColFROM YourTableWHERE ISDATE(DateCol) = 0
Jonathan,
Depends on language settings, eg
English
2003-11-15 1
2003-15-11 0
15/11/2003 0
11/15/2003 1
British English
2003-11-15 0
2003-15-11 1
15/11/2003 1
11/15/2003 0

David,

Look again at my script and the OP's date format.

--Jonathan

mikewillis Old Hand Points: 317 More actions · Answer 6

Thanks for your help guys. I had got it right originally in the way Jonathan's first script shows, using convert. The thing that threw me and made me think it wasn't working was that there were a few zero length strings in the original nvarchar column. Trying to do a convert on these threw up the error.

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Oops!

My apologies

I hate dates in sql

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Why, David?

Datetime data types help us solving many problems we wouldn't have without them.

I remember back in the 'good old dbase days' everybody I knew had a conversion routine to transform such information into yyyymmdd. Works really fine. But today we are significantly further ahead ?!?

Oh happy day

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 9

quote:
Oops!
My apologies
I hate dates in sql

I don't. I think I'll write an article on them. The one on this site (Syverson) is poor, and I've seen a lot of confusion in these forums. I remember consulting at one site where the developers told me that their policy was to use the char data type for dates because SQL Server "does not handle dates correctly."

--Jonathan

David Burrows SSC Guru Points: 65142 More actions · Answer 10

quote:
I hate dates in sql

Hey! Come on chaps, a bit tongue in cheek here.

I do all my data processing in yyyy-mm-dd format and only convert to/from dd/mm/yyyy for presentation/input.

Far away is close at hand in the images of elsewhere.
Anon.

mikewillis Old Hand Points: 317 More actions · Answer 11

Glad to see a basic question sparked a bit of healthy debate!

Jonathan SSC-Insane Points: 20427 More actions · Answer 12

quote:
quote:
I hate dates in sql
Hey! Come on chaps, a bit tongue in cheek here.
I do all my data processing in yyyy-mm-dd format and only convert to/from dd/mm/yyyy for presentation/input.

Guess we need a tongue in cheek smiley.

I'm with you on always using the ODBC canonical or the ISO formats in code.

--Jonathan