Cannot convert string to timestamp

General Questions
, ,
1

Originally asked by Taras Botsko on 24 January 2022 (original question)

Hi Team,

Please help adapt the script for the date field to synchronize between Jira and Exalate for SNOW. I`m receiving string value from SNOW and how I can apply it to the timestamp in Jira. Also vise versa accordingly

See date format in SNOW - 01/17/2022 07:38:08

and Jira - 11/Jan/22 5:47 PM

I saw one topic in community but it did not help - Integrate ServiceNow and Jira SD: date/time field issue (old community)

Thank you

2

Answer by Francis Martens (Exalate) on 24 January 2022

Hi Taras,

Stackoverflow to the rescue - check out this article

Also the web based groovy console is very useful if you want to experiment

I pasted there following code (from that stackoverflow document)

import java.text.SimpleDateFormat
import java.sql.Timestamp

String st = "16/08/2007 09:04:34"    
SimpleDateFormat sdf = new SimpleDateFormat("dd/mm/yyyy hh:mm:ss")     
Date date = sdf.parse(st)     
Timestamp timestamp = new Timestamp(date.getTime())     
t2 = timestamp.toString()
​print t2

Which gave

1728Ă—1052 94.1 KB

Comments:

Taras Botsko commented on 26 January 2022

Thanks for the guidance Francis, I have one more clarification.

It works when a date string suit format we try to parse “16/08/2007 09:04:34” to “dd/mm/yyyy hh:mm:ss”

But how it will work in my case I need to parse string “16/08/2007 09:04:34” to “dd/MMM/yy h:mm a”

Maybe I`m missing something(

Thanks

Francis Martens (Exalate) commented on 26 January 2022

Do you want to convert 16/08 to 16/Aug ?

With the code above you can convert the source datestring into a timestamp, and then with the date formatting methods, you can the convert it back into a specific formatted datestring…

Taras Botsko commented on 27 January 2022

yeah, I have difficulties on how to format date from “mm/dd/yyyy hh:mm:ss” to “dd/MMM/yy h:mm a”. I was searching in Stackoverflow but it does work. I also will ask our local Java dev to help on it. But if you have a working code, it will help. I`ll share here if I will find something as well. Thanks

Francis Martens (Exalate) commented on 27 January 2022

Euh - you’re passing the joy of hacking around

Try this

​import java.text.SimpleDateFormat
import java.sql.Timestamp

String st = "16/08/2007 09:04:34"    
SimpleDateFormat sourceSDF = new SimpleDateFormat("dd/mm/yyyy hh:mm:ss")     
Date date = sourceSDF.parse(st)     


SimpleDateFormat targetSDF = new SimpleDateFormat("dd/MMM/yyyy hh:mm:ss")     
println "Results in " + targetSDF.format(date)     


Timestamp timestamp = new Timestamp(date.getTime())     
t2 = timestamp.toString()
println t2
Taras Botsko commented on 28 January 2022

Thank you Francis, that works perfectly!

One thing is when I set String st = replica.start_date where replica.start_date is a String. I`m receiving exalate error - unable to parse “”.

In line

Date date = sourceSDF.parse(st)   
  
I`ll test it further and let here know  
  
Thanks again for a help
Taras Botsko commented on 28 January 2022

Fixed! Thanks so much for your support! I added an additional variable

import java.text.SimpleDateFormat
import java.sql.Timestamp

def startDate = replica.start_date
String st = startDate   
SimpleDateFormat sourceSDF = new SimpleDateFormat("mm/dd/yyyy hh:mm:ss")     
Date date = sourceSDF.parse(st)        
SimpleDateFormat targetSDF = new SimpleDateFormat("dd/MMM/yy h:mm a")
Timestamp timestamp = new Timestamp(date.getTime())

issue.customFields."Start Date".value = timestamp
3

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.