Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS SQL Dates 2

Status
Not open for further replies.

mousematt

MIS
Joined
Feb 5, 2001
Messages
102
Location
GB
Dates in SQL server has always something I've managed to avoid like the plage as I know how many people have trouble with them. I'm now in a situation where I have to drive into this subject and get it right. I can't!

This is my SQL string that I'm using from an ASP;

Code:
INSERT INTO po_line_schedule (po_number, po_line, acknowledged, vendor, acknowledged_date, po_user_id, active, ship_qty, delivery_date) VALUES (830875,1,'Y',8209,'29/09/2005',13,'Y',1,'05/10/2005')

I get;
Code:
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

I've tried without the '' around the dates, it runs the command but enters 01/01/1900 into the date. I think the region settings on the server are correct, I changed the default and rebooted. does anyone know how I can do this the way I want it and not MS's way.
 
Tried:
Code:
INSERT INTO po_line_schedule (po_number, po_line, acknowledged, vendor, acknowledged_date, po_user_id, active, ship_qty, delivery_date) VALUES (830875,1,'Y',8209,'#29/09/2005#',13,'Y',1,'#05/10/2005#')

And
Code:
INSERT INTO po_line_schedule (po_number, po_line, acknowledged, vendor, acknowledged_date, po_user_id, active, ship_qty, delivery_date) VALUES (830875,1,'Y',8209,'#29/09/2005#',13,'Y',1,#05/10/2005#)

Again doesn't like that
 
Based on the error message you got I'd say it is still trying to read the dates as mm/dd/yyyy. Have you tried the same insert statement with the month and day reversed? That would be the first thing I tried.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard. [dragon]
 
Seems the db expects 09/29/2005 but you are giving it 29/09/2005...

So either change the localization to match between the web server and the database or re-order the date yourself using the native date and/or string functions.
 
Yes if I swap the dates on the insert to usa format goes straight in, how do I cahnge the server/check to see what format the server is running
 
Ha ha, used
Code:
SET DATEFORMAT dmy INSERT INTO po_line_schedule (po_number, po_line, acknowledged, vendor, acknowledged_date, po_user_id, active, ship_qty, delivery_date) VALUES (830875,1,'Y',8209,'29/09/2005',13,'Y',1,05/10/2005)

Genuis! Why did I not know about this before!! Works perfecetly and I will prob use this loads from now on.
 
Since you found your own answer, and it may well be useful to others, and you can't give yourself a star, I gave you one. That little tidbit could come in handy.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard. [dragon]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top