# Change format of t-stamp in sql query

**URL:** <https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [December 12, 2024, 3:28pm UTC](https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752 "2024-12-12T15:28:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Phil.s.Smith](https://avatars.discourse-cdn.com/v4/letter/p/97f17d/32.png) [@Phil.s.Smith](https://forum.inductiveautomation.com/u/Phil.s.Smith)\
**Post date:** [December 12, 2024, 3:28pm UTC](https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752/1 "2024-12-12T15:28:29Z")

</div>

Hello. In a Vision application (Ignition 8.1.25) I have a bar chart component that displays the result of a sql (mssql) query, which returns the top 8 rows. The x-axis ticks are labelled with the t-stamp under each bar. I would like to only display the hour and minute (HH:mm) of the t-stamp, and not the entire date/time, but I'm having trouble figuring out how the change the query or otherwise format the time. Any assistance or guidance would be appreciated. The current query is:

```python
SELECT TOP (8) t_stamp, CaseCount_L5Pack_Hourly FROM HourlyCaseCount 
ORDER BY t_stamp desc

```

Which returns:

2024-12-12 09:00:00.000,196  
2024-12-12 08:00:00.007,166  
2024-12-12 07:00:00.000,163  
2024-12-12 06:00:00.000,159  
2024-12-12 05:00:00.000,153  
2024-12-12 04:00:00.003,172  
2024-12-12 03:00:00.000,175  
2024-12-12 02:00:00.000,167

I tried adding a convert command, but while that changed the format, it also changed the data.

```python
SELECT TOP (8) convert(varchar, t_stamp, 8) t_stamp, CaseCount_L5Pack_Hourly FROM HourlyCaseCount 
ORDER BY t_stamp desc 

```

Returns:

23:00:00,137  
23:00:00,128  
23:00:00,138  
23:00:00,158  
23:00:00,41  
23:00:00,0  
23:00:00,0  
23:00:00,0

As seen in the screenshot, the start of the date/time(Thu Dec 12...) is legible in the x-axis, but only the hour actually needs to be seen, as the data is an hourly count. I'd like to make the hour the only x-axis text visible.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/8/a805b8837cef88299ef5d72e68f18efbf8f0c7ca.png)

Thank you,  
--Phil

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 12, 2024, 6:23pm UTC](https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752/2 "2024-12-12T18:23:57Z")

</div>

Googled "mssql format timestamp"...

> **[Format SQL Server Dates with FORMAT Function](https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/)**
>
> SQL Date Format examples using SQL FORMAT Function to format dates, time and numbers in SQL Server.

---

<div class="post-metadata">

**Author:** ![Phil.s.Smith](https://avatars.discourse-cdn.com/v4/letter/p/97f17d/32.png) [@Phil.s.Smith](https://forum.inductiveautomation.com/u/Phil.s.Smith)\
**Post date:** [December 12, 2024, 6:43pm UTC](https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752/3 "2024-12-12T18:43:03Z")

</div>

Thanks. But i don't seem to be using it correctly, or it doesn't work in this case. Much like the convert command I tried in the initial post, adding a format command seems to return different data.

Really I think I need to know how to combine one of these commands (Convert, Format) with the existing query.

For reference, this is what I tried:

```python
SELECT TOP (8) FORMAT(t_stamp, 'hh:mm') t_stamp, CaseCount_L5Pack_Hourly FROM HourlyCaseCount 
ORDER BY t_stamp desc 

```

and this is the result:

12:00,161  
12:00,120  
12:00,0  
12:00,126  
12:00,144  
12:00,139  
12:00,163  
12:00,141

Removing the Format command and leaving the rest of the query returns:

2024-12-12 12:00:00.000,175  
2024-12-12 11:00:00.000,174  
2024-12-12 10:00:00.007,167  
2024-12-12 09:00:00.000,196  
2024-12-12 08:00:00.007,166  
2024-12-12 07:00:00.000,163  
2024-12-12 06:00:00.000,159  
2024-12-12 05:00:00.000,153

It seems like using t\_stamp as an argument in the Format or Convert is a problem, and I don't know how to combine them with the query or to perform a secondary query to modify the result of the primary one.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 12, 2024, 6:50pm UTC](https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752/4 "2024-12-12T18:50:46Z")

</div>

I think you need to alias the column differently

```python
SELECT TOP (8) 
    FORMAT(t_stamp, 'hh:mm') as formatted_time, 
    CaseCount_L5Pack_Hourly  
FROM 
    HourlyCaseCount 
ORDER BY 
    t_stamp desc 

```

---

<div class="post-metadata">

**Author:** ![Phil.s.Smith](https://avatars.discourse-cdn.com/v4/letter/p/97f17d/32.png) [@Phil.s.Smith](https://forum.inductiveautomation.com/u/Phil.s.Smith)\
**Post date:** [December 12, 2024, 7:00pm UTC](https://forum.inductiveautomation.com/t/change-format-of-t-stamp-in-sql-query/99752/5 "2024-12-12T19:00:27Z")

</div>

> [@dkhayes117](#):
>
> ```python
> SELECT TOP (8) 
> FORMAT(t_stamp, 'hh:mm') as formatted_time, 
> CaseCount_L5Pack_Hourly  
> FROM 
> HourlyCaseCount 
> ORDER BY 
> t_stamp desc 
> 
> ```

That looks promising. I'm going to test a little more before i use it. Thanks!