# Pulling datetime from SQL **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [July 19, 2022, 8:20pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091 "2022-07-19T20:20:14Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [July 19, 2022, 8:20pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/1 "2022-07-19T20:20:14Z")
I am grabbing a table from SQL that has a datetime column. How do I get that time converted to a readable Date and Time? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/9/19e7a4eaffdff191435bf394df1ef904a005744c.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 19, 2022, 8:21pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/2 "2022-07-19T20:21:46Z")
So, one of Ignition’s own tag historian tables? Those store `t_stamp` as 64-bit UTC milliseconds. There’s a `system` function to convert that back. ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [July 19, 2022, 8:24pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/3 "2022-07-19T20:24:09Z")
This is just a table filled with the SQL data. ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 19, 2022, 8:53pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/4 "2022-07-19T20:53:36Z")
Not knowing how the database is constructed the below may work. What is the type of that Time Column in the DB ? ```auto SELECT DATE_FORMAT(FROM_UNIXTIME(`Time`), '%Y-%m-%d %H:%i:%s') as "Date Time" FROM `MyDB` ``` Or just ```auto DATE_FORMAT(Time, "%m-%d-%Y %H:%i:%s") AS 'Date', From 'My DB' ``` ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [July 19, 2022, 8:57pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/5 "2022-07-19T20:57:20Z")
datetime. Below is an example of the query and the tables in the SQL server. ```auto SELECT ROW_NUMBER() OVER(ORDER BY Time ASC) AS IndexNum, PartNumber, ItemCode, JobNumber, Time, Quantity, ProductPrice, TotalPrice, UserName FROM CHECKOUT WHERE JobNumber LIKE :JobNumber ORDER BY Time ASC ``` ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/9/d9ca0f3e783c3f1389ecc233c5c48ca2b5bf3c25.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 19, 2022, 9:00pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/6 "2022-07-19T21:00:30Z")
Ah, you need to configure your table’s column formats explicitly. [https://docs.inductiveautomation.com/display/DOC81/Table+Column+Configurations](https://docs.inductiveautomation.com/display/DOC81/Table+Column+Configurations) ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 19, 2022, 9:02pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/7 "2022-07-19T21:02:58Z")
What format to do you want to display the Time in ? Try the below to tidy it up a little ? Or like Phil mentioned do it in the table. ```auto SELECT ROW_NUMBER() OVER(ORDER BY Time ASC) AS IndexNum, PartNumber, ItemCode, JobNumber, DATE_FORMAT(Time, "%m-%d-%Y %H:%i:%s") AS 'Time', Quantity, ProductPrice, TotalPrice, UserName FROM CHECKOUT WHERE JobNumber LIKE :JobNumber ORDER BY Time ASC ``` ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [July 19, 2022, 9:23pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/8 "2022-07-19T21:23:27Z")
I cannot use the DATE\_FORMAT because that is a MySQL only function. ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 19, 2022, 9:50pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/9 "2022-07-19T21:50:54Z")
```auto to_char(Time, 'dd-mm-yyy hh24:mi:ss') AS 'DATE TIME' ``` ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [July 20, 2022, 12:32pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/10 "2022-07-20T12:32:00Z")
I got it figured out. Thank you everyone for your help and input! ---
**Author:** ![Christopher\_Chataika](https://avatars.discourse-cdn.com/v4/letter/c/f17d59/32.png) [@Christopher\_Chataika](https://forum.inductiveautomation.com/u/Christopher_Chataika)\ **Post date:** [June 18, 2024, 11:23pm UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/11 "2024-06-18T23:23:31Z")
Could you please share how you got it working? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 19, 2024, 1:10am UTC](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/12 "2024-06-19T01:10:06Z")
Christopher, you just need to follow the solution in [Post #5](https://forum.inductiveautomation.com/t/pulling-datetime-from-sql/62091/6) marked as Solution. In summary, in the table's PROPS, - set `columns.x.field` to the name of the SQL column containing the datetime value. - set `columns.x.render : date`. - set `columns.x.dateFormat : HH:mm:ss` or whatever dateformat you require.