Help with Simple Calculated Field

[mgoetze]

Hi All,
I am a SQL newbie so I was hoping to get some help with setting up a calculated field. My requirement is very basic, I have two date fields and one calculated field. I would like the calculated field to calculate the number of days between the two date fields. For example:

Field 1: 2022-01-01
Field 2: 2022-02-05
Field 3: 35 Days (this is the calculated field)

what would be the SQL query for the above?

[pbottcher]

hi, you can use the search function for searching the forum. A lot of tips can be found here. Have a look at viewtopic.php?t=3789
this should give you a good starting point

[mgoetze]

I did search and I saw that link, but unfortunately my knowledge of SQL is non-existent so I have no idea how to interpret what the other users were doing with SQL. To be honest I am hoping that someone can tell me the correct SQL statement so I can complete my project, I wish I had more time to learn SQL but I dont at the moment. Any specific help would be greatly appreciated.

[zibrahim]

Hello there,
A simple SQL statement should be like this.

Code: Select all

SELECT DATEDIFF(field_2, field_1) FROM TABLENAME 
WHERE id='%ID%'

Replace field_2, field_1, TABLENAME & id according to your table and fields definition accordingly.

Stay safe.

[mgoetze]

This worked perfectly. Thank you.

[hubert]

Hi

Have the same request, result is ok but ...
Database is about WW1.
Works fine if date is after 1970-01-01
Doesn't work with earlier date

SQL in calculated field is this :
SELECT DATEDIFF(date,'1914-08-02') FROM mouvements WHERE id='%ID%'

Seems it' a Unix timestamp trouble and if date is set before 1970, date fiels is set to null and of course no result ...

Any key ?