AnsweredAssumed Answered

Time Format Issue - CSV Feed File

Question asked by Sabthami S on Sep 19, 2017
Latest reply on Sep 21, 2017 by William Cheang

Like • Show 0 Likes0
Comment • 6

Hi All,

We are using a custom date attribute for user to populate their Last Working date[with time] which we are populating using Identity Data Collector from a CSV feed.The issue is the Date is populating as expected in mm/dd/yyyy format but the time still shows as 12 AM despite of the different values from the CSV feed.Please see the below screenshot of the particular attribute and the screenshot from CSV file for more reference.

Please let us know if there are workaround to resolve this issue.

Any help is much appreciated !!!

Regards,

Sabthami Subramanian

No one else has this question

Outcomes

6 Replies

Boris Lekumovich Sep 19, 2017 8:32 PM
Mark Correct
Correct Answer
What SQL query are you using to collect the date value?
Check the raw data to see what value is being collected.
Like • Show 0 Likes0
Actions
- Sabthami S @ Boris Lekumovich on Sep 20, 2017 3:49 AM
  Mark Correct
  Correct Answer
  Hi Boris,
  
  Below is the query i'm using in IDC and TerminationDate is the column which holds the date value with time.
  
  select distinct EmployeeID as ui,CompanyName,EmailAddress,FirstName,LastName,Location, Status,TerminationDate
  from xxx.csv
  
  And in the Raw data also it shows in the same format.Please see the attached screenshot for more reference.
  
  Any help is much appreciated !!!
  
  Regards,
  Sabthami Subramanian
  Like • Show 0 Likes0
  Actions
  - Boris Lekumovich @ Sabthami S on Sep 20, 2017 8:21 AM
    Mark Correct
    Correct Answer
    The dates in the csv appear with a colon (:) between the year and the hour.
    Try using the to_date function in your query.
    Some thing like:
    
    select distinct EmployeeID as ui,CompanyName,EmailAddress,FirstName,LastName,Location, Status, to_date(TerminationDate, 'MM/DD/YY:HH:MI:SS') as NEW_TERMINATION_DATE from xxx.csv
    Like • Show 0 Likes0
    Actions
    - Sabthami S @ Boris Lekumovich on Sep 20, 2017 8:33 AM
      Mark Correct
      Correct Answer
      Hi Boris,
      
      Tried the same but still no luck.Getting the below error as shown in the below screenshot.
      
      Regards,
      Sabthami Subramanian
      Like • Show 0 Likes0
      Actions
      - Boris Lekumovich @ Sabthami S on Sep 20, 2017 8:54 AM
        Mark Correct
        Correct Answer
        As a test, I would try to "fix" the date in the csv file by replacing the colon between the year and hour with space and then collect and see if the raw data shows valid results.
        Like • Show 0 Likes0
        Actions
      - William Cheang @ Sabthami S on Sep 21, 2017 5:13 AM
        Mark Correct
        Correct Answer
        As HXTT CSV JDBC is used to query CSV file, the SQL Query for date should "date" not "to_date"(This is used by Oracle Query)
        
        "DATE(expr): extracts the date part of the date or timestamp expression expr."
        
        Kindly refer this link for the detail sytnax
        http://www.hxtt.com/text/function.html#date
        Like • Show 1 Like1
        Actions

Time Format Issue - CSV Feed File

Outcomes

Related Content

Recommended Content