October 3, 2007 at 7:20 pm
I have a couple of datetime fields that I'd like to turn into smalldatetime fields. The problem? They all have milliseconds attached to them. Mind you, the time portion of the datetimefield is literally 00:00:00:000, but no matter what I try I cannot seem to truncate off those milliseconds.
I convert to a char(10) field, then convert back to smalldatetime. The second conversion fails with "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value".
I tried the Left( ,10) function. It doesn't work. I tried the Replace() function to change it over to a 00:00:00, and this doesn't work. Time after time, I get the same darn error message. Even after pulling the info into a temp table and adding two sets of extra columns (one smalldatetime, the other char(10)) in an attempt to fix this problem.
GAH! :crazy: There has to be a way to chop off these milliseconds!!! Anyone have any thoughts?
October 3, 2007 at 7:27 pm
I'm not sure I get your problem. Can you post sample data??
This is what I have, but you obviously would have thaught of that option, so I guess I'm missing something here.
SELECT CONVERT(SMALLDATETIME, GETDATE())
--2007-10-03 21:26:00
October 3, 2007 at 7:34 pm
Do you have dates outside of that range??
January 1, 1900, through June 6, 2079
October 3, 2007 at 9:08 pm
you could convert to int, which would be the number of days since the beginning of time(according to sql server that's 1900-01-01 00:00:00.000)
SELECT getdate(),CONVERT(int, GETDATE())
2007-10-03 23:07:05.640 39357
ints are easier to compare, and you can still do math on them if you need differences or adding days.
as you know, if you convert to double, the hours are in the decimal portion of the results, so this effectively cuts off the time.
Lowell
October 4, 2007 at 5:19 am
Oh, DUH! Ninja, you're right. I do have dates outside of that range. And here I thought it was the stupid millisecond thing.
Although, how anyone came up with an Expiration date of 08/21/4221 on a product is BEYOND me... @=Pbbt
Thanks, Ninja! You're a gem! And this just goes to show that working a 12 hour workday will screw with a person's head. Especially when already suffering a killer head cold. @=)
October 4, 2007 at 9:01 am
I can't imagine how confident in you have have to be to assume that the product will endure for over 2000 years. I can only wish them the best of luck!
October 4, 2007 at 9:07 am
They must have made it out of the same material as the Great Pyramids of Giza. @=)
October 4, 2007 at 9:13 am
No wonder that stood up for so long... there's no users allowed in the system except for the admin... and he ain't moving a finger, just to make the thing doesn't die on him :w00t:.
October 5, 2007 at 6:08 am
Brandie Tarvin (10/4/2007)
They must have made it out of the same material as the Great Pyramids of Giza. @=)
I just assumed that you were talking about a twinkie
October 5, 2007 at 6:16 am
HA!
The twinkie comment just made my day. @=)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply