CA Service Management

View Only

Back to Library

Everything you need know about CA Service Desk Date format

Recommend

Oct 27, 2015 02:57 PM

TMACUL

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.