String to datetime

Question

Post reply

String to datetime

Paul Munter

Say Hey Kid

Points: 681
More actions
September 14, 2007 at 2:21 am

#177471

Total newbie wondering if some kind folks could help. I am trying to convert a string to datetime using convert but i keep getting the same error
Syntax error converting datetime from character string.

Here is the code i am attempting to use.
select CONVERT(datetime, Date_On_List) from temp
The field Date_On_List is currently nvarchar and i want it to be smalldatetime. The format of the field is currently this
23/07/2007
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi there,

This looks messy so I will try and find a better way to do it.

SELECT

CONVERT(DATETIME,(RIGHT('23/07/2007',4) + '-' + SUBSTRING('23/07/2007',4,2) + '-' + LEFT('23/07/2007',2)))

I'll try and get back to you with a better solution

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Paul Munter Say Hey Kid Points: 681 More actions · Answer 2

Thanks Chris

I tried

SELECT CONVERT(DATETIME,(RIGHT(Date_On_List,4) + '-' + SUBSTRING(Date_On_List,4,2) + '-' + LEFT(Date_On_List,2)))

from [Temp]

but i still get the Syntax error converting datetime from character string.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

HI There,

Could you send me the data in your table?

I'm think that some of your Date_On_List, fields are not in the format you descibed.

Here is the code I used:

DECLARE

@Date_On_List NVARCHAR(100)

SET

@Date_On_List ='23/07/2007'

SELECT

CONVERT(DATETIME,(RIGHT(@Date_On_List,4) + '-' + SUBSTRING(@Date_On_List,4,2) + '-' + LEFT(@Date_On_List,2)))

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Paul Munter Say Hey Kid Points: 681 More actions · Answer 4

Thanks Chirs looks like bum data in one of the fields I will investiagte. Appreciate the help

ugur sesen-431664 SSC Enthusiast Points: 128 More actions · Answer 5

ugur sesen-431664

SSC Enthusiast

Points: 128

September 14, 2007 at 4:59 am

#733707

CAST(FLOOR(CAST(GETDATE() AS float))

ugur sesen-431664 SSC Enthusiast Points: 128 More actions · Answer 6

sorry

CAST(FLOOR(CAST(GETDATE() AS float)) AS smalldatetime)

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 7

You shouldn't need all the fancy substring stuff to convert properly, assuming you have good data in your fields. Try:

Convert

(datetime, <mydate>, 103)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Paul Munter Say Hey Kid Points: 681 More actions · Answer 8

Thanks Brandie

From my CSV file which is holding the date in the format of

20/07/2007

I tried the following

select top 1 Convert(datetime, Date_On_List, 105 )from [Temp]

which gave me the error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I then changed the format of the cell in the .csv to

2007-07-20

ran the following code

select top 1 Convert(datetime, Date_On_List, 120 )from [Temp]

which works perfectly! Trouble is my feed is always going to be in the 20/07/2007 format.

Any suggestions?

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 9

Paul, check the collation on your server OS and on your SQL Server. The column itself might be collated differently than the other collations or your database / server collation might have a specific collation that is causing your datetimes to format that way. SS 2k5 picks up its default collation from the OS it's sitting on (if it's a microsoft product) and if no one's changed anything, the Server, DB and column should all be the same collation.

You can alter the collation of your column to be different from your DB or your server. I think you can alter your database collation also, but to alter the server collation, you have to uninstall and reinstall with a new collation.

BTW, I converted to 103 because your original post had a datetime with / characters in it that went dd/mm/yyyy. 105 uses a - delimiter so that's why it didn't work for your value.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Paul Munter Say Hey Kid Points: 681 More actions · Answer 10

Still experiencing problems with this.

I am importing using bulk insert from a .csv file. In the file the date is being stored as

23/07/2007

When you look at format cell on the .csv file the dat is being held as date.

When i perform the bulk insert into a staging table with the field being held as a char SQL server converts it into the following format.

23/07/07

Even if i use

select Convert(datetime, Date_On_List, 103 )from [Temp]

I am still getting Syntax error converting datetime from character string.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 11

Brandie's suggestion works for me. Converts that string to a smalldatetime just fine.

declare

@ch varchar(20), @dt smalldatetime

set

@ch = '23/07/2007'

select @dt = Convert(datetime, '23/07/2007', 103)

select

@dt

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 12

Paul,

What size is the character field you're using in your staging table? You don't mention that.

Also, if it's importing as a character field, but syntax erroring on the Convert, you definitely have hidden characters or other bad data somewhere in that field. Import into your staging table with a character field. Then do a Select on that table with an ORDER BY Date_On_list. Scroll through the field manually to see if you notice anything odd.

If there is only one date value in that field for each record (if you only ever have 7/23/2007 as your date), do a "WHERE Date_On_List <> '23/07/07' or Date_On_list <> '23/07/2007' ". Then see if you come up with any strange results.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Paul Munter Say Hey Kid Points: 681 More actions · Answer 13

Brandie thanks again for the input. My staging table import field is as follows

[Date_On_List] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

When i do the insert and check the data it looks fine but there is obviosuly something in it somewhere that is not in the correct format. Any idea why in the .csv it is stored as dd/mm/yyyy yet when i bulk insert it into a field char 10 the format becomes dd/mm/yy?

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 14

DateTime collation issues shouldn't affect a character field, so it's a little strange to me...

What's the code you're using for the Bulk Insert? What delimiters are being used in the .csv?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.