I have a stored procedure that gets data from a database that utilizes a column with type datetime (there is a valid time value in it) and it updates a table in another database that happens to use the smalldatetime type. When trying to convert from the datetime type to the smalldatetime type, I'm losing the time portion of the date. For some strange reason the date is being rounded up by a day on me. Is there some sort of special convert statement from datetime to smalldatetime that preserves the time portion of that date value?
Asked
Active
Viewed 207 times
0
-
Please provide sample data, the results you are getting, and the code producing those results. – Gordon Linoff Jan 07 '19 at 22:39
-
It's an update statement where I'm getting values from a view and updating a table. I'm trying to get one of the date values that has a time of 23:59:59 to not round up to the next day when updating the smalldatetime in the table. So when the update happens, as an example, instead of updating the smalldatetime column with the value 2019-03-03 23:59:59 as intended, it is going to 2019-03-04 00:00:00. – CurlyShuffle Jan 08 '19 at 16:53
-
Which DBMS product are you using? Neither `datetime` nor `smalldatetime` are data types in standard SQL. – Jan 08 '19 at 20:52
1 Answers
0
Apparently after digging around more I found the answer to my question. The smalldatetime data type rounds the seconds. If it's :29 and below, it goes to the previous minute. If it's :30 and above, it goes to the next minute. I wasn't aware of this previously. What's really goofy is that you still see the seconds value in the database even though it's always :00.
CurlyShuffle
- 135
- 13
-
A simple trick would be to just use an expression like `cast(dateadd(second, -30,
) as smalldatetime` (though the rounding behavior is still buried.) Alternately you can just do: `cast(dateadd(second, -datepart(second, – shawnt00 Jan 08 '19 at 17:57