Hi,
I'm looking for the formula to get a date in the right format, concatenated with a shift id.
It should be looking like this DD/MM/YY - SHIFTID
Ex: 31/01/19 - 1
I tried so many thinks but cannot achieve it. To_char doesn't work.
I'm currently at 31/01/2019 - 1 so I just need to be able to remove the 20 but I don't find how.
Formula now:
extract(day, [DateField])+'/'+extract(month, [DateField])+'/'+extract(year, [DateField])+' - ' + [q_BCH].[Gevr_shift]
I tried
extract(day, [DateField])+'/'+extract(month, [DateField])+'/'+right(extract(year, [DateField]),2)+' - ' + [q_BCH].[Gevr_shift]
but that doesn't work: I get the error The vendor specific function "right" is not supported
When I only do Right(extract(year, [Gevr_OperationeleTijdstip_LaatstGekend]),2)
it does not work either, same error
Couldn't you just subtract 2000 from the year calculation?
extract(day, [DateField]) || '/' || extract(month, [DateField]) || '/' || extract(year, [DateField]) - 2000 || ' - ' || [q_BCH].[Gevr_shift]
I haven't tried that, but it could be worth a go. Do you need to deal with any dates from before 2000?
You don't say what RDBMS you are using. Right is a SQL Server function. Apparently you are not using SQL Server. Try substr or substring.
Good point,
The report is using DB2 and SQL server in this report.
The fields used for this calcualation are all from sql server
Quote from: BigChris on 04 Mar 2019 10:07:10 AM
Couldn't you just subtract 2000 from the year calculation?
extract(day, [DateField]) || '/' || extract(month, [DateField]) || '/' || extract(year, [DateField]) - 2000 || ' - ' || [q_BCH].[Gevr_shift]
I haven't tried that, but it could be worth a go. Do you need to deal with any dates from before 2000?
Thanks!
as easy as that it is! no data from before 2000 so it works.