Everything you need know about CA Service Desk Date format

Document created by TMACUL Champion on Oct 27, 2015
Version 1Show Document
  • View in full screen mode

Date convert / cast date

日付形式と変換日

Como converter as datas do CA SDM?

Formato de la fecha CA Service Desk.

 

What is this number for date on CA Sevice Desk?

It's an Unix format for date.

 

What means this number filled for date on CA SDM?

It's define as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970 using  Julian Calendar.

 

How to do select date on MS SQL?

 

select

select dateadd(ss, open_date ,'19700101')

from call_req

where clause

select count(0)

from call_req

where dateadd(ss, open_date ,'19700101')>'20151001'

DATEADD (Transact-SQL)

 

Ok, we need remember about your own timezone, it's defined on CA SDM tables, depends on SLA, Contact, however in really it's necessary looking for tz table and join with another table, example contacts and for contacts time zone is usp_contact.c_timezone therefore joining everything it's possible find delta.

select tz.gmt_delta

from usp_contact as cnt

left join tz on tz.code = cnt.c_timezone

BUT... IF we just only working on one timezone in CA Service Desk, we can use SQL command to search by timezone (server time).

select datediff(second,getutcdate(),getdate())

 

How to generate UNIX date format using MS SQL command?

select DATEDIFF(ss, '19700101', GETDATE())

 

 

Time Zone Date Variables

Time zone date variables are a special case of business object variables. They provide a means to convert universal dates (UTC) represented as integers to string dates adjusted for the time zone of the user's browser. The variable for representing integer dates is:

$args.attr_name_INT_DATE

Example: $args.open_date_INT_DATE

Implementation Guide Release 12.7 - Web Interface Customization › Server Variables › Business Object Variables

 

 

How to use on CA Business Intelligence?

 

PDM Functions

To assist in working with specialized CA SDM features and data types, the ODBC driver extends SQL to support a number of additional query functions. All driver-supported functions begin with the string "Pdm", and are known as PDM functions as described in the following table:

 

PDM Functions

 

Description

PdmAddDays([date,] count)When used with one argument, adds the number of days in its argument to today's date and returns the result. When used with two arguments, adds the number of days in its second argument to the value of the date column specified in its first argument and returns the result. This function may be used anywhere in the query
PdmAddMonths([date,] count)When used with one argument, adds the number of months in its argument to today's date and returns the result. When used with two arguments, adds the number of months in its second argument to the value of the date column specified in it first argument and returns the result. The single argument form can be used anywhere in the query. The two-argument form can be used only in the selection list
PdmDay([date])When used with no arguments, returns the current day as an integer. When used with one argument, returns the day associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmDownTime( slaName, workshift, startDate, endDate )Calculates the downtime between two dates under the specified SLA and workshift. This function can be used only in the selection list.
PdmMonth([date])When used with no arguments, returns the current month as an integer from 1 to 12. When used with one argument, returns the month associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmMonthName([date])When used with no arguments, returns the localized name of the current month ("January", "February", and so on). When used with one argument, returns the localized name of the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmDay([date])When used with no arguments, returns the current day as an integer. When used with one argument, returns the day associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmSeconds(date)Returns the value of the date column specified in its argument in its raw form as the number of seconds from midnight January 1, 1970. This function can be used only in the selection list. The argument is required.
PdmString(column)Returns the string equivalent of value of the column specified in its argument. This function can be used with UUID, date, or string columns. It can be used only in the selection list.
PdmToday()PdmToday() [timeAdj [, day [, month [, year]]]] )
Evaluates to today's date (in seconds from 1/1/1970), adjusted according to the arguments:

 

timeAdj:
-1 -- adjust time to beginning of day (0:00:00);
+1 -- adjust time to end of day (23:59:59)

 

day:
negative -- adjust date by number of days specified
positive -- set day to absolute value specified (or to last day of month, whichever is less)

 

month:
negative -- adjust date by number of months specified
positive -- set month to absolute value specified (or to December (12), whichever is less)

 

year:
negative -- adjust date by number of years specified
positive -- set year to absolute value specified

 

Adjustments are applied in the order year, month, day. A zero or omitted argument is ignored.
PdmYear([date])When used with no arguments, returns the current year as a four-digit integer. When used with one argument, returns the year associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.

Write CA Business Intelligence Reports - CA Service Management - 14.1 - CA Technologies Documentation

 

 

Unix Time Stamp - Epoch Converter

Unix time - Wikipedia, the free encyclopedia

Understanding Service Desk Time - SDU

TEC489874 - Changing the Date Format for the Service Desk Web Client and Outgoing Notifications

TEC570357 - How do I change the Date Format on a Service Desk Summary/Detail Report?

TEC1681926 - TIMESPANS are based on the Server time zone and not on the logged-in user's time zone

CA SDM Querys úteis – Tiago Macul

 

Was this information helpful? Give your feedback.

1 person found this helpful

Attachments

    Outcomes