Add Date to String in Formula

[DesiLogi]

Does anyone know how to add a Date field's value into a text string, in a formula, where the other fields are text? The two different data types won't work together in the same formula and I need to add a date field's value to the end of a string of other fields. Thanks for the help-

[Vitalikssssss]

Hi DesiLogi,

You can convert date/time field to a String with the following expression:

 

LEFT(CONVERT(VARCHAR, [@field:Date], 120), 10)

 

Regards,

vitalikssssss

[DesiLogi]

Ah- that's great. Thanks Vitalikssss!

[hstange]

On 12/21/2017 at 4:57 PM, Vitalikssssss said:

LEFT(CONVERT(VARCHAR, [@field:Date], 120), 10)

@Vitalikssssss this is SO helpful!! Thanks very much! At first I deleted the "left" and "10" because I didn't understand their purpose, but now I get it For anyone wondering, here's an explanation for why it's useful:

The "LEFT" at the start of the formula gives instruction to only keep the left side of whatever is contained in the brackets that follow, and the "10" gives instruction to keep the first 10 characters (to the left, in this case; you can also use "right" in other cases). So you have year (4 characters), plus month (2 characters) plus day (2 characters) plus 2 forward slashes: ex. "2018/03/22", and that's a total of 10 characters. If you didn't limit the date to 10 characters, the time would be included as well.

In case anyone wants the time included in the date-turned-string, just remove the "LEFT("   and the   ",10)"  from Vitalikssssss's answer above. And if you want only the time, use "right" instead of "left" and replace 10 with the number of time characters (don't forget to include ":" as part of your character count).

[ParkLoey]

Hey guys! Just wanted to provide this article for the list of functions:

https://howto.caspio.com/function-reference/

Hope this helps!

[User05012023]

I have also faced a similar issue, but I wanted to get the date into a more usable format, here I am using UK format, but you can move the day and month around - 

Day / Month / Year

RIGHT(LEFT(CONVERT(VARCHAR, [@field:JobDate], 120), 10),2)

+ '/' + RIGHT(LEFT(LEFT(CONVERT(VARCHAR, [@field:JobDate], 120), 10),7),2)

+ '/' +LEFT(LEFT(CONVERT(VARCHAR, [@field:JobDate], 120), 10),4)

 

Hopefully this helps getting the date formatted within a formula field outside a datapage. 

[autonumber]

Hi! Just to update this post, if you want to format your date field by month and year only, you use this formula: 

CONVERT(VARCHAR,(Month([@field:Date_]))) + '-' + CONVERT(VARCHAR,( Year([@field:Date_])))

[Queso]

Hi! Just to add, you can check this Convert function in our Function Reference and in w3schools page:
- https://howto.caspio.com/function-reference/#:~:text=data type specified-,Convert,-(new data type
- https://www.w3schools.com/sql/func_sqlserver_convert.asp

Lastly, you can visit this article for more information about the VARCHAR:
- https://www.sqlshack.com/sql-varchar-data-type-deep-dive/

[ianGPT]

Hello,

Just want to share that you can also use the syntax below to convert the date/time field to a string.

 

CONVERT(VARCHAR(10), [@field:DATE], 101)

Hope this helps!