Help needed inserting date into a table 1

[da75]

Hi all

I am trying to update a record where the date is equal to blah blah

UPDATE v4_StatusLog SET [minutes]=301 WHERE PC_ID =125 AND PhoneId=1 AND DateStamp='16/12/2002'

I have tried it without the comment marks but this still doesn't work

has anyone got any ideas

 

[osjohnm]

Hi

Try this instead:

UPDATE v4_StatusLog SET [minutes]=301 WHERE PC_ID =125 AND PhoneId=1 AND convert(varchar, DateStamp, 103)= '16/12/2002'.

I'm assuming that DateStamp is a datetime field so it stores the date as well as the time eg: 2002-12-16 13:36:30.700.

SQL Server seems to have its own way of storing dates. I used 103 when converting to varchar so that it would accept the dd/mm/yy format in your post. There are more in BOL under "CONVERT" > "datetime and smalldatetime".
eg:

101 = mm/dd/yy
102 = yy.mm.dd

Hope this helps

John

 

[tlbroadbent]

Why not just convert the hard coded date to a format SQL Server understands. When you use a function on a column in the Where clause, SQL Server cannot use an index. It must evaluate every row to check for a matching value. Granted, in this case, the other criteria in the Where clause may provide sufficient selectivity to mitigate this problem but in general I like to avoid using functions on columns in the criteria unless it is absolutely required.

UPDATE v4_StatusLog SET [minutes]=301
WHERE PC_ID =125 AND PhoneId=1
AND DateStamp = '12/16/2002'

Alternate acceptable formats: '2002-12-16', 'dec 16 2002'

You can also use a function on the value which is useful if the value is being passed in a stored procedure call. SQL only needs to convert the literal or variable once so this is much more efficient than converting the date in the column to a string. Note that the style number is used to convert from a British format date string to a SQL datetime data type.

Convert a literal:
AND DateStamp = convert(datetime, '16/12/2002', 103)

Convert a variable:
AND DateStamp = convert(datetime, @strDate, 103)

I explain some of the issues with dates in SQL Server on my website. I'd appreciate feedback on the information provided there.

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[dogramone]

Another option is to always use dd mmm yyyy format for inserting your date. I find SQL resolves this correctly no matter what date settings the server has.