getdate() - 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 2000 Forums

Transact-SQL (2000)

getdate()

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-21 : 05:45:58

Here's my sp. It is deleting records with today's date - why isn't
'and tblFileRequests.DateRequested < getdate()' working ????

CREATE PROCEDURE [spRMU_DeleteBlankRequests]

AS
DELETE tblFileRequests
FROM tblFileRequests
LEFT JOIN tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
WHERE tblFileRequestDetails.MovementId Is Null
and tblFileRequests.DateRequested < getdate()
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:49:15

1) Which datatype is column DateRequested?
2) GETDATE() also returns milliseconds

CREATE PROCEDURE [spRMU_DeleteBlankRequests]

AS
DELETE tblFileRequests
FROM tblFileRequests
LEFT JOIN tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
WHERE tblFileRequestDetails.MovementId Is Null
and tblFileRequests.DateRequested < dateadd(day, datediff(day, 0, getdate()), 0)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 05:49:29

What is the datatype for DateRequested? Varchar or Datetime?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 05:50:40

You must remember that getdate() returns datetime - which has a time attached to it too.

So if you are storing your data with a midnight time and the time now is say 12:52:00 then all of the midnight times and everything up to 12:51:59 for todays date and dates before today will be deleted.

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:53:31

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-21 : 05:54:49

DateRequested is Datetime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:57:17

Shiver... I feel we now enter Twilight Zone where OP does not read answers.

Pinto, try the query I suggested above.

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-21 : 05:57:21

Thanks peso, < dateadd(day, datediff(day, 0, getdate()), 0) is working fine now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 06:00:40

It is the DATEDIFF function that truncates time information by calculating whole days passed since January 1, 1900 (or days left until January 1, 1900).
And then DATEADD add the day difference to January 1, 1900 to get back to today's date, without time information.

Well, this is not entirely true. Time is midnight (00:00:00) or 12 pm for our american friends.

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-21 : 06:09:26

Thanks for the explanation, Peso. I was only storing the date in my table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-21 : 07:19:16

More on dates
www.sql-server-performaqnce.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-22 : 15:19:51

quote:
Originally posted by Pinto

Thanks for the explanation, Peso. I was only storing the date in my table.

Not with a DateTime column, you weren't.

For a DateTime column, SQL server stores both the date and time. If you don't supply a time value, it uses 12:00:00am. So, one way or the other, you're storing both date and time.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources