On my ASP page, with a SQL backend, I have the following code to insert a
date value into a table, but it is not working correctly.

sql = "UPDATE Problems SET FupDate = '" & updFuDate & "' WHERE TicketNumber
= " & tn & ""

The value updFuDate is currently 06/15/2005. The command works, however, it
keeps on putting a date of 01/01/1990 in the field.

Please help.

[quoted text, click to view]

Hi Syrac,

Check how the formatted sql looks (add some debug code in your ASP page
to show the string). My guess is that the standard formatting chosen by
ASP is not recognisable to SQL Server.

If you have to embed date constants in your SQL, make sure that you use
one of the following formats - the only ones that are gauarnteed to work
correct, regardless of locale settings:

* yyyymmdd (for date only)
* yyyy-mm-ddThh:mm:ss (for date plue time)
* yyyy-mm-ddThh:mm:ss.mmm (ditto, plus milliseconds)

Examples (using current date and current time in my timezone):

'20050614'
'2005-06-14T23:05:52'
'2005-06-14T23:05:52.997'

Best, Hugo
--

Syrac,

06/15/2005 is a numeric expression with value 6 divided by 15
divided by 2005, or zero, and it converts to January 1, 1990.

If you were to look at your query before running it (a good practice
during development), you would see that you are setting FupDate
with
.... SET FupDate = 06/15/2005

[quoted text, click to view]
.... SET FupDate = 0

You probably wanted this instead:
.... SET FupDate = '06/15/2005'

and in addition to putting the quotes in, you should
follow Hugo's advice, and use a date format that
doesn't depend on locale or date format settings.
You could also put an explicit convert to datetime
for a specific format in your query:

.... SET FupDate = convert(datetime,'06/15/2005',101)

Steve Kass
Drew University

[quoted text, click to view]