Using Date formats

Last post 07-16-2012 5:39 PM by angus.kemp. 2 replies.
Page 1 of 1 (3 items)
Sort Posts: Previous Next

  • 07-16-2012 4:30 PM

    Using Date formats

    Reply Contact
    Hi, just a quick question regarding queries and using date compares. If i have a field in a DB thats a string field with a date entered in it, can I use it as a date to compare when pulling data out of the DB. something like the SELECT * From X where date > XXX . or because it isnt in the PSQL date format it cant do the comparison? And on that note whats the pervasive Date format is it ISO standard YYYYMMDD ? and while im at it, can the statement compare dates where it includes the date and time with one that has just the date? does it put a generic time in or will it fail the comparison?

  • 07-16-2012 4:59 PM In reply to

    Re: Using Date formats

    Reply Contact
    http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=sqlref/syntaxref.03.71.html#2342178
    About two thirds down this page is the following: 
     
    date-literal
    Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. SQL_CHAR and the vendor string representation are treated as a value of type SQL_DATE. This becomes important when conversions are attempted.
    Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.
    The next two statements return all the classes whose start date is after 1995-06-05.
    SELECT * FROM Class WHERE Start_Date > '1995-06-05'
    SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'}
    Pervasive PSQL supports the following date literal format: 'YYYY-MM-DD'.
    Dates may be in the range of year 0 to 9999.
     What format are your string dates?  Most likely, you will need to use various string scalar function to either convert a date into a string matching your format, or to convert your string into the 'YYYY-MM-DD' format, and they use cast() or convert() to convert that string to an actual date.

    select convert('1995-12-20', sql_date);
    select cast('1995-12-20' as date);

     

  • 07-16-2012 5:39 PM In reply to

    Re: Using Date formats

    Reply Contact
    dates are being stored as "14:24:14 16/07/2012" . which i think is going to cause problems. I can redo this db to take in the PSQL formats and that might be easier in the long run, thanks for the quick reply
Page 1 of 1 (3 items)