locked
Data Flow - Derived Column: Convert string to datetime2, datetimeoffset and time data types RRS feed

  • Question

  • I am trying to cast string to datetime2, datetimeoffset and time data types but unable to figure out how to do it as toTimestamp is only allowing upto milliseconds as per this https://docs.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions#totimestamp

    For example to convert a string with datetimeoffset data to smalldatetime i was able to write this code "toTimestamp('2018-11-27 10:57:51.2000000 +00:00','yyyy-MM-dd HH:mm')" => output was "2018-11-27 10:57:00" as expected

    I am trying to figure out same for datetime2, datetimeoffset and time


    Raja

    Tuesday, April 14, 2020 7:22 PM

All replies

  • Hi Raja,

    The right function to do so is toDate .

    toDate will get date in the format you specify, which can be stored in datetime2, datetimeoffset, datetime.
    Thursday, April 16, 2020 6:15 AM
  • Hi Chirag,

    could you please be more specific? toDate only allows date parts not time, how am i supposed to use it to get time?

    below expression to get time is giving me error

    toDate('10:57:51.3530000','HH:mm:ss.nnnnnnn')

    Error:

    DF-SYS-01 at : Cannot evaluate expression: to_date(10:57:51.3530000, Some(HH:mm:ss.fffffff))


    Raja


    Monday, April 20, 2020 2:23 PM
  • Hi Raja,

    The function expects the datetime format in Java SimpleDateFormat. Kindly have a look at the following docs for patterns and formats :

    https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

    http://tutorials.jenkov.com/java-internationalization/simpledateformat.html

    Here are a few Java SimpleDateFormat date pattern examples:

    Pattern Example
    dd-MM-yy 31-01-12
    dd-MM-yyyy 31-01-2012
    MM-dd-yyyy 01-31-2012
    yyyy-MM-dd 2012-01-31
    yyyy-MM-dd HH:mm:ss 2012-01-31 23:59:59
    yyyy-MM-dd HH:mm:ss.SSS 2012-01-31 23:59:59.999
    yyyy-MM-dd HH:mm:ss.SSSZ 2012-01-31 23:59:59.999+0100
    EEEEE MMMMM yyyy HH:mm:ss.SSSZ Saturday November 2012 10:45:42.720+0100

    Hope this helps.

    Tuesday, April 21, 2020 1:27 PM

  • Hi Chirag,

    The difference between datetime and datetime2 is, datetime2 goes upto nano seconds. As far as i see SimpleDateFormat goes only till milli seconds but i am looking for way to convert string with datetime with precision of nano seconds.



    Raja

    Tuesday, April 21, 2020 2:16 PM