June 17, 2010 at 1:35 pm
select '2009-09-15',
'10:54:08:531',
'2009-09-15' + ' ' + LEFT('10:54:08:531',12),
CAST('2009-09-15' + ' ' + LEFT('10:54:08:531',12) AS DATETIME)
why are the miliseconds changing??
June 17, 2010 at 1:45 pm
Because datetime has an accuracy of 3 milliseconds. It cannot store 351 milliseconds, only 350 or 353.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2010 at 2:04 pm
awesome. I thought I was losing my mind.
I should of looked at BOL 1st.
But how to get around this? any way? I am dealing with a high insert OLTP that stores in VARCHAR and unable to change that (shrink wrap) now I must report on it and wanted to use datetime for my repository table and am getting this change which then can put a oper or closed date to be the same becaused of the speed of the system they are only that far apart... which then puts the close date before the open date due to this rounding...
June 17, 2010 at 2:09 pm
Use the DATETIME2 datatype in SQL Server 2008.
June 17, 2010 at 2:19 pm
We are not approved to move that server to SQL 2008.
June 17, 2010 at 2:24 pm
Create a custom CLR type and re-implement all of the datetime functions? Store the miliseconds in a separate column?
There's no easy (or even really practical) way on SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2010 at 4:10 pm
It's really ugly, but you could drop all open ms down to the next-lower ms break and bump up all close ms down to the next-higher ms break. [No human likely cares about the ms anyway :-).]
If needed, you could separately retain the original varchar value for comparison/matching to the original data.
Scott Pletcher, SQL Server MVP 2008-2010
June 18, 2010 at 4:24 am
You can store it as meaningfull value available for comparisons in "where" clause, but it cannot be datetime, so no datetime functions will be applicable.
What you do is: format it into "YYYYMMDDHHMMSSMMM" and store it as bigint, so your 2009-09-15 10:54:08.531 will became
20090915105408531 and 2009-09-15 10:54:08.530 will be 20090915105408530.
June 18, 2010 at 4:34 am
Please, no. Storing dates as integers in that format makes any form of manipulation far harder than it needs to be. If the date can't be stored as a date due to precision issues, store it as a string. The fact that MSDB uses the 'datetime as integer' is not a recommendation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2010 at 4:46 am
Absolutely agree.
However:
1. If you really need to use something to identify what happen before what, and that difference is down to millisecs and no other datetime functions are required (and it's not SQL2008 :-D), use of BIGINTcan be acceptable. It would be similar to having identity column, ability to indicate date and time if required.
2. You cannot store date and time in INT. However, if you need just a date portion of it, use of INT is fine and quite common in large datawarehouses (as a key of Date dimension). It gives savings of 4 bytes per value, benefits in performance of comparisons and joins. Also, INT is implicitly convertable to datetime!
My rule is: Never say never.
😀
June 18, 2010 at 4:55 am
GilaMonster (6/18/2010)
...If the date can't be stored as a date due to precision issues, store it as a string. ....
How this would help for working with milliseconds? As soon as you will start using datetime functions, they will be rounded away anyway.
The only difference will be in presentation, however it will cost more in terms of storage and performance.
If, for example you will want to pass this value to the GUI, it will still need to be passed as string (converting to datetime rounds precious milliseconds), string comparison will be slower and trying to compare them as datetime - see the above rounding issue.
Am I missing something or what the benefits of having it as a string then other than presentation?
I might be wrong as I don't know everything...
Or rightly to say: I know nothing except the fact of my ignorance
(not me, it's Socrates):-D
June 18, 2010 at 6:12 am
Eugene Elutin (6/18/2010)
Also, INT is implicitly convertable to datetime!
DECLARE @NotADate INT, @ADate DATETIME
SET @NotADate = 20100618
SET @ADate = @NotADate
When an int is cast to a datetime, SQL takes the value in the int as the number of days since 1900. To convert an integer that contains a date as YYYYMMDD requires first converting it to string in order to not get an arithmetic overflow or nonsense result.
How this would help for working with milliseconds? As soon as you will start using datetime functions, they will be rounded away anyway.
Same with any other format if you convert to datetime. If, however, it's just used to store and compare (with the idea that the milliseconds have to be preserved) it's OK (I'd prefer datetime2, but that's only SQL 2008).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2010 at 6:23 am
July 1, 2010 at 12:36 pm
Still doesnt help with Milliseconds.
So basically I am SOL without 2008:(
I am looking more into what my customer needs this for. Just comparing, that would be find to compare as int but display, no way.
July 1, 2010 at 12:50 pm
mberry 51447 (7/1/2010)
Still doesnt help with Milliseconds.So basically I am SOL without 2008:(
Store as a varchar, display as a varchar, hope that you don't need any date manipulation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply