# DATE format GTM in template

**URL:** <https://discourse.nodered.org/t/date-format-gtm-in-template/84084>\
**Category:** Dashboard\
**Created:** [28 December 2023 13:22 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084 "2023-12-28T13:22:09Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![LinkView\_Maze](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@LinkView\_Maze](https://discourse.nodered.org/u/LinkView_Maze)\
**Post date:** [28 December 2023 13:22 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/1 "2023-12-28T13:22:09Z")

</div>

Hello

I have a dashboard with template node

 ![Capture d'écran 2023-12-28 141734](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/f/1f26a6642e50330215e98980c1d0917070e8eaa8.png)

But the date in my database is on format YYYY-MM-DD, after select on Mysql the value is in format  
 ![Capture d'écran 2023-12-28 142009](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/3525638593e42fd6e9ac67e7a45b15c06b32d538.png)

What is the solution for any value il in my template in format YYYY-MM-DD not GTM

---

<div class="post-metadata">

**Author:** ![GogoVega](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gogovega/32/71313_2.png) [@GogoVega](https://discourse.nodered.org/u/GogoVega)\
**Post date:** [28 December 2023 13:30 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/2 "2023-12-28T13:30:42Z")

</div>

Since you are French-speaking, don't you want this kind of format?  
`"20/12/2012 04:00:00"`

Take a look [here](https://developer.mozilla.org/fr/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleString), I don't know if you can use it directly in the template or if you have to modify the message before

---

<div class="post-metadata">

**Author:** ![LinkView\_Maze](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@LinkView\_Maze](https://discourse.nodered.org/u/LinkView_Maze)\
**Post date:** [28 December 2023 13:34 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/3 "2023-12-28T13:34:50Z")

</div>

```auto
    <td><center>{{msg.payload[$index].date.toLocaleString('fr-FR', { timeZone: 'UTC' })}}</center></td>

```

I changed it like that but it doesn't work

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 December 2023 13:35 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/4 "2023-12-28T13:35:10Z")

</div>

> [@LinkView\_Maze](#):
>
> But the date in my database is on format YYYY-MM-DD,

I would think you can adjust your sql statement to get the correct format.

---

<div class="post-metadata">

**Author:** ![LinkView\_Maze](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@LinkView\_Maze](https://discourse.nodered.org/u/LinkView_Maze)\
**Post date:** [28 December 2023 13:37 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/5 "2023-12-28T13:37:27Z")

</div>

this is my request mysql

```auto
SELECT * FROM authorized ORDER BY name ASC, lastname ASC

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 December 2023 13:39 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/6 "2023-12-28T13:39:49Z")

</div>

Would need to know column types, to make a reply that would work. But try naming the columns instead of \*, and also you can use sql functions on the columns then. Dont for get to use `AS` name function to make the returned object properties more readable.

e.g

```auto
SELECT DATE_FORMAT(date_column, "%Y") AS date....

```

[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function\_date-format](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format)

---

<div class="post-metadata">

**Author:** ![GogoVega](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gogovega/32/71313_2.png) [@GogoVega](https://discourse.nodered.org/u/GogoVega)\
**Post date:** [28 December 2023 13:40 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/7 "2023-12-28T13:40:18Z")

</div>

Try with

```auto
{{new Date(msg.payload[$index].date).toLocaleString('fr-FR', { timeZone: 'UTC' })}}

```

---

<div class="post-metadata">

**Author:** ![LinkView\_Maze](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@LinkView\_Maze](https://discourse.nodered.org/u/LinkView_Maze)\
**Post date:** [28 December 2023 13:44 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/8 "2023-12-28T13:44:21Z")

</div>

no i have no entry in my table with this format

---

<div class="post-metadata">

**Author:** ![LinkView\_Maze](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@LinkView\_Maze](https://discourse.nodered.org/u/LinkView_Maze)\
**Post date:** [28 December 2023 13:46 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/9 "2023-12-28T13:46:55Z")

</div>

this is my database

 ![Capture d'écran 2023-12-28 144457](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f156ac4cbe3991c55a47569c6a9588b3f1c0f18e.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [28 December 2023 13:51 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/10 "2023-12-28T13:51:12Z")

</div>

Modify the format in msg.payload before you pass it to the template node.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 December 2023 13:52 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/11 "2023-12-28T13:52:28Z")

</div>

Try

```auto
SELECT 
  ID,
  name,
  lastname,
  plate,
  DATE_FORMAT(date, "%Y-%m-%d") AS date 
FROM 
  authorized 
ORDER BY 
  name ASC, 
  lastname ASC;

```

Or similar

---

<div class="post-metadata">

**Author:** ![LinkView\_Maze](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@LinkView\_Maze](https://discourse.nodered.org/u/LinkView_Maze)\
**Post date:** [28 December 2023 14:07 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/12 "2023-12-28T14:07:36Z")

</div>

this is good but the code is

`msg.topic = "SELECT ID ,name, lastname,plate, DATE_FORMAT(date, '%Y-%m-%d') AS date FROM authorized ORDER BY name ASC, lastname ASC"; return msg;`

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [28 December 2023 18:10 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/13 "2023-12-28T18:10:45Z")

</div>

> [@LinkView\_Maze](#):
>
> in format YYYY-MM-DD not GTM

If you want the dates to show in your **local time** ,  
you'll have to use an additional `CONVERT_TZ` mysql date function  
in order to first convert it to your timezone and then format it ..  
otherwise the dates would _sometimes_ show incorrectly.

so adding to @E1cid 's solution

```auto
SELECT ID, name, lastname, plate, DATE_FORMAT(CONVERT_TZ(date,'+00:00','+10:00'), '%Y-%m-%d') AS date FROM authorized ORDER BY name ASC, lastname ASC

```

- `+10:00` as an example. adjust to your local tz

[**CONVERT\_TZ()** function](https://www.tutorialspoint.com/mysql/mysql_date_time_functions_convert_tz.htm)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [11 January 2024 18:11 UTC](https://discourse.nodered.org/t/date-format-gtm-in-template/84084/14 "2024-01-11T18:11:39Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.