| View previous topic :: View next topic |
| Author |
Message |
TECHS
Newbie
Joined: 17 Dec 2009
Posts: 3
|
 Posted: Thu Dec 17, 2009 7:12 pm Post subject: DATEDIFF() isn't working [SOLVED] |
 |
|
Well I wouldn't be surprised if these questions are posted and I'm just not searching correctly or patient enough to dig through all those posts so I'm gonna as a few questions.
1. GETDATE() I just can't get this function to work at all.
2. DATEADD() AND DATEDIFF() I can't seem to get these working either. Tell me what is wrong with my statement.
SELECT "Orders"."RequiredByDate" FROM "Orders" AS "Orders", "Customers" AS "Customers" WHERE "Orders"."CustomerID" = "Customers"."CustomerID" AND DATEDIFF(DAY,CURRENT_TIMESTAMP,"Orders"."RequiredByDate") < 5
My goal is to have the query tell me if I have less than 5 days to complete an order.
Any help would be appreciated
Last edited by TECHS on Fri Dec 18, 2009 1:18 pm; edited 3 times in total |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Thu Dec 17, 2009 7:50 pm Post subject: |
|
|
I will assume ( since you did not say ) . . . that you are using HSQL as your database engine. You can confirm this by looking at the status line at the bottom, and, if it says: "Embedded database" and "HSQL database engine".
According to HSQL documentation, found at:
http://www.hsqldb.org/doc/guide/ch09.html#stored-section
| http://www.hsqldb.org/doc/guide/ch09.html#stored-section wrote: |
DATEDIFF(string, datetime1, datetime2)
returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used. |
Therefore, you should modify your SQL Query to something like this:
| Code: | SELECT
"Orders"."RequiredByDate"
FROM "Orders" AS "Orders",
"Customers" AS "Customers"
WHERE "Orders"."CustomerID" = "Customers"."CustomerID"
AND DATEDIFF('DAY',CURRENT_DATE,"Orders"."RequiredByDate") < 5 |
NOTE: the difference is the first parameter of DATEDIFF is wrapped in single quotes, because it is a 'string'.
About GETDATE() . . . that is NOT a function in HSQL. If you want the current date / time ( computer system date / time ) information, use ANY of the following:
- For ONLY a Date:
- For ONLY a Time:
- A Date AND Time ( AKA timestamp ):
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.[/list][/list][/code][/quote] |
|
| Back to top |
|
|
TECHS
Newbie
Joined: 17 Dec 2009
Posts: 3
|
| Posted: Fri Dec 18, 2009 1:21 pm Post subject: |
|
|
| Thanks for the answer. I don't know where the status line is, but since several of my other SQL codes aren't working I assume that I should be looking for HSQL documentation from now on. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
