SQL date problem
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=25558
Printed Date: 28 March 2026 at 9:18am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: SQL date problem
Posted By: ainsworth14
Subject: SQL date problem
Date 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
|
Replies:
Posted By: WebWiz-Bruce
Date 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.
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: ainsworth14
Date 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?
|
Posted By: WebWiz-Bruce
Date 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
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: ainsworth14
Date 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
|
Posted By: michael
Date 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
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: ainsworth14
Date 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 ???
|
Posted By: michael
Date 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
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: ainsworth14
Date Posted: 14 April 2008 at 2:16pm
|
my format at the moment is yyyymmdd
|
Posted By: michael
Date Posted: 14 April 2008 at 4:47pm
Try to force your date format like this
SET DATEFORMAT ymd
Insert into blah .... Values(xxx,xxxx,Convert(datetime('20080414')))
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
|