Run daily report for yesterday

Question

I need to nightly run a report at 1am for all the customers who visited the hotel yesterday. I am not understanding what I am doing wrong. I am trying as below but it doesn't give me any records. CheckInDate format is '2015-09-02 06:45:00.000'. Please assist. Thanks.

Select top 5 * from Customers where CheckInDate =  DATEADD(day, -1, GETDATE())

Below worked:

select top 5 * from Customers where cast(CheckInDate as date) = DATEFROMPARTS(YEAR(GETDATE()-1), MONTH(GETDATE()-1), DAY(GETDATE()-1))

You have hh:mm:ss part which may vary, convert both to DATE or use `BETWEEN date 00:00:00 AND date date 23:59:59` — Lukasz Szozda, Sep 03 '15 at 13:08
I did Select top 5 * from Customers where cast(CheckInDate as date) = DATEADD(day, -1, GETDATE())....still no results. — sky_limit, Sep 03 '15 at 13:16

Hussain Patel · Answer 1 · 2015-09-03T13:34:33.463

0

try this.

SELECT top 5 from Customers where CheckInDate = DATEADD(DAY,DATEDIFF(DAY,0,'2015-09-02 06:45:00.000'),0) - 1

The DateAdd function will convert the date into 2015-09-02 00:00:00.000 format and you subtract minus 1 day. i.e. 2015-09-01 00:00:00.000

you can pass the date in stored procedure as parameter and the rest will be taken care by the select statement

hope this helps..

edited Sep 03 '15 at 13:34

answered Sep 03 '15 at 13:25

Hussain Patel

460
3
10
24

I tried datefromparts and it worked. I updated the solution in my original question. The report needs to auto run at night so I can't use any manual date as '2015-09-02 06:45:00.000'. – sky_limit Sep 03 '15 at 13:33
1

Create and schedule a job in Sql Server . I would prefer writing a stored procedure, you can write the sql query also – Hussain Patel Sep 03 '15 at 13:37
here's the link from stackoverflow for scheduling a jon in sql server to run daily.. http://stackoverflow.com/questions/5471080/how-to-schedule-a-job-for-sql-query-to-run-daily – Hussain Patel Sep 03 '15 at 13:39

score 0 · Answer 2 · answered Sep 03 '15 at 13:36

0

select top 5 * from Customers where cast(CheckInDate as date) = DATEFROMPARTS(YEAR(GETDATE()-1), MONTH(GETDATE()-1), DAY(GETDATE()-1))

answered Sep 03 '15 at 13:36

sky_limit

133
2
11

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – JAL Sep 03 '15 at 19:39

score 0 · Answer 3 · answered Sep 03 '15 at 13:43

Is your yesterday a TranDate - 1? Maybe this should help.

select top 5 * from customers where checkindate 
BETWEEN
        CONVERT(DATETIME, CONVERT(VARCHAR(20),(GETDATE() - 1),101) + ' 00:00') -- start of the day or you can modify it base on your report cut-off
    AND CONVERT(DATETIME, CONVERT(VARCHAR(20),(GETDATE() - 1),101) + ' 23:59') -- until end of the day or '23:59' of the GETDATE() - 1

what this does is

(GETDATE() - 1),101) = '09/03/2015' then ' 00:00' then gives you '09/03/2015 00:00:00' then convert this to datetime again as filter

hopefully this helps.

Run daily report for yesterday

3 Answers3