# Returning a datetime string in a case statement

**URL:** <https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [November 8, 2021, 3:30pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835 "2021-11-08T15:30:50Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [November 8, 2021, 3:30pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835/1 "2021-11-08T15:30:50Z")

</div>

Good day all, I am trying to return a string value using a this case statement:

`,(CASE WHEN TSFO_DeliveredTime = '1900-01-01 00:00:00' THEN 'Not Delivered' ELSE TSFO_DeliveredTime END) AS Delivered`

the ELSE part, i want it to return the value of the TSFO\_Delivered when it has real data to display.  
The ‘1900-01-01 00:00:00’ is a default input by the system when there is no data to display.

i get this error currently:  
Conversion failed when converting date and/or time from character string.

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [November 8, 2021, 3:42pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835/2 "2021-11-08T15:42:24Z")

</div>

Try CASTing or CONVERTing the datetime as a string; you will need to do it in both places. You could also perform the logic in Ignition, as a transform if using perspective or with numerous other methods if using vision.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [November 10, 2021, 2:42pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835/3 "2021-11-10T14:42:42Z")

</div>

Can anyone help me figure out how my transform needs to be Expressed?

---

<div class="post-metadata">

**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\
**Post date:** [November 10, 2021, 2:50pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835/4 "2021-11-10T14:50:10Z")

</div>

I just started using ignition, but are you sure your syntax is correct? or are you using the Jython language?

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [November 10, 2021, 3:01pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835/5 "2021-11-10T15:01:25Z")

</div>

I am using a SQL query, here is my whole code so far:

```auto
SELECT TOP 6
-- *
		 TSFO_MachineNumber AS 'Twister'
		,TSFO_DonLocation AS 'Pot_or_Creel'
		,TSFO_DeliveryByDateTime	AS 'ETA'
		,TSFO_BuggyNumber AS 'Buggy'
		,TSFO_DelayReason AS 'Delay'
		,(CASE WHEN TSFO_DeliveredTime = '1900-01-01 00:00:00' THEN 'Not Delivered' ELSE TSFO_DeliveredTime END) AS 'Delivered'
-- ,TSFO_CompletionTime AS 'Quad_Scan'
		,(CASE WHEN TSFO_CompletionTime = '1900-01-01 00:00:00' THEN 'Not Scanned' ELSE 'Scanned' END) AS Quad_Scan
		
FROM 
		SHAW_TwistingOrders
WHERE 
		(TSFO_DeliveryByDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) -- Beginning of the current day.
		AND TSFO_DeliveryByDateTime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)) -- Beginning of the next day.
		AND DATEPART(HOUR, TSFO_DeliveryByDateTime) BETWEEN 0 AND 23 -- Change for the hour range of the day, 0-23 always have results.
		AND TSFO_MachineNumber LIKE 'V042%' 
ORDER BY 
		TSFO_DeliveryByDateTime ASC

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [November 10, 2021, 9:38pm UTC](https://forum.inductiveautomation.com/t/returning-a-datetime-string-in-a-case-statement/52835/6 "2021-11-10T21:38:27Z")

</div>

For reference this code got it working for my istance:

`,(CASE WHEN TSFO_DeliveredTime = '1900-01-01 00:00:00' THEN 'Not Delivered' ELSE CONVERT(VARCHAR(25),TSFO_DeliveredTime,22) END) AS 'Delivered'`