#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2005
    Posts
    1
    Rep Power
    0

    Unhappy 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:
    'yyyy-mm-dd hh:mm:ss.0000'

    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.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2005
    Posts
    5
    Rep Power
    0

    An Idea


    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.
  4. #3
  5. Code Cruncher
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Tasmania, Australia
    Posts
    121
    Rep Power
    14
    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.
    Code:
    Select * from Table Where CAST(datefield AS DATE)='10/15/1977'
    Beware of a programmer with a screwdriver!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    2
    Rep Power
    0

    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
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Romania
    Posts
    173
    Rep Power
    11
    why store the date as integer when you have the date datatype
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    1
    Rep Power
    0

    Extract


    yo utlizo


    Code:
    select  EXTRACT(year FROM FECHA)||'/'
                    || EXTRACT(month FROM FECHA)||'/'
                    || EXTRACT(day FROM FECHA);
    FROM tabla
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Yugoslavia
    Posts
    3
    Rep Power
    0

    hello


    hello
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Andorra
    Posts
    5
    Rep Power
    0

    hello


    hello
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up


    Originally Posted by SilverDB
    why store the date as integer when you have the date datatype
    At first, a simple question!

    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) -

    http://www.firebirdfaq.org/files/esqlcobol.pdf

    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.

IMN logo majestic logo threadwatch logo seochat tools logo