inserting date into sql server

  • Thread starter Thread starter Paul
  • Start date Start date
P

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.
 
Paul said:
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?
Click to expand...

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,
 
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
 
Back
Top