Unanswered: Procedure to Select entries from yesterdays date
I currently have a stored procedure that selects sales entries from a table and inserts them into a temp table.
To execute this procedure you must enter the store number and the dates required.
What i am looking to do is run this procedure daily for one store daily to load the previous days sales into the temp table.
i have tried using the following
DECLARE @dt DATETIME
SET @dt = CAST(CAST(MONTH(getdate())as varchar) + '-' +
Cast(DAY(GetDate()-1)as varchar)+ '-' +
Cast(YEAR(GetDate())as varchar)as DateTime)
set @sql = @sql + ' and hd.TXHD_START_DATE between ''''' + @dt + ''''' and ''''' + @dt + '23595999+9999'''''
But i am getting an error
Syntax error converting datetime from character string.
DECLARE @dt datetime
SET @dt = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
SELECT @dt As today_at_midnight
WHERE date_field >= DateAdd(dd, -1, @dt)
AND date_field < @dt
Thanks for the reply
I forgot to mention that i need the date to be in [YYYYMMDD] format.
What change would i need to make to your code to return this?
i have tried using something like
select CONVERT(VARCHAR(8), GETDATE()-1, 112) AS [YYYYMMDD]
with your code but it is giving me an error with 'AS'