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?

  • Hi,

    Because in database dateTime is of this format 2023-08-31 23:08:00.000059 and stored procedure is giving output when I'm passing this format.

    But I Appian record the dateTime is of this format 9/10/2023 12:50 PM GMT+05:30. 

    For converting this format 9/10/2023 12:50 PM GMT+05:30 to this 2023-08-31 23:08:00.000059 I'm using datetext() function.

    To stored procedure I want to pass dateTime in 2023-08-31 23:08:00.000059 format from Appian.

    Thank you.

  • 0
    Certified Lead Developer
    in reply to prachit0003

    Would you please read my first reply! When dealing with data types, there is no such thing like a "format". This is just a visual representation of a internal value.

  • So why I'm not getting the data after running the stored procedure

    My Stored Procedure

    SELECT
    TBA_BOOKING.BOOKING_ID,
    TBA_BOOKING.CUSTOMER_EMAIL,
    TBA_BOOKING.TRAVEL_FROM,
    TBA_BOOKING.TRAVEL_TO,
    TBA_BOOKING.LOCATION_FROM,
    TBA_BOOKING.LOCATION_TO,
    TBA_BOOKING.NO_OF_TRAVELERS,
    TBA_BOOKING.PACKAGE_TOTAL_COST,
    TBA_BOOKING.DURATION,
    TBA_BOOKING.FOOD_REQUIRED,
    TBA_BOOKING.FOOD_COST,
    TBA_BOOKING.FINAL_TOTAL_COST,
    TBA_BOOKING.STATUS_OF_TRAVEL_BOOKING_CANCELLATION,
    TBA_BOOKING.STATUS_OF_FOOD_BOOKING_CANCELLATION,
    TBA_BOOKING.CANCELLATION_FEES,
    TBA_BOOKING.CREATED_BY,
    TBA_BOOKING.CREATED_ON,
    TBA_TRAVEL_PACKAGE.PACKAGE_TYPE,
    TBA_TRAVEL_PACKAGE.ISFOOD_AVAILABLE,
    TBA_TRAVEL_PACKAGE.NO_OF_SEATS
    FROM TBA_BOOKING
    INNER JOIN TBA_TRAVEL_PACKAGE
    ON TBA_BOOKING.TRAVEL_ID = TBA_TRAVEL_PACKAGE.TRAVEL_ID
    WHERE TBA_BOOKING.CREATED_ON BETWEEN dateFrom AND dateTo

    dateFrom and dateTo are parameters to stored procedure of dateTime type

  • 0
    Certified Lead Developer
    in reply to prachit0003

    https://dba.stackexchange.com/questions/150158/preferred-way-to-store-datetime

    "A common way of storing date/time data, employed "behind the scenes" by many products, is by converting it into a decimal value where the "date" is the integer portion of the decimal value, and the "time" is the fractional value. So, 1900-01-01 00:00:00 is stored as 0.0 and September 20th, 2016 9:34:00 is stored as 42631.39861. 42631 is the number of days since 1900-01-01. .39861 is the portion of time elapsed since midnight."

    But back to your question. How am I supposed to answer that? I do not see your data, nor how you call that SP from Appian.

    BTW, why do you create a SP for such a trivial query? You could easily do that with Appian records.

  • Initially I was using view but my manager said after 5-6 years inside view the data will be large and fetching data will take time, so what will you use in such case and I preferred stored procedure.

    The process is like : Inside interface I have two date and time field (dateFrom and dateTo) which is passed to process model by using a!startProcess and inside process model execute stored procedure smart service is used which takes dateFrom and dateTo as parameter and gives data between two dates and pass that data to Excel.

  • 0
    Certified Lead Developer
    in reply to prachit0003

    Is that the same use case we discussed in another thread? The Appian Data Fabric feature is specifically made to simply managing and querying data from multiple sources. Create records and the relationships. Then you can easily query the data you need, without a view or a stored procedure.

Reply Children