OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

DATEDIFF() isn't working [SOLVED]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
TECHS
Newbie
Newbie


Joined: 17 Dec 2009
Posts: 3

PostPosted: Thu Dec 17, 2009 7:12 pm    Post subject: DATEDIFF() isn't working [SOLVED] Reply with quote

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
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Dec 17, 2009 7:50 pm    Post subject: Reply with quote

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:
  1. For ONLY a Date:
    • CURRENT_DATE
    • CURDATE()

  2. For ONLY a Time:
    • CURRENT_TIME
    • CURTIME()

  3. A Date AND Time ( AKA timestamp ):
    • CURRENT_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
View user's profile Send private message
TECHS
Newbie
Newbie


Joined: 17 Dec 2009
Posts: 3

PostPosted: Fri Dec 18, 2009 1:21 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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