Insert Datetime.now from asp.net into sql server

Question

I'm trying to insert 'DateTime.Now' into sql column type of datetime but when insert is done in sql table date is OK! (exp: 2015-02-11) but time is stored as 00:00:00!!! I tried to many alternates. this is my code:

sqlComm.Parameters.Add("@join_date", SqlDbType.Date).Value = DateTime.Now.ToString("yyyy-MM-dd h:m:s");

What am I missing???

msdn.microsoft.com/en-us/library/8kb3ddd4%28v=vs.110%29.aspx — Chris
– Chris, Commented Feb 11, 2015 at 18:11

Jon Skeet · Accepted Answer · 2015-02-11 18:10:09Z

9

You're specifying a SqlDbType of Date - that doesn't have a time. So when the driver is converting the parameter value into the desired type, it's removing the time part.

You're also converting DateTime.Now into a string for no obvious reason... and doing so in a way which uses an odd and lossy time format (h:m:s, with no AM/PM designator). If you really did want to convert to a string, it would be better to use HH:mm:ss for the time specifier.

This line:

sqlComm.Parameters.Add("@join_date", SqlDbType.Date).Value = DateTime.Now.ToString("yyyy-MM-dd h:m:s");

should be:

sqlComm.Parameters.Add("@join_date", SqlDbType.DateTime).Value = DateTime.Now;

answered Feb 11, 2015 at 18:10

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amin Bakhtiari Far Over a year ago

yes-thanks! Dear Jon Skeet time portion was added in a later revise so I forgot to change the DBType and string conversion was one of my alternations. any way thank you very much.

Community · Accepted Answer · 2017-05-23 12:00:21Z

Jon's answer is fine, but also, you can make things easier like this:

sqlComm.Parameters.AddWithValue("@join_date", DateTime.Now);

It will automatically use the correct data type, based on the type of the value.

Also - You said you were calling this from ASP.Net. In web applications, DateTime.Now is usually not appropriate - because it uses the time zone of the server, and it doesn't retain any offset information so it can be ambiguous during daylight saving time fall-back transitions. I recommend storing UTC-based values in the database (DateTime.UtcNow).

If you feel that the server's local time zone is relevant in your use case, then consider switching your database column to the datetimeoffset type and using DateTimeOffset.Now. That will avoid problems with DST transitions.

Collectives™ on Stack Overflow

Insert Datetime.now from asp.net into sql server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related