Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: convert string to datetime

  1. #11

    Default

    Found it!

    if firstExec then {
    Threshold_DT = date("2017-07-01","CCYY-MM-DD")
    }

    Match_Before = dateSubtract(START_DATE, Threshold_DT) < 0

    emit * where Match_Before

  2. #12
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    200

    Default

    I'm glad you found a solution.

    You can convert NULL string values or empty/whitespace strings to a NULL date type using the equivalent of this in a Filter node:

    Code:
    if isNull(DateStr) or trim(DateStr) == "" then {
    	START_DATE = date(null)
    } else {
    	START_DATE = date(DateStr,"CCYY-MM-DD")
    }
    emit START_DATE
    Note that a NULL value is effectively treated as the lowest possible value so when looking for dates before a threshold the NULL values would also be included in the output.
    If you wanted to exclude the NULL records you could modify your date filter to have the following code:

    Code:
    if firstExec then {
     Threshold_DT = date("2017-07-01","CCYY-MM-DD")
     }
    
     Match_Before = dateSubtract(START_DATE, Threshold_DT) < 0
    
    Match_NotNull = isNotNull(START_DATE)
    
     emit * where Match_Before and Match_NotNull

  3. #13

    Default

    Very useful, thanks a lot!

  4. #14

    Default

    Another problem!

    How can I keep the date only from string with format: 7/6/2017 11:21:50 and convert it to: "CCYY-MM-DD" ?

  5. #15

    Default

    the day could be with 2 digits as well: 17/6/2017 11:21:50. I need to cover both cases and make the conversion...

  6. #16

    Default

    Somehow this worked and autoconverted the date:

    a=timestamp(date('Date of call'.left(9),"D/M/CCYY"),time('Date of call'.right(8),"HH:MM:SS"))
    emit *,a

  7. #17
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    200

    Default

    If the date element in the datetime string can have a one-digit or two-digit day component (and obviously the month component will also have both one and two digits) the most reliable way of separating the date element from the time element will be to split the string using the space character as the delimiter. As you have found out, the date operator's format string "D/M/CCYY" allows the day and month components to have one or two digits. You can use the following code to convert the string to a datetime:

    Code:
    #Split datetime string on space character
    Elements = 'Date of call'.split(" ")
    
    a= timestamp(date(Elements[0],"D/M/CCYY"), time(Elements[1],"HH:MM:SS"))
    
    emit *, a

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •