Thread: Problem comparing dates
07-14-13, 18:54 #1Registered User
- Join Date
- Jul 2013
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
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;
07-15-13, 07:37 #2Jaded Developer
Provided Answers: 59The database we use is their version of Informix and does not have a data type for date and time together
- Join Date
- Nov 2004
- out on a limb
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 systemI'd rather be riding on the Tiger 800 or the Norton
06-25-15, 10:45 #3Registered User
- Join Date
- Jun 2015
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.