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 formatting

    Employee
    Posted 10-20-2016 09:25

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

    Originally posted by: mjmcd

    I am facing an issue at the moment that is holding me back.

    I'm bringing in some DB values that are datetimeformat. When i use TOAD they appear as DD/MM/YYYY HH:MM:SS but when they are pulled into lavastorm they flip to CCYY/MM/DD HH:MM:SS.

    I've tried converting the date to a string and using date.() but I can't get it to work.

    I tried using the datetime function but it said it would prefer a date.

    I have not been able to successfully flip the format around. Is there a server environment setting that I need to change to get it to display all date times filds in the same format?


  • 2.  RE: Date formatting

    Employee
    Posted 10-20-2016 09:53

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

    Originally posted by: stonysmith

    Note: I split your questions into two threads.

    Lavastorm stores dates internally in a binary form that has no format to it.
    There is no need to alter that internal representation.
    What you want to address is how the data is sent to an output.

    Are you outputting to Excel, a database, text files?

    Before you send the data into an output node, the simple thing to do is change the column from a date to a string, and then output the string.

    here is one way:
    myDateStr=format("%02d/%02d/%04d" %02d:%02d:%02d",
myDate.day(),myDate.month(),myDate.year(),
mydate.hours(),myDate.minutes(),mydate.seconds())
override emit myDateStr as myDate


  • 3.  RE: Date formatting

    Employee
    Posted 10-20-2016 09:55

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

    Originally posted by: stonysmith

    Also, if you're talking about the format displayed by the BRDviewer - there's no option (other than changing to strings) for changing the format it uses.


  • 4.  RE: Date formatting

    Employee
    Posted 10-25-2016 09:03

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

    Originally posted by: mjmcd

    Originally posted by: stonysmith
					

    Also, if you're talking about the format displayed by the BRDviewer - there's no option (other than changing to strings) for changing the format it uses.
    Thanks i didn't notice the two threads at first.

    My scenario is this.

    Using a JDBC query node, i connect to our DB and run an sql script. In the script i purposefully added to_date(to_char( etc ) to convert my data into dd/mm/yyyy hh:mm:ss

    This works however as soon as i view the data in brd it is displayed as yyyy-mm-dd hh:mm:ss.

    Eventually i need to export the data into excel node in the UK style data format.

    I've tried various methodes of this, - formatting a template in advance. using lava formatting etc but the best solution I have is excel displaying the dates as a date field in yyyy-mm-dd hh:mm:ss format.

    I've checked the server settings and the time and region are all set on English uk time.

    How can i get the brd to stop flipping my date formats around ?

    Thanks.


  • 5.  RE: Date formatting

    Employee
    Posted 10-25-2016 09:07

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

    Originally posted by: AdamParker

    Hi,

    As Stony said, the BRD Viewer is not actually changing your date format. It is just showing it to you as a date type.

    When it is time for you to output the data to Excel, you can convert your date type to a string with the appropriate format as the example code above.

    Edit: There was a small syntax error in the code above (an extra double-quote)...

    Put your actual field name where you see MyDateField (both places).

    _myDate = 'MyDateField'

_myDateStr=format("%02d/%02d/%04d %02d:%02d:%02d",
_myDate.day(),_myDate.month(),_myDate.year(),
_myDate.hours(),_myDate.minutes(),_myDate.seconds())

override emit _myDateStr as "MyDateField"


  • 6.  RE: Date formatting

    Employee
    Posted 10-25-2016 09:19

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

    Originally posted by: stonysmith

    Originally posted by: mjmcd
					

    to_date(to_char( myDate ))
    If the myDate column is already defined in the database as a Date data type, then this bit of SQL is just adding run time to the query - it's not changing the actual format.

    One option available to you is to convert the dates to strings
    to_char( myDate )
    But that keeps you from performing date mathematics on the values, and will also cause a strange sort order.

    If you keep them as dates, you are not limited to US format in Excel.. you can select your local formatting. One way of dealing with this is to use an input Template Excel file that uses the format you desire.


  • 7.  RE: Date formatting

    Employee
    Posted 10-25-2016 09:54

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

    Originally posted by: mjmcd

    Originally posted by: stonysmith
					

    If the myDate column is already defined in the database as a Date data type, then this bit of SQL is just adding run time to the query - it's not changing the actual format.

    
One option available to you is to convert the dates to strings
    

    

    to_char( myDate )
    

    But that keeps you from performing date mathematics on the values, and will also cause a strange sort order.

    
If you keep them as dates, you are not limited to US format in Excel.. you can select your local formatting.  One way of dealing with this is to use an input Template Excel file that uses the format you desire.
    The template approach is what i am trying now.

    I can't get it to work though, what format would you suggest i set the template file date fields to use in excel?

    I have tried custom dd/mm/yyyy hh:mm:ss and it turns it into custom dd-mm-yyyy hh:mm:ss.

    I tried *dd/mm/yyyy and it displayed this :42647.45631

    Now tried date type 14/03/2007 from excel format and it came out as the same thing..

    If i afterwards convert the 324324.324324 etc into dd/mm/yyyy hh:mm:ss custom format in excel it works perfectly.

    Can't get the node to automate it though. Current excel out put settings are formatting "from template" and preserve cell styles as true.