cancel
Showing results for 
Search instead for 
Did you mean: 

Custom SQL queries Date format

kevin_nathan
Not applicable

Hi All,

I wanted take a snapshot of the  active/queued jobs at say 8 AM in the morning and want them to be exported to some location.

I have a sample query that pulls out the active jobs

SELECT * FROM domain_job WHERE state = 1

Also the query for queued jobs is 

SELECT * FROM domain_job WHERE state = 0

But when the query runs the columns that contain the start time ,end time info are in the format

135892025510000000 (basically eighteen digit numbers with the last seven digits as 0 )

To my knowledge the ctime number is usually 10 digits,so when I take the first 10 digits for conversion (1358920255),I get the output as

 Wed, 23 Jan 2013 05:50:55 GMT  which im sure is not the output .

 

How to understand the format or change the format in the sql script or at excel ?? 

2 REPLIES 2

Dyneshia
Level 6
Employee

Give this a try :

 

https://www-secure.symantec.com/connect/forums/sql-query-custom-report-parameters-backupsize-job-count-clients-count-etc-0

 

https://www-secure.symantec.com/connect/forums/sql-query-custom-reports-workspace-streaming

 

https://www-secure.symantec.com/connect/forums/looking-custom-opscenter-analytics-sql-script

mikent
Level 3
@Kevin, You need to use the following function in your query to format UTC time: UTCBigIntToNomTime() It will format it in the following format: Jun 3, 2013 10:00:00 PM