LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only
Back to discussions
Expand all | Collapse all

convert string to datetime

  • 1.  convert string to datetime

    Employee
    Posted 11-03-2017 04:31

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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.  RE: convert string to datetime

    Employee
    Posted 11-03-2017 05:44

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: gmullin

    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.  RE: convert string to datetime

    Employee
    Posted 11-03-2017 07:10

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

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

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


  • 4.  RE: convert string to datetime

    Employee
    Posted 11-03-2017 09:54

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    Thanks a lot for the help!


  • 5.  RE: convert string to datetime

    Employee
    Posted 11-28-2017 07:09

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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.  RE: convert string to datetime

    Employee
    Posted 11-28-2017 07:48

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: awilliams1024

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

    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


  • 7.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 02:17

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 03:15

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: awilliams1024

    What is the error?

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


  • 9.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 03:45

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 03:54

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

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


  • 11.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 04:08

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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


  • 12.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 05:24

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: awilliams1024

    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:

    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:

    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


  • 13.  RE: convert string to datetime

    Employee
    Posted 11-29-2017 06:21

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    Very useful, thanks a lot!


  • 14.  RE: convert string to datetime

    Employee
    Posted 12-01-2017 06:58

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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" ?


  • 15.  RE: convert string to datetime

    Employee
    Posted 12-01-2017 07:02

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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...


  • 16.  RE: convert string to datetime

    Employee
    Posted 12-01-2017 08:45

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: cpapageo

    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


  • 17.  RE: convert string to datetime

    Employee
    Posted 12-04-2017 03:09

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: awilliams1024

    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:

    #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