Issues with date datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issues with date datatype

Author

Topic

soulchyld21
Starting Member

28 Posts

Posted - 2012-01-26 : 08:48:01

Hi, I am facing a conversion issue with the date data type, I need to modify an existing table and change a column from varchar to date datatypes, below is the error message I am getting, I have thousands of records in the column. I have even gone as far as exporting the data to a spreadsheet trying to modify it there and import it back, all to no avail! any ideas?

- Unable to modify table.
Conversion failed when converting date and/or time from character string.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-26 : 08:52:16

does it all convert nicely to date fields in excel?

If not, maybe you can identify the offending records there.

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 08:54:01

you need to first make sure you dont have any spurious values in it before you convert column to date type. what does this return?


SELECT COUNT(*)
FROM Yourtable
WHERE ISDATE(yourcol)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

soulchyld21
Starting Member

28 Posts

Posted - 2012-01-31 : 07:43:34

@visakh16 I dont think it will return anything because the column currently holds varchar! shall I run it anyway?

soulchyld21
Starting Member

28 Posts

Posted - 2012-01-31 : 07:50:00

I also recreated the table and am trying to populate it with the same records using a spreadsheet to generate the necessary sql code (I dont know if this makes sense) It seems to work so far, admitteedly I havent dont it for all the records, a little problem I am now facing is with the date format, It is using american style (sql server 12/1/2011 for 1st december (or something of the sort)) instead of 12 January (UK format). Any Ideas how I can overcome this one?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-31 : 07:54:24

ISDATE function would return 1 if the varchar column can be converted to a date in your setting. You will see what Visakh was trying to do if you run these queries:

SELECT ISDATE('20120131');  -- returns 1 because it can be converted to date.
SELECT ISDATE('1/31/2012'); -- returns 1 can be converted to date in my US setting.
SELECT ISDATE('31/1/2012'); -- returns 0 because it cannot be converted to date in my US setting.
SELECT ISDATE('Abcdefgh');  -- returns 0

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-31 : 07:56:39

quote:
Originally posted by soulchyld21

I also recreated the table and am trying to populate it with the same records using a spreadsheet to generate the necessary sql code (I dont know if this makes sense) It seems to work so far, admitteedly I havent dont it for all the records, a little problem I am now facing is with the date format, It is using american style (sql server 12/1/2011 for 1st december (or something of the sort)) instead of 12 January (UK format). Any Ideas how I can overcome this one?

You can use CONVERT function with a style specified,for example:

SELECT CONVERT(DATETIME,'31/1/2012',103);

Various styles are listed here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 12:51:11

quote:
Originally posted by soulchyld21

I also recreated the table and am trying to populate it with the same records using a spreadsheet to generate the necessary sql code (I dont know if this makes sense) It seems to work so far, admitteedly I havent dont it for all the records, a little problem I am now facing is with the date format, It is using american style (sql server 12/1/2011 for 1st december (or something of the sort)) instead of 12 January (UK format). Any Ideas how I can overcome this one?

you need to make sure you convert dates to unambiguos format before you pass it for storing in sql table else based on date format and regional settings it may interpret date values differently

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources