0

I'm completely new to this and have no background in IT or anything. I don't know how to use SQL although I am currently reading to learn. I'm using infomaker to create a report for work. Currently I'm using existing reports and making modifications and learning to use the program that way. I'm adding a clause to look for everything with a 12 hour expiration (for medication).

my column is pulling "compound.expires" but when I attempt to put in a value of 12 hours I get an error that says "a field in a datetime or interval value is incorrect or an illegal operation"

I was wondering if anyone has an idea as to how to format my 12 hour value to get the appropriate information. When I export the data to excel the expire hours show up in this format:

0 00:00 0 12:00 30 00:00

This is an example where its 0 hours, 12 hours, and 720 hour expiration. I have tried doing

'12' '12:00' '1200' '001200' '0 12:00' '01200'

If anyone has an idea as to what value I need to use that would be greatly appreciated. Thanks

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Greg Chau
  • 11
  • Welcome to the nightmare of dates. This issue will return throughout your entire IT career. Unfortunately excel is going to mangle whatever it really is. Are you able to find out what _datatype_ this field is? According to this, https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1600.htm, an Informix date literal looks something like this: `DATETIME (12:00:00.000)` but it's possible that infomaker has it's own format. – Nick.Mc Jan 10 '19 at 00:11
  • Here's an infomaker apge showing a simliar format: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01031.0400/doc/html/asc1252676795351.html For an interval it would be something like `INTERVAL '0 days 12:00:00.000000'` – Nick.Mc Jan 10 '19 at 00:12
  • But the very first thing you need to do is find out the data type of the field (it might be interval or datetime or it might be integer or something) – Nick.Mc Jan 10 '19 at 00:13
  • Those values are more or less appropriate for values of type INTERVAL DAY TO MINUTE. The `0 00:00` is 0 days, 0 hours, 0 minutes; the `0 12:00` is 0 days, 12 hours, 0 minutes; the `30 00:00` is 30 days (of 24 hours each = 720 hours) 0 hours, 0 minutes. That type as written will only allow up to 99 days, 23 hours, 59 minutes, You could make it cover longer periods using, for example, INTERVAL DAY(9) TO MINUTE — which will cover longer than your program or medications are due to last. _[…continued…]_ – Jonathan Leffler Jan 10 '19 at 03:08
  • _[…continuation…]_ If you want to insert a value based on a string, then you can try `'0 12:00'` (which you seem to say you've tried). If that won't work, then you need to describe more clearly how your input will be handled by whatever InfoMaker is using. Informix will accept the string `0 12:00` as a valid value; it would also accept as part of the SQL a value `INTERVAL(0 12:00) DAY TO MINUTE`. There are probably other ways to insert the values, but it depends on what the type of the column is. Do you have access to DB-Access (`dbaccess`)? – Jonathan Leffler Jan 10 '19 at 03:11
  • Thanks @Jonathan Leffler. I swear it didn't work when I tried it but I must have made a typo somewhere because I tried the '0 12:00' and it worked. Thanks for the the responses – Greg Chau Jan 11 '19 at 01:34

0 Answers0