Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    9

    Unanswered: Procedure to Select entries from yesterdays date

    Hi All
    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.

    Can anyone help me with this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @dt datetime
        SET @dt = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
    
    SELECT @dt As today_at_midnight
    
    SELECT list
         , of
         , columns
    FROM   your_table
    WHERE  date_field >= DateAdd(dd, -1, @dt)
    AND    date_field <  @dt
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you are using SQL Server 2008, you can just use the Date datatype, too.

    Code:
    DECLARE @dt date
    SET @dt =  getdate()

  4. #4
    Join Date
    Jan 2011
    Posts
    9
    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'

  5. #5
    Join Date
    Jan 2011
    Posts
    9
    sorry, i have got it now using
    DECLARE @dt datetime
    SET @dt = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
    SELECT CONVERT(VARCHAR(8), @dt, 112) AS [YYYYMMDD]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •