Insert into Table getdate() rounds seconds in ASP.net

Question

I have a logs table with a smalldatetime column.

When I'm inserting into the table the value of GETDATE() the data is inserted without the seconds.

Example result: 2017-01-15 15:20:00

What am I missing?

proplem with you data type . can you use datetime instead – Yashveer Singh Jan 15 '17 at 13:47 — Yashveer Singh, Jan 15 '17 at 13:47

score 2 · Accepted Answer · answered Jan 15 '17 at 13:48

From MSDN

smalldatetime

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

If you want seconds then use DATETIME datatype instead of smalldatetime

DECLARE @smalldatetime SMALLDATETIME = '1955-12-13 12:43:10',
        @datetime      DATETIME = '1955-12-13 12:43:10'

SELECT @smalldatetime as [SmallDateTime],
       @datetime as [DateTime]

Result :

+---------------------+-------------------------+
|    SmallDateTime    |        DateTime         |
+---------------------+-------------------------+
| 1955-12-13 12:43:00 | 1955-12-13 12:43:10.000 |
+---------------------+-------------------------+

score 0 · Answer 2 · edited May 23 '17 at 12:24

For Sql Server 2008 and above:

Use datetime2(p) instead of smalldatetime and sysdatetime() instead of getdate().

datetime is 8 bytes; 1753-01-01T00:00:00.000 to 9999-12-31T23:59:59.997 (time rounded to .000, .003, .007)

datetime2(2) is 6 bytes; 0001-01-01T00:00:00.00 to 9999-12-31T23:59:59.99

datetime2(4) is 7 bytes; 0001-01-01T00:00:00.0000 to 9999-12-31T23:59:59.9999

datetime2(7) is 8 bytes; 0001-01-01T00:00:00.0000000 to 9999-12-31T23:59:59.9999999

Note: When adding a .NET DateTime as a parameter, use System.Data.SqlDbType.DateTime2. datetime2 can store any value that can be stored in the .NET DateTime type.

Learn more:

Insert into Table getdate() rounds seconds in ASP.net

2 Answers2