inserting date into sql server

31 Oct 2006 8:21 PM

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.

31 Oct 2006 8:45 PM

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

31 Oct 2006 10:49 PM

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

Show quoteHide quote

"Paul" <csstuden***@hotmail.com> wrote in message
news:O2NuroS$GHA.924@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.
>