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:
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.