nvarchar(50) to Datetime - 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

SQL Server 2005 Forums

Transact-SQL (2005)

nvarchar(50) to Datetime

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-02-29 : 07:58:39

Hi all, Please help.
I have created a new SQL dB and imported a table from Access to SQL.
my date columns in Access have been imported as nvarchar(50).
all dates are in the format dd/mm/yyyy.And there is no bad data

My problem is that in the table design of my new SQL Table, if I change the datatype from nvarchar(50) to datetime I get the error "Arithmetic overflow error converting expression to data type datetime"

I know this is telling me that there is a problem with the format of some of the dates but all my dates are defo dd/mm/yyyy ,none of the data has format mm/dd/yyyy ie a 13th month.I know this because I cut the data down to just 2 pieces of data.
can someone help me get the format into datetime please
Ray..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 08:05:34

Did you check your datetime data by using ISDATE() function?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 08:30:48

See if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 08:32:39

quote:
Originally posted by harsh_athalye

Did you check your datetime data by using ISDATE() function?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-02-29 : 08:34:29

Try this:

EXEC sp_rename 'mytable.mycolumn', 'mycolumn_old'

ALTER TABLE mytable ADD mycolumn datetime

UPDATE mytable
  SET mycolumn = 
    SUBSTRING(mycolumn_old, 7, 4) + '-' + 
    SUBSTRING(mycolumn_old, 4, 2) + '-' + 
    SUBSTRING(mycolumn_old, 1, 2) + '-'

ALTER TABLE mytable DROP COLUMN mycolumn_old

--
Lumbago
"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4"

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-02-29 : 11:33:13

Hi all and thx for reply
select isdate('20/11/2004') returns 0
select isdate('11/20/2004') returns 1
So SQL thinks 20/11/2004 is NOT a date.SQL must be defaulting to mm/dd/yyyy
But I want dd/mm/yyyy
What do I need to do?

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-02-29 : 12:40:24

Hi all,
My sql server properties - language says English(United States)
I assume if i change this it will sort my problem but this will effect my other dB's ?
please help
Ray

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-03 : 01:48:22

set dateformat dmy
check validations
set dateformat mdy

Madhivanan

Failing to plan is Planning to fail

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-03-03 : 19:21:21

FYI - SQL will always accept dates of the format YYYYMMDD.

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources