Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Unanswered: Problem comparing dates

    We are a call center that uses the Noble Systems dialer. The database we use is their version of Informix and does not have a data type for date and time together. So I have a call_date field and a call_time field. I have an access database connected to this database to export files and run reports. I have been having problems trying to figure out how to select data from the database that is between a start date time and end date time on an access form.

    In the example below call_date_time is a text field that I combined call_date and call_time into. I am not sure if somehow I can combine the call_date and call_time in the query itself instead. Comparing dates that are text does not seem to be working well for me. Any help would be greatly appreciated.

    SELECT Count(*) AS sales, publication, service, addi_status
    FROM cust_otherpapers
    WHERE status='ES' call_date_time Between Forms!frmSalesRecordPrintout_New_v2!txtAltDateTime And Forms!frmSalesRecordPrintout_New_v2!txtAltEndDateT ime
    GROUP BY publication, service, addi_status
    ORDER BY addi_status;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The database we use is their version of Informix and does not have a data type for date and time together
    I don't believe it.
    Informix Chapter, First Edition : Informix Data Types and SAS Representations and Formats shows that Informix supports date, datetime and interval datatypes. so either the Nobel Systems dialler interface isn't exporting the call data correctly or its exhibiting symptoms of piss poor design.

    I'd check with the DBA's amanaging that sysem or Noble themsleves and see if their system does indeed use datetime values

    if you must compare on text values (and you shouldn't then make certaint he text values are stored as ISO date format (YYYY/MM/DD)

    what I think you need to do is to accept that the outside world will let you down, and make certain that when you pump the data into you Access tables you convert the incoming data into a datetime column

    the best solution get the Informix system to spit out datetime values
    next best write a VBA process which validates the data before entering it, as part of that validation process add data tot he datetiem column by mergiung the date and time elements time data should be presented to Acces using US or better ISO format ("YYYY/mm/DD HH:MMS")
    failing that rund a query after import which rebuilds the date time element


    but as said before investigate what the NOBLE system can supply. Id place as much cash as I have on betting that the system uses datetiem, just that the data stream you are getting isn't design for another computer system
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2015
    Posts
    1

    Problem comparing dates

    I realize that this is an old thread, but thought I would reply if anyone else was searching the internet for this issue.

    Believe it or not, the flavor of Informix that Noble is using does NOT have a DateTime data type. It's based on Informix, but it is a custom version (called "Atomix", for anyone who cares). The date is stored as DATE, while the Time is stored as an integer in the Call History table, but as a CHAR(8) in the Dialer List...which means it can lop off any leading zeroes on time before 10:00 AM in History, but they will be there in the Lists.

    We had the same issue, but I am working in MS SQL Server, not Access. I suspect Access can do the same type of formatting, here was the solution I came up with to get around their date time "solution" for the History version, the Calling List version would be a little more straight forward, as you would not need to check lengths of the Time data...just concatenate the two together and convert to DATETIME.

    CASE WHEN LEN([act_time])=(6) THEN CONVERT([DATETIME],(((((CONVERT([VARCHAR],CONVERT([DATETIME],[act_date]),(101))+' ')+LEFT([act_time],(2)))+':')+SUBSTRING(CONVERT([VARCHAR],[act_time]),(3),(2)))+':')+RIGHT([act_time],(2))) ELSE CONVERT([DATETIME],((((((CONVERT([VARCHAR],CONVERT([DATETIME],[act_date]),(101))+' ')+'0')+LEFT([act_time],(1)))+':')+SUBSTRING(CONVERT([VARCHAR],[act_time]),(2),(2)))+':')+RIGHT([act_time],(2))) END

    Hope this helps.

    Z

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •