#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
    17
    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
    14
    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
    74
    Rep Power
    10

    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 11:52 AM.

IMN logo majestic logo threadwatch logo seochat tools logo