Hi,

I have an integer field which represent the date.  I am trying to use "CAST" function with "FORMAT" option but it is not working.  In other words, it is not showing the date separators.  Here is the SQL clause:


//I tried this
SELECT CAST(InvoiceDate AS VARCHAR(10)  DATE FORMAT 'yyyy"/"MM"/"dd') FROM TB_Invoices


//And this
SELECT CAST(InvoiceDate AS VARCHAR(10)  DATE FORMAT 'yyyy/MM/dd') FROM TB_Invoices


Any help?

Regards,
Abdulaziz Jasser

Better to say..Can "CAST" an integer field and forma it at the same time?

Regards,
Abdulaziz Jasser

Abdulaziz


The second version is correct - you don't need the "

However, an integer is no longer a date in ElevateDB unlike in DBISAM so you'll need to convert the integer to a date first before you try and format it. How you do that depends on what the integer is.


Roy Lambert [Team Elevate]

<<How you do that depends on what the integer is. >>

First, thank you,
Second, the integer field/value is 20121108
Third, I am trying to format this field to be 2012/11/08

However, I cannot find the right SQL clause.  I do understand the syntax of formatting (ONLY) the date fields.  But I am trying to format and before that cast an integer field.  I do not have any problem right now because I manage to that in Delphi code... but as always looking for the best more optimized way to do that.

Regards,
Abdulaziz Jasser

Abdulaziz


>Second, the integer field/value is 20121108
>Third, I am trying to format this field to be 2012/11/08

That's "relatively" simple

SUBSTR(CAST(fld AS VARCHAR(10),1,4)) + '/' + SUBSTR(CAST(fld AS VARCHAR(10),5,2)) + '/' + SUBSTR(CAST(fld AS VARCHAR(10),7,2))


I'd do it that way rather than converting to a date first and then formatting since you'd have to convert the integer into '2012-11-08' before you can convert it into a date.

Roy Lambert [Team Elevate]

Abdulaziz,

as I mentioned earlier: use Roy's code to create a function:

CREATE FUNCTION "IntToDate" (IN "IntValue" INTEGER)
RETURNS DATE
BEGIN

SET Temp = SUBSTR(CAST(IntValue AS VARCHAR(10)),1,4)
+ '-' + SUBSTR(CAST(IntValue AS VARCHAR(10)),5,2)
+ '-' + SUBSTR(CAST(IntValue AS VARCHAR(10)),7,2);

END
VERSION 1.00

And use this function like this:

SELECT IntToDate(InvoiceDate) FROM TB_Invoices

Uli

Sorry, Copy&Paste error. Here the correct script:

SCRIPT
BEGIN
EXECUTE IMMEDIATE 'CREATE FUNCTION "IntToDate" (IN "IntValue" INTEGER)
RETURNS DATE
BEGIN

RETURN CAST( SUBSTR(CAST(IntValue AS VARCHAR(10)),1,4) + ''-'' +
SUBSTR(CAST(IntValue AS VARCHAR(10)),5,2) + ''-'' +
SUBSTR(CAST(IntValue AS VARCHAR(10)),7,2) AS DATE);

END

VERSION 1.00';
END

Roy,
Uli,

Big thanks to you guys.

Regards,
Abdulaziz Jasser