Custom SQL reporting between two dates

[Supra_James]

Hi All!

I have OpsCenter 7.5.0.6 running on Windows Server 2003. I have the Analytics license and have been writing a custom SQL report with the help of the schema document supplied by Symantec.

I wonder if anyone could help me work out how to limit my report to data between two given dates? The example below runs but returns ALL dates in the Backup Time column, not just the dates in September that I require.

I found the syntax for 'between '01/Sep/13' and '30/Sep/13'' from some random Stack Overflow page so I could be barking up the wrong tree completely here... but I'm a backup admin not an SQL dev :)

Anyone got any hints?

SELECT 
domain_MasterServer.friendlyName as 'Master',
domain_SLPImage.clientName as 'Client',
domain_SLPImage.id as 'Backup ID',
domain_SLPImage.policyName as 'Policy',
UTCBigIntToNomTime(domain_SLPImage.writeStartTime) as 'Backup Time',
UTCBigIntToNomTime(domain_SLPImage.slpCompletionTime) as 'Duplication Time'
FROM domain_SLPImage,domain_MasterServer
WHERE domain_MasterServer.id=domain_SLPImage.masterServerId
AND UTCBigIntToNomTime(domain_SLPImage.writeStartTime) between '01/Sep/13' and '30/Sep/13'
ORDER by domain_SLPImage.id;

[SRP]

Supra

Would changing:

AND UTCBigIntToNomTime(domain_SLPImage.writeStartTime) between '01/Sep/13' and '30/Sep/13'
 

To:

AND UTCBigIntToNomTime(domain_SLPImage.writeStartTime) between 'Sept 1 2013' and 'Sept 30 2013'
 

work?

Sean

[Supra_James]

Hi Sean, that does seem to work! May I ask how you knew the correct date format?

[SRP]

Supra

I believe that the format is the "normal" date format.

Change:

UTCBigIntToNomTime(domain_SLPImage.writeStartTime) as 'Backup Time',
 

To:

Convert(varchar(20), UTCBigIntToNomTime(domain_SLPImage.writeStartTime),120) as 'Backup Time',

And the date will appear differently (yyyy-mm-dd hh:mm:ss)

If you change 120 to another number, a different format can be produced (check on a SQL website -some may not be available on OpsCenter, however).

Have Fun

Sean

[Supra_James]

Thanks - just what I needed! :)