# MSSQL datevalue = null **URL:** \ **Category:** Ignition\ **Created:** [March 10, 2025, 11:13am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442 "2025-03-10T11:13:27Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![milton.stathakis](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/milton.stathakis/32/73561_2.png) [@milton.stathakis](https://forum.inductiveautomation.com/u/milton.stathakis)\ **Post date:** [March 10, 2025, 11:13am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/1 "2025-03-10T11:13:27Z")
Hi everyone, I am trying to create an sql query that records the batchStart and batchEnd datevalue in the MSSQL database (triggered by the changing of the tag's intvalue), and use this period of time to calculate things like average process value, min, max etc. This is for a production report. In the database, the datevalue column is returning only null values. I tried to use the t\_stamp column, but upon further reading I've realised that this is the rowversion and not a replacement for the date/time. Is there a simple way to enable datevalues to be recorded in the database? Cheers ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [March 10, 2025, 11:23am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/2 "2025-03-10T11:23:42Z")
> [@milton.stathakis](#): > > reading I've realised that this is the rowversion and not a replacement for the date/time Is this an entry by the Tag Historian? I would be inteterested to know what value of `t_stamp` you're seeing that makes you think it is a row version and not a timestamp. Also, can you point to where you read this? Or do you mean that you did some reading of internal documentation? For what it's worth the t\_stamp column as inserted by the Tag Historian is a timestamp, in milliseconds from Unix Epoch (Jan 1, 1970). It can be converted to a more Human Readable date using the `fromMillis()` expression function or the `system.date.fromMillis()` scripting function. ---
**Author:** ![milton.stathakis](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/milton.stathakis/32/73561_2.png) [@milton.stathakis](https://forum.inductiveautomation.com/u/milton.stathakis)\ **Post date:** [March 10, 2025, 11:40am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/3 "2025-03-10T11:40:18Z")
Thanks Irose, I read it under one of the topics on this forum, but I may have misinterpreted. This is entered by the tag historian into the MS SQL database. The t\_stamp value looks like this: 1741086679216. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [March 10, 2025, 11:44am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/4 "2025-03-10T11:44:36Z")
That's it in Unix timestamp. Add this: “SELECT DATEADD(s,t\_stamp/1000,‘1970-01-01 00:00:00’)” ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [March 10, 2025, 11:44am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/5 "2025-03-10T11:44:57Z")
> [@milton.stathakis](#): > > 1741086679216 ```python print system.date.fromMillis(1741086679216) ``` Output: ```python >>> Tue Mar 04 06:11:19 EST 2025 >>> ``` The t\_stamp column is most definitely a timestamp. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [March 10, 2025, 11:45am UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/6 "2025-03-10T11:45:53Z")
This is the tag historian, so I would hope they are not directly querying the database. ---
**Author:** ![AUSDIV](https://avatars.discourse-cdn.com/v4/letter/a/e47c2d/32.png) [@AUSDIV](https://forum.inductiveautomation.com/u/AUSDIV)\ **Post date:** [March 10, 2025, 12:34pm UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/7 "2025-03-10T12:34:33Z")
Try something like this. Once you have a DateandTime column in the format you are expecting, you can Query between specified datetimes, or backwards an hour, 24 hours, etc, pretty simply. ```python SELECT TOP (100000000) [Dateandtime],[tagid] ,[intvalue] ,[floatvalue] ,[stringvalue] ,[datevalue] ,[dataintegrity] FROM ( SELECT CAST(DATEADD(SECOND, (t_stamp/1000) - 18000 ,'1970/1/1') AS DateTime) DateandTime, tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity from Ignition_Server.dbo.sqlth_1_data ) as src ``` ---
**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:** [March 10, 2025, 12:37pm UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/8 "2025-03-10T12:37:23Z")
> [@lrose](#): > > hope they are not directly querying the database. Concur. Use the `system.tag.*` functions to retrieve from the historian. ---
**Author:** ![Chris\_Bingham](https://avatars.discourse-cdn.com/v4/letter/c/e5b9ba/32.png) [@Chris\_Bingham](https://forum.inductiveautomation.com/u/Chris_Bingham)\ **Post date:** [March 10, 2025, 1:18pm UTC](https://forum.inductiveautomation.com/t/mssql-datevalue-null/103442/9 "2025-03-10T13:18:05Z")
> [@milton.stathakis](#): > > I am trying to create an sql query that records the batchStart and batchEnd datevalue in the MSSQL database (triggered by the changing of the tag's intvalue), and use this period of time to calculate things like average process value, min, max etc For each of these, +1 for using native system functions. The Ignition functions will do the more complicated series of queries required to cross boundaries of data table partitions, aggregate values (averages, min, max, etc). System functions can also export to CSV and send an e-mail, if that is the method of reporting you prefer. Should you decide that a manual query is the right option for you, here is a link to [Ignition Database Table Reference](https://www.docs.inductiveautomation.com/docs/8.1/appendix/reference-pages/ignition-database-table-reference). Under the ' sqlt\_data\_X\_X' section, you'll see that the 'datevalue' column has a note: > Holds the value of the tag if it is data type 3, NULL otherwise. And, as others have identified, 't\_stamp' with the note: > Unix Timestamp (milliseconds since epoch) for this value. Heed the caution at the top of the page. Do not attempt to alter data within the tables.