This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL for last 30 days - date calculation

StephenT over 18 years ago

Hi all,

I am creating a bespoke report based on the built-in Orion "last 30 days" report. In Report Writer the SQL is something like this:

SELECT .....
FROM .....
WHERE
( DateTime BETWEEN 87893 AND 87922 )

As the report is a 'rolling' 30 days, how does report writer calculate the 5-digit values above? When I look at the SQL in the writer app they have already been worked out...

Thanks ,

Stephen

Top Replies

gbutler over 14 years ago in reply to DonYonce +1

You know you are a nube when the answers you are getting are from user entries dated 2005! :-O Thanks Don... if you still are here.

0 DonYonce over 18 years ago

This is part of the undocumented magic of SQL. Orion converts these dates to numbers in order to easily support many different International date/time formats. You could just as easily use "( DateTime BETWEEN '10/1/2005' AND '10/5/2005' )" instead.

You could also use "( DateTime > (GetDate()-30) )" to select the past 30 days.
Cancel
Vote Up 0 Vote Down

Cancel
0 StephenT over 18 years ago

Excellent - that was just what I was after, Thanks Don
Cancel
Vote Up 0 Vote Down

Cancel
0 gbutler over 14 years ago in reply to DonYonce

You know you are a nube when the answers you are getting are from user entries dated 2005! :-O
Thanks Don... if you still are here.
Cancel
Vote Up +1 Vote Down

Cancel
0 ecklerwr1 over 14 years ago in reply to gbutler

That's pretty funny gb :}
Cancel
Vote Up 0 Vote Down

Cancel
0 FormerMember over 12 years ago in reply to DonYonce

What syntax should i use for last month report ?
Cancel
Vote Up 0 Vote Down

Cancel
0 glsmith67 over 8 years ago in reply to gbutler

Hey eleven years later and this thread is still showing me what I needed!
Cancel
Vote Up 0 Vote Down

Cancel
0 dc4networks over 6 years ago in reply to FormerMember

Satish,
Did you ever get an answer for this or resolve the issue yourself?
I would like to run a report on the 1st of every Month but to show the Previous Month and not the current New Month that it is.
Unfortunately for me the ${N=Generic;M=LocalMonthName} will only show the Current Month Name.
Thank You in Advance for your Support.
Kind Regards,
Dan
DC4Networks
Cancel
Vote Up 0 Vote Down

Cancel