| Author |
Message
|
| gus |
 Posted: Wed Oct 04, 2006 4:59 am Post subject: how to find the number of days between two dates |
 |
|
Apprentice
Joined: 10 Apr 2002
Posts: 36
|
Good morning,
I am trying to use CAST two dates as INTERVAL DAY to find the number of days between the two dates and having no luck at all. The date formats are '2006-09-29' and 2006-10-03' These are only examples. They can be any valid dates. Has anyone done this before. Any help would be great. |
|
| Back to top |
|
 |
| Edde |
| Posted: Wed Oct 04, 2006 5:14 am Post subject: Re: how to find the number of days between two dates |
|
|
Acolyte
Joined: 01 Oct 2006
Posts: 67
Location: Moscow, Russia
|
Try to use EXTRACT function with DAYS option.
But it seems INTERVAl must work correct.
Can you give an example of your source code? |
|
| Back to top |
|
|
| gus |
| Posted: Wed Oct 04, 2006 6:22 am Post subject: |
|
|
Apprentice
Joined: 10 Apr 2002
Posts: 36
|
I got it working now. What a pain. You would think there would be better built in functions for dates. Here is what I did
DECLARE in_date DATE;
DECLARE curr_date DATE;
DECLARE numOfDays INT;
SET in_date = SUBSTRING(acctListRef."acctItem"[loopSub]."acctOpenDt" FROM 1 FOR 4) || '-' ||
SUBSTRING(acctListRef."acctItem"[loopSub]."acctOpenDt" FROM 5 FOR 2) || '-' ||
SUBSTRING(acctListRef."acctItem"[loopSub]."acctOpenDt" FROM 7 FOR 2);
SET curr_date = SUBSTRING(hostDate FROM 1 FOR 4) || '-' ||
SUBSTRING(hostDate FROM 5 FOR 2) || '-' ||
SUBSTRING(hostDate FROM 7 FOR 2);
SET numOfDays = CAST((curr_date - in_date) DAY AS INT); |
|
| Back to top |
|
|
| Edde |
| Posted: Wed Oct 04, 2006 6:36 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006
Posts: 67
Location: Moscow, Russia
|
You can use CAST with FORMAT specified, for example:
SET in_date = CAST (acctListRef."acctItem"[loopSub]."acctOpenDt" AS DATE FORMAT 'yyyyMMdd') |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Oct 04, 2006 6:46 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Why not just subtract the dates? CASTing as necessary?
Or am I missing the obvious? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Edde |
| Posted: Wed Oct 04, 2006 6:50 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006
Posts: 67
Location: Moscow, Russia
|
| Vitor wrote: |
Why not just subtract the dates? CASTing as necessary?
Or am I missing the obvious? |
You can substract dates, if you have dates.
But you cannot substract strings...
P.S. I understand. Substracting two dates you have INTERVAL, not INTEGER. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Oct 04, 2006 7:04 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Point taken, but cast as dates (using your method), subtract & recast from INTERVAL to INTEGER if you don't fancy the implicit cast seems (IMHO) easier than all the substringing and contatenating. Certainly less typing! 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Edde |
| Posted: Wed Oct 04, 2006 7:21 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006
Posts: 67
Location: Moscow, Russia
|
| Vitor wrote: |
| Point taken, but cast as dates (using your method), subtract & recast from INTERVAL to INTEGER if you don't fancy the implicit cast seems (IMHO) easier than all the substringing and contatenating. Certainly less typing! |
Totally agree  |
|
| Back to top |
|
|
|
|
