New DateAdd functionality - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

New DateAdd functionality

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2009-01-26 : 07:08:25

Has anyone noticed the new microsecond and nanosecond options?
how will this work?
I thought most server clocks don't generate this level of precision?
It only stores in increments of 0.003
EG:

declare @a datetime
set @a=getdate()
print 'Original Date:'
print convert(varchar(50),@a,113)
print ''

print 'Makes no difference adding one millisecond:'
print convert(varchar(50),dateadd(ms,1,@a),113)
print ''

print 'Two or more milliseconds work:'
print convert(varchar(50),dateadd(ms,2,@a),113)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 07:20:05

You have 100 nanosecond timeresolution with SQL Server 2008.
It is well documented in Books Online.

As before, the very best bet to use date ranges are open-ended ones.

SELECT * FROM Table1 WHERE Col1 >= '2009-01-26' AND Col1 < '2009-01-27'

will work regardless of resolution.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 07:21:03

[code]declare @a datetime2
set @a=getdate()
print 'Original Date:'
print convert(varchar(50),@a,113)
print ''

print 'Makes no difference adding one millisecond:'
print convert(varchar(50),dateadd(ms,1,@a),113)
print ''

print 'Two or more milliseconds work:'
print convert(varchar(50),dateadd(ms,2,@a),113)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources