String to datetime
-
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
-
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:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
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.
-
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:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Thanks Chirs looks like bum data in one of the fields I will investiagte. Appreciate the help
-
CAST(FLOOR(CAST(GETDATE() AS float))
-
sorry
CAST(FLOOR(CAST(GETDATE() AS float)) AS smalldatetime)
-
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)
-
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?
-
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.
-
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.
-
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 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?
-
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?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply