Results 1 to 3 of 3

Thread: Date Reformat

  1. #1
    Contributor
    Join Date
    Dec 2011
    Location
    Nukualofa
    Posts
    6

    Default Date Reformat

    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. #2
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    134

    Default

    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.

    Code:
    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. #3
    Contributor
    Join Date
    Dec 2011
    Location
    Nukualofa
    Posts
    6

    Default

    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,
    Last edited by tfainga; 11-13-2017 at 08:06 PM.

Posting Permissions

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