Home » SQL & PL/SQL » SQL & PL/SQL » joining between 2 different dates ?
joining between 2 different dates ? [message #621249] Tue, 12 August 2014 03:22 Go to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
Hi,

I my table I have a column called day_id(mm/dd/yyyy hh:mm:ss) and I need to join with date column(mm/dd/yyyy).
How to join this 2 columns ?

Thanks,
Re: joining between 2 different dates ? [message #621250 is a reply to message #621249] Tue, 12 August 2014 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
what are the datatypes of the two columns?
Re: joining between 2 different dates ? [message #621251 is a reply to message #621249] Tue, 12 August 2014 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Truncate the first (DAY_ID) column, and then join them, such as
where trunc(day_id) = the_second_date_column
Re: joining between 2 different dates ? [message #621255 is a reply to message #621251] Tue, 12 August 2014 03:35 Go to previous messageGo to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
cookiemonster day_id is the timestamp(6) and day_date is date
Re: joining between 2 different dates ? [message #621256 is a reply to message #621249] Tue, 12 August 2014 03:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
tondapi wrote on Tue, 12 August 2014 13:52
day_id(mm/dd/yyyy hh:mm:ss)


Minutes format is "mi" and not "mm", since "mm" means month.
Re: joining between 2 different dates ? [message #621257 is a reply to message #621256] Tue, 12 August 2014 03:36 Go to previous messageGo to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
lalit it is typo error
Re: joining between 2 different dates ? [message #621258 is a reply to message #621257] Tue, 12 August 2014 03:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
tondapi wrote on Tue, 12 August 2014 14:06
lalit it is typo error


If you do that typo in your code, it will take month instead of minutes. In your case, anyway, using trunc will get rid of the timestamp part. Follow Littlefoot's suggestion.
Re: joining between 2 different dates ? [message #621259 is a reply to message #621255] Tue, 12 August 2014 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tondapi wrote on Tue, 12 August 2014 10:35
cookiemonster day_id is the timestamp(6) and day_date is date


A DATE column also contains a time part (so is not "mm/dd/yyyy").
What kind of join do you actually want: up to second part or on day part?

Re: joining between 2 different dates ? [message #621261 is a reply to message #621259] Tue, 12 August 2014 03:57 Go to previous messageGo to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
trunc(day_id) is working fine, thanks Littlefoot
Re: joining between 2 different dates ? [message #621263 is a reply to message #621261] Tue, 12 August 2014 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to get your head around the fact that dates and timestamps do not have formats when stored in the DB. If you state the data is in a particular format then we have to wonder if it's actually a varchar column.
The formats are used when converting the dates to char for display purposes.
Re: joining between 2 different dates ? [message #621269 is a reply to message #621261] Tue, 12 August 2014 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tondapi wrote on Tue, 12 August 2014 10:57
trunc(day_id) is working fine, thanks Littlefoot


Are you sure?
Just think about the following:
SQL> select * from dual where sysdate=systimestamp;

no rows selected

Re: joining between 2 different dates ? [message #621270 is a reply to message #621269] Tue, 12 August 2014 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
He's applying trunc to the timestamp. The datatype of the result of that is date.
Re: joining between 2 different dates ? [message #621276 is a reply to message #621251] Tue, 12 August 2014 06:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Tue, 12 August 2014 13:54
Truncate the first (DAY_ID) column, and then join them, such as
where trunc(day_id) = the_second_date_column


SQL> select * from dual where trunc(systimestamp)=sysdate;

no rows selected


OP needs to apply trunc on both, timestamp and date.

SQL> select * from dual where trunc(systimestamp)=trunc(sysdate);

D
-
X


P.S. : Perhaps this is what Michel was pointing to.

[Updated on: Tue, 12 August 2014 06:04]

Report message to a moderator

Re: joining between 2 different dates ? [message #621282 is a reply to message #621276] Tue, 12 August 2014 06:44 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
depends on what's in the date column, doesn't it?
If the date column is populated with sysdate then yes.
If it's populated with trunc(sysdate), or anything else that specifies date and not time, then no.
Re: joining between 2 different dates ? [message #621285 is a reply to message #621282] Tue, 12 August 2014 06:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
cookiemonster wrote on Tue, 12 August 2014 17:14
depends on what's in the date column, doesn't it?


Yes, agreed. As OP said LF's query worked, it can be assumed the date column is populated with trunc(sysdate).
Re: joining between 2 different dates ? [message #621287 is a reply to message #621276] Tue, 12 August 2014 07:17 Go to previous message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Lalit Kumar B
P.S. : Perhaps this is what Michel was pointing to.


I just wanted to make it think about his "is working".

cookiemonster
depends on what's in the date column, doesn't it?


Yes, and so my question (still answered):

Quote:
A DATE column also contains a time part (so is not "mm/dd/yyyy").
What kind of join do you actually want: up to second part or on day part?


The "solution" depends on this.

Previous Topic: Find the number 3 in the third and last digits of an SSN
Next Topic: read hebrew from file
Goto Forum:
  


Current Time: Tue Sep 30 18:34:31 CDT 2014

Total time taken to generate the page: 0.10183 seconds