CODEWITHSUNDEEP
sqlsql-serversql-server-2005ssis
Lily
Lily

Reputation: 99

Sql query to select data from the day before at 4:15:01 pm to today at 4:15:00 pm

I'm building a SSIS package where I need to get data from the day before at 4:15:01 pm to today's date at 4:15:00 pm, but so far the only query that I know is how to get the day before. I am not sure how to also add hour, minute, and second to the same query. Can someone please show me how to add the hour, minute, and second to this sql query? Below is the query I have so far. 

SELECT Posted_Date, Total_Payment FROM   
Table1
WHERE Posted_Date >= dateadd(day, datediff(day, 1, Getdate()), 0)
and Posted_date < dateadd(day, datediff(day, 0, getdate()), 0)
order by posted_date

Upvotes: 1

Views: 1364

Answers (3)

anon
anon

Reputation:

Be very careful about precision here - saying you want things from 4:15:01 PM yesterday, means that at some point you could possibly lose data (e.g. 4:15:00.500 PM). Much better to use an open-ended range, and I typically like to calculate that boundary outside of the query:

DECLARE @today DATETIME, @today_at_1615 DATETIME;

SELECT @today = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), 
       @today_at_1615 = DATEADD(MINUTE, 16.25*60, @today);

SELECT Posted_Date, Total_Payment
FROM dbo.Table1
WHERE Posted_Date > DATEADD(DAY, -1, @today_at_1615)
  AND Posted_Date <= @today_at_1615
ORDER BY Posted_date;

You should also avoid using DATEDIFF in queries like this - there is a cardinality estimation bug that can really affect the performance of your query. I don't believe the bug affects SQL Server 2005, but if you wanted to be ultra-safe you could change it to the slightly more expensive:

SELECT @today = CONVERT(CHAR(8), GETDATE(), 112),

And in either case, you should mark this code with some kind of flag, so that when you do get onto SQL Server 2008 or later, you can update it to use the much more optimal:

SELECT @today = CONVERT(DATE, GETDATE()),

SSIS things

I created an SSIS package with an Execute SQL Task that created my table and populated it with data which was then used by a Data Flow Task

enter image description here

Execute SQL Task

I created an Execute SQL Task, connected to an OLE DB Connection Manager and used the following direct input.

-- This script sets up a table for consumption by the DFT
IF EXISTS
(
    SELECT * FROM sys.tables AS T WHERE T.name = 'Table1' AND T.schema_id = SCHEMA_ID('dbo')
)
BEGIN
    DROP TABLE dbo.Table1;
END;
CREATE table dbo.Table1
(
    Posted_Date datetime NOT NULL
,   Total_Payment int NOT NULL
);

INSERT INTO 
    dbo.Table1
(
    Posted_Date
,   Total_Payment
)
SELECT
    DATEADD(minute, D.rn, DATEADD(d, -1, CURRENT_TIMESTAMP)) AS Posted_Date
,   D.rn
FROM
(
    -- 2 days worth of data
    SELECT TOP (60*24*2)
        DI.rn
    FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
            FROM sys.all_columns AS AC
        ) DI
) D;

Right click on the Execute SQL Task and execute it. This ensures the table is created so that we can work with it in the next step.

Data Flow Task

I created a Data Flow Task and set the DelayValidation property to True since my connection manager is pointed at tempdb. This is likely not needed in the real world.

I added an OLE DB Source component and configured it to use the first query

enter image description here

I then added a Derived Column to allow me to attach a data viewer to the flow and fired the package off. You can observe that the last value streaming through is as expected.

enter image description here

Upvotes: 8

Anon
Anon

Reputation: 10908

Another way of asking the same question: Show me the data that would have posted today if it had been posted 7 hours, 44 minutes, and 59 seconds later.

SELECT Posted_Date, Total_Payment FROM   
Table1
WHERE CAST(DATEADD(second,7*60*60+44*60+59,Posted_Date) AS date) = CAST(GETDATE() as date)

This will smoothly handle fencepost issues, fractional seconds, and daylight savings, and it will be very fast with an index on CAST(DATEADD(second,7*60*60+44*60+59,Posted_Date) AS date)

Upvotes: -6

user3064580
user3064580

Reputation: 9

I'd think this would work similarly. 

DECLARE @today DATETIME, @today_at_1615 DATETIME;
SELECT @today = CONVERT(DATE, GETDATE()), 
    @today_at_1615 = DATEADD(MINUTE, 16.25*60, @today);

SELECT Posted_Date, Total_Payment
FROM dbo.Table1
WHERE Posted_Date between DATEADD(DAY, -1, @today_at_1615) AND @today_at_1615
ORDER BY Posted_date

Would've made a comment about using BETWEEN but I don't have the reps for it. Is there a reason for not using the between clause?

Upvotes: 0

Related Questions