Is this a bug in the DATEADD function

select top 5 timestamp,dateadd(second,11,timestamp)as SyncDate,event from logtable

returns

timestamp             SyncDate              event                                                                                                                                                                                                                                                            
--------------------- --------------------- -------------------------------------
2007-07-13 13:15:00   2007-07-13 13:15:00   Program Startup
2007-07-13 13:15:00   2007-07-13 13:15:00   CorpXfer database successfully opened
2007-07-13 13:15:00   2007-07-13 13:15:00   Initializing Alarm control...
2007-07-13 13:15:00   2007-07-13 13:15:00   Alarm Control disconnected from Host
2007-07-13 13:15:00   2007-07-13 13:15:00   Alarm Control connected to Host

select top 5 timestamp,dateadd(minute,11,timestamp)as SyncDate,event from logtable

returns

timestamp             SyncDate              event                                                                                                                                                                                                                                                            
--------------------- --------------------- ---------------------------------------------------------
2007-11-29 00:05:00   2007-11-29 00:16:00   059289 Sending C:\CorpXfer\LocalCopy\Accumulators\ACCUMULATORS20071129000500CST.TXT to DoverFTP
2007-11-29 00:05:00   2007-11-29 00:16:00   059289 Successfully connected to DoverFTP
2007-11-29 00:05:00   2007-11-29 00:16:00   Processing 059289 Hourly05 Accumulators
2007-11-29 00:05:00   2007-11-29 00:16:00   5 Minute Analogs for MOST item added to que. Filename = C:\CorpXfer\LocalCopy\5 Minute Analogs (MOST)\ANALOGS20071129000500CST.TXT
2007-11-29 00:05:00   2007-11-29 00:16:00   Hourly05 Analogs item added to que. Filename = C:\CorpXfer\LocalCopy\Analogs\ANALOGS20071129000500CST.TXT

The syntax I am using is obviously correct. Why does the second one correctly add 11 minutes to timestamp while the first does not add 11 seconds to timestamp? I think this is a bug but I can't believe something this simple ever made it into production.

rjdegraffAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris MangusConnect With a Mentor Database AdministratorCommented:
If your TIMESTAMP column is a data type of smalldatetime you won't have any seconds stored or used.
0
 
SidFishesCommented:
this works as expected

select getdate()
select dateadd(second, 11, getdate())

inclined to agree that it's a data formatting issue and not a bug
0
 
rjdegraffAuthor Commented:
Hah!

You are correct. I NEVER use SmallDateTime fields, however, this table was created automatically when I imported the data from an Access database. I changed the field to DATETIME and tried again. Success.

Thanks much.
0
 
ursangelCommented:
I too agree with cmangus reply.
let us know the datatype of your TimeStamp column. If its smalldatetime, then it will not work as u expect.
try this excampl and you can c teh difference. I have declared the @val variable as Datetime and it works perfectly, now you chnage the data type to smalldatetime and c how its behaving.

declare @val ldatetime
set @val = '2007-12-05'
select @val
select dateadd (ss ,10,  @val)
0
 
Chris MangusDatabase AdministratorCommented:
Is there anything else we can do for you on this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.