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

  • 1.  Date Reformat

    Employee
    Posted 11-03-2017 04:16

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

    Originally posted by: tfainga

    Im trying to reformat the date from an excel file sources to be able to match with the date from DB data source.

    Date Format from Excel files:

    9/1/2016 0:05 (mm/dd/yyy hh:mm)


    Date Format on DB server:

    2017-10-04 (ccyy-mm-dd)

    So, I need to reformat the excel files date to have a same date format with DB date format!


  • 2.  RE: Date Reformat

    Employee
    Posted 11-03-2017 05:33

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

    Originally posted by: gmullin

    In your case you might have a date that has 10/1/2017 which would need to be converted using "MM/D/CCYY" and then you could have 9/1/2017 which would use "M/D/CCYY". You will have to use the function isDate() to check the format before doing the conversion. What version of LAE are you on? You might have the accelerators library, it has a node called Date Conversion that will cycle through all the different types and convert for you. Or you can write your own BRAINScript in a filter node. I think this will cover you, if I missed one format just add it to the if statement in the filter.

    node:Dates
bretype:core::Static Data
editor:Label=Dates
editor:sortkey=59fc5f3a236836b4
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
DateField
9/1/2017 00:00:00
10/1/2017 12:00:05
8/30/2016 13:00:00
EOX
editor:XY=180,280
end:Dates

node:Filter
bretype:core::Filter
editor:sortkey=59fc5f6a645d7b9c
input:@40fd2c74167f1ca2/=Dates.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_date = 'DateField'

# check format before trying to convert to date
if _date.isDate("MM/DD/CCYY") then
	_newdate = _date.str().date("MM/DD/CCYY")
else if _date.isDate("M/D/CCYY") then 
	_newdate = _date.date("M/D/CCYY")
else if _date.isDate("MM/D/CCYY") then
	_newdate = _date.date("MM/D/CCYY") 
else if _date.isDate("DD/M/CCYY") then 
	_newdate = _date.date("DD/M/CCYY")
else
	_newdate = date(null)

emit _newdate as "Date_Format", 'DateField' as "Date_Original", _date as "Date_String"

EOX
editor:XY=320,280
end:Filter


  • 3.  RE: Date Reformat

    Employee
    Posted 11-13-2017 13:03

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

    Originally posted by: tfainga

    Thanks, very appreciate your help!

    I'm using LAE v6.1. Is it available on this version or do I need to update my library?


    Cheers,