Data & Time in Records

Hello,

For storing date and time for (dateTime type variable) in record I'm using now() function.

But in record type its format is like : 9/10/2023 12:50 PM GMT+05:30 and in database it is of this format : 2023-09-10 08:06:33.100000.

I want in record type and database should have same format like this : 9/10/2023 12:50 PM GMT+05:30. 

As I'm using stored procedure to get data between two dates, but I'm passing the date format as 9/10/2023 12:50 PM GMT+05:30 and in database it is in this format 2023-09-10 08:06:33.100000, so not getting the data after executing stored procedure.

Thank you.

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer

    This is just a matter of how that value is displayed to you. The DB uses the ISO format, while Appian uses a US format. If you need to display, not transform, a date in a different format, the text() function is your friend. But do this in the UI only.

    And regarding your stored procedure. Either use the ISO format, or one of the date parsing function MariaDB provides.

  • Hello,

    Now I'm doing this in expression rule

    a!localVariables(
    local!endDateTime: datetext(userdatetime(
    year: year(eomonth(now(),-1)),
    month: month(eomonth(now(),-1)),
    day: day(eomonth(now(),-1)),
    hour: 23,
    minute: 59,
    second: 59
    ),"yyyy-MM-dd HH:MM:SS.mmmmmm"),
    local!endDateTime
    )

    OUTPUT"2023-08-31 23:08:00.000059"(Text) , here output is text format and I want to pass dateTime format to stored procedure. But whenever using todatetime() function like

    a!localVariables(
    local!endDateTime: todatetime(datetext(userdatetime(
    year: year(eomonth(now(),-1)),
    month: month(eomonth(now(),-1)),
    day: day(eomonth(now(),-1)),
    hour: 23,
    minute: 59,
    second: 59
    ),"yyyy-MM-dd HH:MM:SS.mmmmmm")),
    local!endDateTime
    )

    OUTPUTDate out of range (-2147483648)(Date and Time with Timezone)

    I want to convert that text ("2023-08-31 23:08:00.000059"(Text)) in datetime, how to do?

    Thank you.

  • 0
    Certified Lead Developer
    in reply to prachit0003

    This is confusing ....

    You have a value of type datetime (userdatetime), then convert it into a string (datetext), just to try to convert it back into a datetime!?!?!?

    Why?

    If your stored procedure accepts a datetime vaue, why convert it into a string. And if it accepts a string, why converting it into a datetime again?

Reply Children