Data & Time in Records

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
)

OUTPUT : Date 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.

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.

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

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

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.

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.

I have records and relationship but I need columns from two tables that's why created view and generate excel we need cdt, so how can I use records in that case.

This is so confusing, we constantly switch topics here ...

Which of your many problems do you want to discuss now?