Re: TSQL Date question

On Tue, 12 Apr 2005 11:02:03 -0700, tag wrote:

>My order dates of service load from a text file and contain "today minus 1,
>plus 3". For example, using today's file, this equates to yesterday's date
>(4/11) through Friday's date (4/15). What I want to delete using a SQL
>command daily that requires no input from me is yesterdays info.
>
>How do I write a SQL command that states delete any date less than today? I
>know this is easy, but I've been struggling with it for about an hour now.
>
>I appreciate any help anyone can offer.
>
>thanks,
>tag

Hi tag,

CURRENT_TIMESTAMP or GETDATE() will get you the current date and time.
To reduce this to just the date (IOW to set the time portion to
midnight), you can use an expression like this:

DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP), '20040101')

You can use this to delete rows with a date value before the current
date:

DELETE FROM MyTable
WHERE TheDateColumn < DATEADD(day, DATEDIFF(day, '20040101',
CURRENT_TIMESTAMP), '20040101')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.