New to T-SQL - what is wrog with this update statement? 1

[Scorez2000]

Here's the query I'm trying to run:

UPDATE Bathrooms SET Bathrooms.[2nd Fix Hot and Cold Water AF] = '15/06/2004' WHERE (((Bathrooms.BathroomID)=77));

BathroomID is a an INT field, and "2nd Fix Hot and Cold Water AF" is a smalldatetime field.

The query runs fine if on the database in Access2000 format, but not when the database is upped to SQL Server.

I believe this is because SQL Server uses Transact SQL which has slightly different syntax, but I cannot find any documentation on the differences.

 

[johnherman]

You probably need to convert or cast the date to tell the database what format you are providing.

-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[donutman]

Generally dates are written in 'mm/dd/yyyy' format. I don't know how to change the default. The ; at the end of the statement and all of the ()'s around BathroomID are not needed, but they shouldn't hurt.
Since you are new to MSSQL, you may want to read this thread on some coding tips, thread183-856683. It's a work in progress, so there is conflicting advice, but it should give you some ideas.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dariopowell]

Donutman is correct.

Try switching the month and the day as so:

UPDATE Bathrooms
SET
Bathrooms.[2nd Fix Hot and Cold Water AF]= '06/15/2004'
WHERE Bathrooms.BathroomID = 77

 

[dariopowell]

Donutman is correct.

Try switching the month and the day as so:

UPDATE Bathrooms
SET
Bathrooms.[2nd Fix Hot and Cold Water AF]= '06/15/2004'
WHERE Bathrooms.BathroomID = 77

I set up a test environment and this worked fine.

Good Luck!

 

[Scorez2000]

Yeah, using a date such as 05/05/2004 works. Or switching to americanised dates (05/28/2004) also works.

Thing is, this application will be used in the UK only.

 

[SQLBill]

Or you can use:

SET DATEFORMAT dmy
UPDATE Bathrooms SET Bathrooms.[2nd Fix Hot and Cold Water AF] = '15/06/2004' WHERE (((Bathrooms.BathroomID)=77));

SET DATEFORMAT tell SQL Server how to interpret the date information being passed to the database.

This is one reason why using yyyy-mm-dd is usually the best way to enter dates.

-SQLBill

 

[Scorez2000]

SQLBill,

If you are ever in Belfast, let me know and Ill take you out for a shed load of pints of Guinness.

Thank you very much. My frustration has finally surpassed!!!

 

[SQLBill]

Thank you for the offer. I've been to Ireland once and love TRUE Guiness. It doesn't compare to the 'Guiness' served in Europe or America.

MMMMMMMM.

-SQLBill

 

[ESquared]

How about giving SQLBill his purple star? Click on the "Thank SQLBill for this valuable post" link and then confirm the action.