Post reply

Need help with DATE should be dynamic for every day summary.

  • July 3, 2015 at 4:56 am

    #99042

    Here the date is hardcoded in the script, need to help with dynamic date for everyday day summary 12AM - 4PM

    Thanks in advance!

    AND t.StartExport BETWEEN CAST('2015-07-02 00:01:00.000' AS DATETIME) AND CAST('2015-07-02 16:00:00.000' AS DATETIME) –--- Here the date should be dynamic for every day ticket summary 12AM – 4PM.

  • July 3, 2015 at 5:25 am

    #1810172

    select dateadd(dd, datediff(dd, 0, getdate()), 0)

    select dateadd(hh,16,dateadd(dd, datediff(dd, 0, getdate()), 0))

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 3, 2015 at 5:32 am

    #1810175

    AND t.StartExport BETWEEN CAST(left(convert(varchar,CURRENT_TIMESTAMP,120),10)+' 00:01:00.000' AS DATETIME) AND CAST(left(convert(varchar,CURRENT_TIMESTAMP,120),10)+' 16:00:00.000' AS DATETIME)

  • July 6, 2015 at 10:11 am

    #1810511

    Thanks for all your replies

  • July 7, 2015 at 8:56 am

    #1810727

    Hi Palotaiarpad,

    Thanks for all your help.

    how does it work for below statement from 4PM to next day 6AM

    ANDt.StartExport BETWEEN CAST('2015-07-06 16:00:00.000' AS DATETIME) AND CAST('2015-07-07 06:00:00.000' AS DATETIME)

  • July 7, 2015 at 9:03 am

    #1810733

    AND t.StartExport BETWEEN CAST(left(convert(varchar,CURRENT_TIMESTAMP,120),10)+' 16:00:00.000' AS DATETIME) AND CAST(left(convert(varchar,dateadd(day,1,CURRENT_TIMESTAMP),120),10)+' 06:00:00.000' AS DATETIME)

  • July 7, 2015 at 9:20 am

    #1810736

    Awesome thank you!

  • July 7, 2015 at 9:40 am

    #1810748

    Hi Palotaiarpad,

    the date logic is used in SQL agent t-sql code which runs @ 6AM in morning and exports a CVS file.

    when the job runs at 6AM it should extract data from 4PM(yesterday) to till date 6AM.

    ANDt.StartExport BETWEEN CAST('2015-07-06 16:00:00.000' AS DATETIME) AND CAST('2015-07-07 06:00:00.000' AS DATETIME)

    I guess in your date logic it is taking current time to yesterday date right ?

  • July 7, 2015 at 10:01 am

    #1810755

    Ranzz (7/7/2015)

    Hi Palotaiarpad,

    the date logic is used in SQL agent t-sql code which runs @ 6AM in morning and exports a CVS file.

    when the job runs at 6AM it should extract data from 4PM(yesterday) to till date 6AM.

    ANDt.StartExport BETWEEN CAST('2015-07-06 16:00:00.000' AS DATETIME) AND CAST('2015-07-07 06:00:00.000' AS DATETIME)

    I guess in your date logic it is taking current time to yesterday date right ?

    Try this:

    ...

    AND t.StartExport BETWEEN dateadd(hour,-8,dateadd(day,datediff(day,0,getdate()),0)) AND dateadd(hour,6,dateadd(day,datediff(day,0,getdate()),0))

    ...

  • July 7, 2015 at 10:15 am

    #1810760

    Thanks Lynn it worked!

Viewing 10 posts - 1 through 9 (of 9 total)

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