Post reply

Query for yesterday's records shifted one hour

  • March 23, 2010 at 3:58 pm

    #222183

    Hi everyone. I have been asked to design a select query that can be run daily to show the records with a time stamp of the day before.

    I was using this WHERE to get yesterday's records:

    WHERE datediff( d,INSERT_DATE,getdate()) = 1)

    It turns out that some processing does not finish until 1AM so they want the query to to find 1:00:00AM until 12:59:59AM instead of 12:00:00AM - 11:59:59PM. Does anyone know of a way to do this?

    Thanks a lot. I always learn so much here.

    Howard

  • March 23, 2010 at 4:24 pm

    #1138597

    So you want 1 day's worth of data beginning with yesterday >= 1:00 AM and up until today <= 12:59 PM?

    This should do it:

    DECLARE @Table TABLE (RowID int IDENTITY(1,1) NOT NULL, RowDate datetime)

    INSERT INTO @Table(RowDate)

    SELECT '2010-03-21 23:59:59.000' UNION ALL

    SELECT '2010-03-22 00:59:57.000' UNION ALL

    SELECT '2010-03-22 01:00:08.000' UNION ALL

    SELECT '2010-03-22 23:59:59.000' UNION ALL

    SELECT '2010-03-23 01:01:01.000'

    DECLARE @StartDate datetime,

    @EndDate datetime

    SELECT @StartDate = CAST(SUBSTRING(CONVERT(varchar,DATEADD(d,-1,GETDATE()),121),1,10) + ' 01:00:01' as datetime),

    @EndDate = CAST(SUBSTRING(CONVERT(varchar,GETDATE(),121),1,10) + ' 01:00:00' as datetime)

    SELECT *

    FROM@Table

    WHERERowDate BETWEEN @StartDate AND @EndDate

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 23, 2010 at 4:42 pm

    #1138600

    Thanks John, I actually want => 1am last night until <1am this morning to be run every day. I think that is what your code is doing.

    Thanks a lot for writing that out. I'm going to work with it in my query. Looks really good.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply