Hitachi Vantara Pentaho Community Forums
Page 4 of 4 FirstFirst ... 234
Results 31 to 40 of 40

Thread: Convert Zulu to a date time value

  1. #31
    Join Date
    Apr 2017
    Posts
    26

    Default

    We have tried your format
    Attached Images Attached Images

  2. #32
    Join Date
    Apr 2008
    Posts
    4,374

    Default

    You are showing the date format in a select values step, but it's LDAP Input that's complaining.

    Instead of telling LDAP Input that it's a date, tell it that the value is a string, or put the date format on the LDAP Input step.

    You should be able to use the 'File -> Export -> To XML' to generate a KTR file that you can post here so we can provide input on how to improve it.
    Last edited by gutlez; 07-26-2017 at 01:21 PM.

  3. #33
    Join Date
    Apr 2017
    Posts
    26

    Default

    That screen shot is from the LDAP input step.
    Attached Files Attached Files

  4. #34
    Join Date
    Jun 2012
    Posts
    5,273

    Default

    There seems to be a bug in the LDAP-Input step, preventing a specified format to overwrite the default format.
    For LDAP attribute types with syntax "Generalized Time" do this: Fetch as = String, Type=String.
    Then use Select-Values to convert from String to Date - it's done on the Meta-Data tab, Format = yyyyMMddHHmmssX

  5. #35
    Join Date
    Apr 2017
    Posts
    26

    Default

    Using your suggestion "Then use Select-Values to convert from String to Date - it's done on the Meta-Data tab, Format = yyyyMMddHHmmssX" converts "20170713154939Z" to this "20170713114939-04" which Remedy cannot understand.

  6. #36
    Join Date
    Apr 2008
    Posts
    4,374

    Default

    Quote Originally Posted by Charles Roth View Post
    "20170713114939-04" which Remedy cannot understand.
    What is Remedy actually expecting?

    If you send your data through another Select Values step (now that you've converted the string to a Date), you can change the format of the Date column (while keeping it as a date) just to prove to yourself that Remedy will be able to read it... Just change the format in this second Select Values step to something like MM/dd/yyyy HH:mm:ss
    But note: it will send it as local time to the server that PDI is running on!

    Quote Originally Posted by Charles Roth View Post
    converts "20170713154939Z" to this "20170713114939-04"
    Those are actually the same time:
    20170713154939Z . . . 2017-07-13 15:49:39 UTC
    20170713114939-04. . 2017-07-13 11:49:39 EDT
    Since SQL doesn't actually read the date as a specific formatted string, it should simply work when sending it to a fully functioning SQL interface.


  7. #37
    Join Date
    Jun 2012
    Posts
    5,273

    Default

    According to the title of your thread you wanted to have a date time value - now you have one.

  8. #38
    Join Date
    Apr 2017
    Posts
    26

    Default

    We are getting closer - this 20170713154939Z now converts to this 07/13/2017 11:49:39 - which Remedy can understand. But I believe the real date/time should be 07/13/2017 3:49:39 (pm). It converted the "15" hour to 11 (am or pm?) instead of 3 pm. The database server and the job are all run in the same time zone(EST). Also is there are way to get "AM" or "PM" factored in to the conversion?

    Thanks for all your help.

  9. #39
    Join Date
    Apr 2008
    Posts
    4,374

    Default

    A timezone ending in Z means that it's in UTC.
    EDT (I'm pretty sure you're in daylight saving time, not standard time) is UTC-4 (4 hours decreased from UTC), so 07/13/2017 11:49:39 EDT and 07/13/2017 15:49:39 UTC **ARE THE SAME TIME**
    https://www.timeanddate.com/worldclo...&sec=39&p1=179

    If you go to the link that I sent you at the beginning of this thread
    https://docs.oracle.com/javase/7/doc...ateFormat.html

    You will find how to program your second "Select Values" step to have the AM/PM, but it shouldn't be necessary... If the AR Plugins are handling SQL dates correctly, then it will take care of the AM and PM.



    Last edited by gutlez; 08-09-2017 at 11:55 AM.

  10. #40
    Join Date
    Apr 2017
    Posts
    26

    Default

    First I want to thank you for all your help. I did get it to work perfectly - if I set the format to yyyyMMddHHmmss (no X) the value returns exactly the way we want and Remedy can understand.

    Again many thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.