Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 8 of 8 total |
EDB 2.11 B2 - "CAST" issue. |
Thu, Nov 8 2012 11:47 AM | Permanent Link |
Abdulaziz Al-Jasser | 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 |
Thu, Nov 8 2012 11:51 AM | Permanent Link |
Abdulaziz Al-Jasser | Better to say..Can "CAST" an integer field and forma it at the same time?
Regards, Abdulaziz Jasser |
Thu, Nov 8 2012 12:46 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, Nov 8 2012 1:39 PM | Permanent Link |
Abdulaziz Al-Jasser | <<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 |
Fri, Nov 9 2012 5:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Fri, Nov 9 2012 6:21 AM | Permanent Link |
Uli Becker | 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 |
Fri, Nov 9 2012 8:04 AM | Permanent Link |
Uli Becker | 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 |
Sat, Nov 10 2012 3:59 AM | Permanent Link |
Abdulaziz Al-Jasser | Roy,
Uli, Big thanks to you guys. Regards, Abdulaziz Jasser |
This web page was last updated on Monday, May 6, 2024 at 01:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |