| Author |
 Topic Search  Topic Options
|
ainsworth14 
Groupie
Joined: 05 August 2003
Location: United Kingdom
Status: Offline
Points: 62
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: SQL date problem
Posted: 07 April 2008 at 2:30pm |
|
Hi,
i am having trouble copying dates into my live sql db, i've tried all sorts of combinations of mm/dd/yyyy, yyyy/mm/dd etc but every time i run my script to copy from a temp db and paste into my live db i get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL
Server]The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.
the records with a date of 01/12/2008 paste in with no problems, but it fails at the date of 19/02/2008
now i assumed this was because there isn't a 19th month so i swapped the format around to dd/mm/yyyy and it still failed at the very same record
this is really frustrating at the moment, any help would be appreciated.
thanks
Mark
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 April 2008 at 2:33pm |
|
You could use the ISO international date format eg:-
2008-02-19
However, if you use SQL Server 2000 or below you need to remove the '-' eg:-
20080219
This format is used in Web Wiz Forums as find it works with all locales, at least not heard of anyone having any date issues since moving across to this format some years ago.
|
|
|
|
ainsworth14
Groupie
Joined: 05 August 2003
Location: United Kingdom
Status: Offline
Points: 62
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 April 2008 at 2:39pm |
|
my temp db is an access db and its here where i state my date format, even if access has the yyyy/mm/dd format it still fails when copying to the sql db
when you say 20080219 you mean with no / in between?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 April 2008 at 2:51pm |
|
The ISO format which is recognised by all databases uses dashes '-' not slashes '/'
It's only SQL Server 2000 which likes it without the dashes.
Whether you can set this format in Access is another matter though, as internally Access stores dates as a double integer number from 1899 or some date like that eg.:-
6346363.98798
|
|
|
|
ainsworth14
Groupie
Joined: 05 August 2003
Location: United Kingdom
Status: Offline
Points: 62
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 April 2008 at 2:58pm |
|
i can set the date within access to yyyymmdd and it displays as exactly that but when clicking on it, the value changes to the dd/mm/yyyy format then clicking away changes it back to yyyymmdd which looks correct
i've just tried this format and it still fails on the same record,
it is Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) that im using so i think i have the correct format without the '-' like you said
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 April 2008 at 2:54pm |
|
Look at the cast or convert function when importing dates into sql if they come from access (depending on how you do it) they maybe flagged as varchar(x) and sql will reject that even if the format is write. If you do e.g. insert into sqltable Values(Convert(datevalue as datetime)) it should work.
Again, it really depends how you transfer your data
|
|
|
|
ainsworth14
Groupie
Joined: 05 August 2003
Location: United Kingdom
Status: Offline
Points: 62
|
Post Options
Thanks(0)
Quote Reply
Posted: 13 April 2008 at 11:21am |
|
great
i've managed to stop the script failing using convert datevalue but all my dates are now 01/01/1900 ???
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 April 2008 at 2:11pm |
|
what was the original date formatted like? You need to make sure they are based on the same calendar/date format
|
|
|
|
