inserting date into sql server

[Paul]

I'm sure this isn't a difficult question, but I've been struggling trying to
insert the current date into a sql server field. I've tried numerous
suggestions that I've seen in newsgroups and everything either gives an
error or puts 1/1/1900 in the field. The code I'm trying is:

Dim sqlCMD As New SqlClient.SqlCommand("Insert Into tblNotificationStatus
(UserName, ExpirationDate) values ('" & strUser & "','" &
CDate(Now().ToShortDateString) & "')", sqlConn)

sqlCMD.ExecuteScalar()

I've tried just about any combination of date/time formats and delimiter
characters you can think of and just can't get this to work. This code
works if I remove the date field and just enter the user name.

Does anybody have any suggestions?

Thanks.

[Oenone]

Paul wrote:

I've tried just about any combination of date/time formats and
delimiter characters you can think of and just can't get this to
work. This code works if I remove the date field and just enter the
user name.
Does anybody have any suggestions?

When working with string representations of dates, always always use
ISO8601-style date formatting: "yyyy-mm-dd" or "yyyy-mm-dd hh:mm:ss". It's
the only date format that is definitively recognisable and non-ambiguous
(and has some other nice properties too such as automatically sorting into
the correct chronological order).

Try replacing your CDate() with:

\\\
[...] & Format(Now(), "yyyy-MM-dd HH:mm:ss") & [...]
///

(Note that the values passed to Format are case-sensitive, so "MM" returns
the month and "mm" the minutes).

If you want to omit the time part, just use a format of "yyyy-MM-dd".

HTH,

--

(O)enone

[The Grim Reaper]

There's a trick to that! ... Use the built in SQL function GetDate(), like
this...

Dim sqlCMD As New SqlClient.SqlCommand("INSERT INTO tblNotificationStatus
(UserName, ExpirationDate) VALUES ('" & strUser & "', GETDATE())", sqlConn)

sqlCMD.ExecuteScalar()

I don't know what everyone else's opinion is (but this is the place to find
out!!), but I find this farrrr easier than farting about with converting a
date/time/now into a string (usually in American format).
BTW, when dealing with dates in SQL as strings, I prefer the completely
unambiguous '2006 Oct 31 20:24:03' format.
______________________________________
The Grim Reaper

"Paul" <cs*********@hotmail.comwrote in message
news:O2*************@TK2MSFTNGP03.phx.gbl...

I'm sure this isn't a difficult question, but I've been struggling trying
to insert the current date into a sql server field. I've tried numerous
suggestions that I've seen in newsgroups and everything either gives an
error or puts 1/1/1900 in the field. The code I'm trying is:

Dim sqlCMD As New SqlClient.SqlCommand("Insert Into tblNotificationStatus
(UserName, ExpirationDate) values ('" & strUser & "','" &
CDate(Now().ToShortDateString) & "')", sqlConn)

sqlCMD.ExecuteScalar()

I've tried just about any combination of date/time formats and delimiter
characters you can think of and just can't get this to work. This code
works if I remove the date field and just enter the user name.

Does anybody have any suggestions?

Thanks.