Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Can anybody help me with an SQL expression please ?

JFLET2

I'm writing custom reports using SQL, but am having a problem with defining time/date expressions.

I want to define a datetime covering "last month".

The builtin reports generate something like :-
WHERE
( DateTime BETWEEN 39171 AND 39200.9999884259 )

This is not very useful for hard coded SQL reports, so for September (last Month),

I manually created the following :-

WHERE
( DateTime BETWEEN DATEADD(month, 0, '09/01/2007') AND DATEADD(month, 0, '09/30/2007') )

This works fine but obviously needs changing every month

What I need is a generic expression which will match for the whole previous calender month automatically.

(I've seen some examples using datediff, etc, but have been unable to get them working within Orion)

Thanks,

Julian Fletcher

Find more posts tagged with

custom_report

Accepted answers

All comments

mark_wiggans

Julian-

check the Content Sharing Zone for some examples of 30 day reports-

thwack.com/.../default.aspx

JFLET2

Thanks Mark for the prompt reply, much appreciated.

Unfortunately, all the example files are based on matching the last n=30 days.

This would work if I ran the report on the 1st of each month.

I need to be able to run the report anyday mid month to match for the whole previous calender month.

Hope that clarifies what I'm trying to achieve.

Cheers,

Julian

kbash

The GetDate() function will return the current date/time, so from there it's just a matter of calculating the first and last day of the month. Some good examples are here: http://www.databasejournal.com/features/mssql/article.php/3076421

Taking the expressions from that page, your WHERE clause would be:

WHERE
(DATETIME BETWEEN
(select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
AND
(select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))
)
Note that this is for the current month, you'd need to modify the DATEDIFF(m,0 to DATEDIFF(m,-1 in both places for the previous month. Per the example on the linked page, the last day of the month is calculated by subtracting a few milliseconds from the first day of the following month. If you were to subtract a whole day, you'd get a 00:00 time stamp. If you really wanted to clean up your query, you could define these as User Defined Functions.

JFLET2

Thanks for the reply.

>you'd need to modify the DATEDIFF(m,0 to DATEDIFF(m,-1 in both places

In the example code you quote, should the letter be m or mm? (are they the same?)

to test this out I print the calculated returned datetime as a column in a report,

------------------------------------------------------------

convert(char,DATEADD(mm, DATEDIFF(m,0,getdate()), 0),0) as Hello,
convert(char,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)),0) as Hello2,

returns Hello as Oct 1 200712:00AM

returns Hello2 as Oct 31 2007 11:59PM

Works Fine

--------------------------------------------------------------

convert(char,DATEADD(mm, DATEDIFF(m,-1,getdate()), 0),0) as Hello,
convert(char,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,-1,getdate() )+1, 0)),0) as Hello2,

returns Hello as Nov 1 200712:00AM

returns Hello2 as Nov 30 2007 11:59PM

Which is a month forward rather than backward.

--------------------------------------------------------------

I've tried changing the value to +1 rather than 0, or -1 but that doesnt generate a September result

What could I try next ?

Thanks,

Julian