How to convert the long value to date format in ServiceDesk Plus – MSP?

For MYSQL: 
Use “from_unixtime([columnname]/1000)” in the mysql prompt to get the date in the correct format.
For getting the long value from date, see the following example:
mysql> select unix_timestamp(‘2004-1-31 20:10:10′) * 1000;
unix_timestamp will give you the time in seconds since Jan 1 1970. Multiply by 1000 to get the time in milliseconds.
DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,’%d-%m-%Y %k:%i’)’Created Time’,

Compare date in MYSQL: 
where wo.createdtime >= (UNIX_TIMESTAMP(DATE(‘2009-01-01 00:00:00’)) * 1000) and wo.createdtime <= (UNIX_TIMESTAMP(DATE(‘2009-12-31 23:59:59’)) * 1000)
Also you can use,
where (from_unixtime(wo.createdtime/1000) between ‘2011-09-20 00:00:00’ and ‘2011-09-20 23:59:59’)

For MSSQL: 
As an example, see the query below,
Display the date column in SQL Server
select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),’1970-01-01 00:00:00′) ‘Created Date’, WORKORDERID ‘Request ID’ from WorkOrder
select DATEADD(s,wo.CREATEDTIME/1000,’01-01-1970′)’Created time’ from workorder wo
Compare date column in SQL server: 
select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),’1970-01-01 00:00:00′) ‘Created Time’ from workorder
where dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),’1970-01-01 00:00:00′) >= convert(varchar,’2011-01-01 00:00′,21)
and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),’1970-01-01 00:00:00′) <= convert(varchar,’2011-12-31 23:59′,21)

Need More Help?

Are you looking for a personalized customer support? Submit your question or Call our Toll Free Number to speak with ServiceDesk Plus MSP Expert who can offer a better solution for your requirements.

Submit a Ticket
Email : sdpmsp@manageengine.com
Toll Free : 1-888-720-9500