Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: convert string to datetime

  1. #1

    Default convert string to datetime

    Hello,

    I'm trying to convert a string field into datetime using the function:

    a=dateTime(date(ExpDate,"CCYY-MM-DD"),time(ExpDate,"HH:MM:SS"))

    but I get an "cannot parse value '2014-10-02 11:49:34' with format 'HH:MM:SS'.

    Can someone assist?

    Thanks, Christos

  2. #2
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    159

    Default

    You have to use timestamp() to get a datetime field. You're almost have it, you just have to put in the time part ExpDate.right(8) so that it will only pick up the HH:MM:SS piece.

    a=timestamp(date(ExpDate,"CCYY-MM-DD"),time(ExpDate.right(8),"HH:MM:SS"))

  3. #3
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    779

    Default

    You're going to need to substring the date portion as well:

    Code:
    a=timestamp(date(ExpDate.left(10),"CCYY-MM-DD"),time(ExpDate.right(8),"HH:MM:SS"))

  4. #4

    Default

    Thanks a lot for the help!

  5. #5

    Default

    Hi again,

    How can I filter records based on datetime type? eg before or after or equal to "2017-11-10 14:55:37" ?

  6. #6
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    226

    Default

    Here is one way you can filter or split the records. Copy the code and paste it into the BRE canvas.

    Code:
    node:Datetime_Strings
    bretype:core::Static Data
    editor:Label=Datetime Strings
    editor:sortkey=5a1d716849192b82
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    X
    2017-10-12 04:25:16
    2017-11-10 14:55:37
    2017-11-21 11:22:00
    EOX
    editor:XY=280,110
    end:Datetime_Strings
    
    node:Convert_String_To_Datetime
    bretype:core::Filter
    editor:Label=Convert String To Datetime
    editor:sortkey=5a1d72974ade228a
    input:@40fd2c74167f1ca2/=Datetime_Strings.40fe6c55598828e5
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX
    X_DT = timestamp(date(X.left(10),"CCYY-MM-DD"),time(X.right(8),"HH:MM:SS"))
    emit X_DT
    
    EOX
    editor:XY=370,110
    end:Convert_String_To_Datetime
    
    node:Split_Datetimes_before_threshold
    bretype:core::Split
    editor:Label=Split Datetimes before threshold
    editor:sortkey=5a1d756d7b413476
    input:@40fd2c74167f1ca2/=Convert_String_To_Datetime.40fd2c7420761db6
    output:@40fd2c7420761db6/=
    output:@456df11556bd6bcf/=
    prop:PredicateExpr=<<EOX
    
    dateSubtract(X_DT, timestamp(date("2017-11-10 14:55:37".left(10),"CCYY-MM-DD"),time("2017-11-10 14:55:37".right(8),"HH:MM:SS"))) < 0
    EOX
    editor:XY=580,240
    end:Split_Datetimes_before_threshold
    
    node:Ouput_Records_Before_Threshold
    bretype:core::Filter
    editor:Label=Ouput Records Before Threshold
    editor:sortkey=5a1d71e21c1904fc
    input:@40fd2c74167f1ca2/=Convert_String_To_Datetime.40fd2c7420761db6
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX
    if firstExec then {
    	#Set the threshold dateTime
    	DTstring = "2017-11-10 14:55:37"
    	Threshold_DT = timestamp(date(DTstring.left(10),"CCYY-MM-DD"),time(DTstring.right(8),"HH:MM:SS"))
    }
    
    # Test the records to see if X_DT is earlier than the threshold
    Match_Before = dateSubtract(X_DT, Threshold_DT) < 0
    
    # Or: Date equality test
    #Match_Same = dateSubtract(X_DT, Threshold_DT) == 0
    
    # Or: Test for dates after the threshold
    #Match_After = dateSubtract(X_DT, Threshold_DT) > 0
    
    emit * where Match_Before
    
    EOX
    editor:XY=590,110
    end:Ouput_Records_Before_Threshold
    Last edited by awilliams1024; 11-28-2017 at 02:51 PM.

  7. #7

    Default

    Thanks for the input!

    I am also trying to parse date by:

    a=START_DATE.date("CCYY-MM-DD")
    emit *
    override emit a as "START_DATE"

    No success. What exactly is wrong? Also, how can I filter records before and after a certain date?

  8. #8
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    226

    Default

    What is the error?

    Please provide a sample of the data in the START_DATE field.

  9. #9

    Default

    error: not enough data for date spec, cannot parse value " with format 'CCYY-MM-DD'

    sample:
    START_DATE
    2017-06-01
    2017-07-05
    2017-07-26
    2015-04-23

    2014-10-16
    2016-10-06

  10. #10

    Default

    Removed null values and the node run successfully!
    How can I filter records before and after a certain date?

Posting Permissions

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