Updating datetime fields

[WelshyWizard]

Hi all,

I want to update a datetime field in a sql server table so it holds a different date to what id currently in there.

The code I've tried is the following...

UPDATE LABOR_TICKET SET TRANSACTION_DATE = 30/11/2006 WHERE EMPLOYEE_ID = 'GT';

However, This puts '01/01/1900' in the field... How do I modify my code to put the correct data in?

Cheers.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[Jamfool]

just use the following before your update.

set dateformat dmy

 

[Catadmin]

As Jamfool said, it's a style thing. SQL Server's normal datetime mode is to run off of either yyyy/mm/dd or the United States standard of mm/dd/yyyy. Since you put 30 as your first number, and there is no 30th month, SQL Server is updating the field with the default I-don't-understand-the-date-you're-entering-so-I'll-use-'01/01/1900' value.

Make sense?

Just do the style thing like he said or use Convert(DateTime,'30/11/2006',102) or something to that effect and you should be fine.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Actually, the style is probably not the problem. It all depends on your regional settings.

Look here for more info on that: thread183-1240616

The reason you are having a problem is that you need to have apostrophes around your date. Without the apostrophes, sql is interpreting the date as integers with the slashes as the division operator. Since the numbers evaluate to 0 (because of integer math), the date is interpretted as day 0, which is Jan 1, 1900.

UPDATE LABOR_TICKET SET TRANSACTION_DATE = [!]'[/!]30/11/2006[!]'[/!] WHERE EMPLOYEE_ID = 'GT';

As the other thread I mention suggests, you should really be using the ISO Unseperated date format (YYYYMMDD). So, Ideally you would have...

UPDATE LABOR_TICKET SET TRANSACTION_DATE = [!]'[/!]20061130[!]'[/!] WHERE EMPLOYEE_ID = 'GT';

This date will never be misinterpretted by sql server, no matter what your language setting is.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom