Using getdate() in a proc

[jwestmore]

I'm inserting records into a table on a daily basis from 3 source tables that are updated in real-time. I want to capture records entered between midnight to 11:59pm of the previous day.

Somthing like

INSERT INTO destination_table

SELECT * FROM source_tables
WHERE entry_date = dateadd(day,-1,getdate())

Problem is, the entry_date field and getdate() both include times as well as dates. Any suggestions on how to use getdate() excluding the time portion and simply returning the date?

Thanks in advance,
Jon

 

[NoCoolHandle]

convert(varchar(10),getdate(),1)

 

[SQLBill]

Why use DATEADD? Test this:

WHERE entry_date = getdate()-1

-SQLBill

 

[SonOfEmidec1100]

You can use this to return the previous days date as a datetime data type, with the time set to 00:00:00

WHERE entry_date = dateadd(dd,datediff(dd,0,getdate()),-1)

 

[SQLBill]

I think we all missed your point. We are showing you ways to use just the date in your WHERE clause. You want your RESULTS to have just the date.

If you want just the date returned from a datetime column, you can't use:
SELECT *

You will need to list out your columns (the bold part shows how to get JUST the date returned):

SELECT somecolumn, someothercolumn, convert(char(10),entry_date,1) as MyDate
FROM source_tables
WHERE entry_date = getdate()-1

Let us know if that's what you are looking for.

-SQLBill