August 5th, 2005, 07:42 AM
how can I customize CAST (DATE TO STRING) format
when i use this filter in a query
" Select * from Table Where CAST (dateField as CHARACTER(800)) ='15-10-1977' "
it doesn't match any row because the casting from date to string result is like this:
Any idea about customize the casting result from date to string.
I can't use other differents solutions becasuse this query is automatically generated and i don't know the filter field type.
Thanks in advance.
August 9th, 2005, 10:43 AM
Select * from Table Where substr((cast(dateField as char(800))),1,10) ='1977-10-15'
If you can change the pass in just that much ie switch order of year month day you could use this.
August 9th, 2005, 06:12 PM
Cast the value to a DATE to strip off the time characters.
The date literal you are comapring with should be in single quotes in the format mm/dd/yyyy as shown below.
Select * from Table Where CAST(datefield AS DATE)='10/15/1977'
Beware of a programmer with a screwdriver!
November 7th, 2005, 05:28 AM
more about it
i have a field wich saves datetime value but in integer format: YYYYMMDD. example of today: 20051107
so, i want to execute a query where this field is higher or equal to system.date. example: where FIELD >= date.now
which will be the sentence?
does anybody know the solution?
i don't know how to transform de values in the correct format
thx a lot
November 8th, 2005, 02:41 AM
why store the date as integer when you have the date datatype
select EXTRACT(year FROM FECHA)||'/'
|| EXTRACT(month FROM FECHA)||'/'
|| EXTRACT(day FROM FECHA);
November 21st, 2012, 04:46 PM
November 24th, 2012, 10:44 AM
November 27th, 2012, 10:27 AM
At first, a simple question!
Originally Posted by SilverDB
Every date and time may be included in a mathematical equation to calculate some value, eg:
DayPay + 10day + 15minutes = 41284,01042 (in EXCEL for example)
DayPay = 12/31/2012 (december)
So, will be DayPay = 10/01/2013 00:15:00:000 (TimeStamp)
So, the DataTime (or, TimeStamp in Dialect 3) is stored in numeric format (INTEGER - INTEGER)
Before comma is Days. After comma Hours.
Thus, we can perform calculations with a DateTime type.
Rather, it was stored, for example, the type STRING, we would make a little complex calculations to find the expected result.
Since, Leap Years, Months (12) with 28, 30 and 31 days, Hours (23), Minutes (59), Seconds (59), Miliseconds (9999) -
TIMESTAMP 64 bits
1 Jan 100 00:00:00 BC to 29 Feb 32768 23:59:59.9999 CE
Date and time of day. Not available in dialect 1 databases,
use DATE instead.
DATE 64 bits
1 Jan 100 BC to 29 Feb 32768 CE In dialect 3 databases this type holds a date only. In dialect 1 databases this type is equivalent to TMESTAMP.
TIME 64 bits
00:00 AM to 23:59:59.9999 PM Time of day. Not available in dialect 1 databases.
Last edited by emailx45; November 27th, 2012 at 10:52 AM.