AnsweredAssumed Answered

Convert Unix Time to Date Format

Question asked by crandall on Jul 30, 2012
Latest reply on Jul 31, 2012 by crandall

Like • Show 0 Likes0
Comment • 9

Good afternoon,

We are working on extracting a date field from a SDM Change Order for use in an ITPAM process. I can get the date extracted through the Select Operator. However, I can only get the date extracted in Unix time and not in the MM/DD/YYYY format that I am looking for. Has anyone run into this before and how did you get around it?

Thank you,

Clinton

Tom_Kouhsari

Jul 31, 2012 5:33 PM

Correct Answer

crandall wrote:
Good afternoon,

We are working on extracting a date field from a SDM Change Order for use in an ITPAM process. I can get the date extracted through the Select Operator. However, I can only get the date extracted in Unix time and not in the MM/DD/YYYY format that I am looking for. Has anyone run into this before and how did you get around it?

Thank you,

Clinton

Hey Clinton,

Say that the date/time field from SDM is in a variable called ticketDateTime. This will convert it to the format you need:

dt = new Date(ticketDateTime * 1000)
formattedDateTime = formatDate(dt, "MM/dd/yyyy")

OR if you are not going to make use of the dt variable in any other place, you can skip creating that and combine it to just one line:

formattedDateTime = formatDate(new Date(ticketDateTime * 1000), "MM/dd/yyyy")

Note that you have to multiple by 1000 because JavaScript uses Epoch time (milliseconds since midnight on January 1, 1970) while SDM stores the number of seconds (not milliseconds) since midnight on January 1, 1970.

Thanks,
Tom

See the reply in context

No one else had this question

Outcomes

Scott_Owens

Jul 30, 2012 7:39 PM

I believe this can be accomplished with the formatdate function.

Process.DT = now();
Process.strDT=formatDate(Process.DT,'yyyy.MM.dd.HH.mm');

I believe the now() function does return a similarly formated datetime value to what you get from ServiceDesk. I hope this helps.

Actions

crandall @ Scott_Owens on Jul 30, 2012 7:59 PM

Scott_Owens wrote:
I believe this can be accomplished with the formatdate function.

Process.DT = now();
Process.strDT=formatDate(Process.DT,'yyyy.MM.dd.HH.mm');

I believe the now() function does return a similarly formated datetime value to what you get from ServiceDesk. I hope this helps.

Hey Scott,

Thanks for the quick reply. That will definitely give me the current date in the correct format. However, the customer is choosing a date (need by date) within the ticket to be used in the ITPAM process. That is the date that I need to convert.

Thanks,

Clinton

1 person found this helpful

Actions

JLBASTOS Jul 30, 2012 8:24 PM

If the database in use is MySQL you can use the UNIX_TIMESTAMP() function to convert from timestamp number to data format.
If you need only the date part you also need the DATE() function to extract only this part.
And if you need the date as a string you can use CAST() or CONVERT() functions.

See (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) to more help.

Actions

crandall @ JLBASTOS on Jul 30, 2012 8:23 PM

jlbastos wrote:
If the database in use is MySQL you can use the UNIX_TIMESTAMP() function to convert from timestamp number to data format.
If you need only the date part you also need the DATE() function to extract only this part.
And if you need the date as a string you can use CAST() or CONVERT() functions.

See (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) to more help.

It is Oracle

Actions

JLBASTOS @ crandall on Jul 31, 2012 3:07 PM

In ORCALE you can use the functions TO_CHAR() or TO_TIMESTAMP() to convert between data and char formats.
(see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i88891)

Actions

crandall @ JLBASTOS on Jul 31, 2012 1:58 PM

Thanks! I'll try that out.

Actions

crandall @ JLBASTOS on Jul 31, 2012 3:29 PM

jlbastos wrote:
In ORCALE you can use the functions TO_CHAR() or TO_TIMESTAMP() to convert between data and char formats.
(see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i88891)

Would you have to do this within the Select Operator under the JDBC module? Or can you do this within the Basic Select Operator under the SDM Module?

1 person found this helpful

Actions

Tom_Kouhsari

Jul 31, 2012 5:33 PM

crandall wrote:
Good afternoon,

We are working on extracting a date field from a SDM Change Order for use in an ITPAM process. I can get the date extracted through the Select Operator. However, I can only get the date extracted in Unix time and not in the MM/DD/YYYY format that I am looking for. Has anyone run into this before and how did you get around it?

Thank you,

Clinton

Actions

crandall @ Tom_Kouhsari on Jul 31, 2012 7:51 PM

Thank you, that did it!

Actions

9 Replies

Scott_Owens Jul 30, 2012 7:39 PM
Mark Correct
Correct Answer
I believe this can be accomplished with the formatdate function.

Process.DT = now();
Process.strDT=formatDate(Process.DT,'yyyy.MM.dd.HH.mm');

I believe the now() function does return a similarly formated datetime value to what you get from ServiceDesk. I hope this helps.
Like • Show 1 Like1
Actions
- crandall @ Scott_Owens on Jul 30, 2012 7:59 PM
  Mark Correct
  Correct Answer
  Scott_Owens wrote:
  I believe this can be accomplished with the formatdate function.
  
  Process.DT = now();
  Process.strDT=formatDate(Process.DT,'yyyy.MM.dd.HH.mm');
  
  I believe the now() function does return a similarly formated datetime value to what you get from ServiceDesk. I hope this helps.
  Hey Scott,
  
  Thanks for the quick reply. That will definitely give me the current date in the correct format. However, the customer is choosing a date (need by date) within the ticket to be used in the ITPAM process. That is the date that I need to convert.
  
  Thanks,
  
  Clinton
  1 person found this helpful
  Like • Show 0 Likes0
  Actions
JLBASTOS Jul 30, 2012 8:24 PM
Mark Correct
Correct Answer
If the database in use is MySQL you can use the UNIX_TIMESTAMP() function to convert from timestamp number to data format.
If you need only the date part you also need the DATE() function to extract only this part.
And if you need the date as a string you can use CAST() or CONVERT() functions.

See (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) to more help.
Like • Show 1 Like1
Actions
- crandall @ JLBASTOS on Jul 30, 2012 8:23 PM
  Mark Correct
  Correct Answer
  jlbastos wrote:
  If the database in use is MySQL you can use the UNIX_TIMESTAMP() function to convert from timestamp number to data format.
  If you need only the date part you also need the DATE() function to extract only this part.
  And if you need the date as a string you can use CAST() or CONVERT() functions.
  
  See (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) to more help.
  It is Oracle
  Like • Show 0 Likes0
  Actions
  - JLBASTOS @ crandall on Jul 31, 2012 3:07 PM
    Mark Correct
    Correct Answer
    In ORCALE you can use the functions TO_CHAR() or TO_TIMESTAMP() to convert between data and char formats.
    (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i88891)
    Like • Show 1 Like1
    Actions
    - crandall @ JLBASTOS on Jul 31, 2012 1:58 PM
      Mark Correct
      Correct Answer
      Thanks! I'll try that out.
      Like • Show 0 Likes0
      Actions
    - crandall @ JLBASTOS on Jul 31, 2012 3:29 PM
      Mark Correct
      Correct Answer
      jlbastos wrote:
      In ORCALE you can use the functions TO_CHAR() or TO_TIMESTAMP() to convert between data and char formats.
      (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i88891)
      Would you have to do this within the Select Operator under the JDBC module? Or can you do this within the Basic Select Operator under the SDM Module?
      1 person found this helpful
      Like • Show 0 Likes0
      Actions
Tom_Kouhsari Jul 31, 2012 5:33 PM
Unmark Correct
Correct Answer
crandall wrote:
Good afternoon,

We are working on extracting a date field from a SDM Change Order for use in an ITPAM process. I can get the date extracted through the Select Operator. However, I can only get the date extracted in Unix time and not in the MM/DD/YYYY format that I am looking for. Has anyone run into this before and how did you get around it?

Thank you,

Clinton
Hey Clinton,

Say that the date/time field from SDM is in a variable called ticketDateTime. This will convert it to the format you need:

dt = new Date(ticketDateTime * 1000)
formattedDateTime = formatDate(dt, "MM/dd/yyyy")

OR if you are not going to make use of the dt variable in any other place, you can skip creating that and combine it to just one line:

formattedDateTime = formatDate(new Date(ticketDateTime * 1000), "MM/dd/yyyy")

Note that you have to multiple by 1000 because JavaScript uses Epoch time (milliseconds since midnight on January 1, 1970) while SDM stores the number of seconds (not milliseconds) since midnight on January 1, 1970.

Thanks,
Tom
Like • Show 4 Likes4
Actions
- crandall @ Tom_Kouhsari on Jul 31, 2012 7:51 PM
  Mark Correct
  Correct Answer
  Thank you, that did it!
  Like • Show 0 Likes0
  Actions

Convert Unix Time to Date Format

Outcomes

Related Content

Recommended Content