By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,990 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,990 IT Pros & Developers. It's quick & easy.

inserting date into sql server

P: n/a
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.
Oct 31 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Oct 31 '06 #2

P: n/a
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.

Oct 31 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.