MS SQL Dates 2

[mousematt]

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;

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;

'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.

 

[guitardave78]

try putting #29/09/2005#


}...the bane of my life!

http://www.fuzzyd.co.uk

 

[mousematt]

Tried:

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

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

 

[tsdragon]

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.

 

[Sheco]

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.

 

[mousematt]

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

 

[mousematt]

Ha ha, used

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.

 

[tsdragon]

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.