SQL Date question

[Jakel]

I have a database that receives an import of data nightly from another database. The problem is that there is a column that is simported with no value, so I need to change the value in that field for all new records. There is a column for the Date and time the new record was added.

Since I dont know what records came over each night I need to be able to select all records added since yesterday. That is where I'm stuck, this is what I have so far:

update TABLE
set COLUMN ='VALUE'
where AddingDateTime > (24 hours ago)-this is the value I dont know how to get.

What command do I use to get the date/time 24 hours ago.

Thanks in advance

 

[bborissov]

Check DateAdd function in BOL. And what you mean with 24 hours ago?
If NOW is "29 Mar 2008 12:30:55" is 24 hours ago is "28 Mar 2008 12:30:55" or it is just "28 Mar 2008 00:00:00"?
If you care about time portions then your UPDATE should look like this:

[COLOR=blue]DECLARE[/color] @dt24Ago [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @dt24Ago = [COLOR=#FF00FF]DateAdd[/color](hh,-24, [COLOR=#FF00FF]GETDATE[/color]()) [COLOR=green]-- minus 24 hours
[/color][COLOR=green]-- or SET @dt24Ago = DateAdd(dd,-1, GETDATE()) -- minus one day
[/color][COLOR=blue]UPDATE[/color] [COLOR=blue]Table[/color]
       [COLOR=blue]SET[/color] [COLOR=blue]Column[/color] =[COLOR=red]'VALUE'[/color]
[COLOR=blue]where[/color] AddingDateTime > @dt24Ago

If you want to get rid of time portion:

[COLOR=blue]DECLARE[/color] @dt24Ago [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @dt24Ago = [COLOR=#FF00FF]DateAdd[/color](dd,-1, [COLOR=#FF00FF]DateDiff[/color](dd,0,[COLOR=#FF00FF]GETDATE[/color]()))

[COLOR=blue]UPDATE[/color] [COLOR=blue]Table[/color]
       [COLOR=blue]SET[/color] [COLOR=blue]Column[/color] =[COLOR=red]'VALUE'[/color]
[COLOR=blue]where[/color] AddingDateTime > @dt24Ago

not tested well

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP