sysdate = date not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sysdate = date not working

Author

Topic

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-05-24 : 01:25:11

I have a table containing a column with dates:

I want to find out how many days have passed since the project started. But I am confused by the results..

SELECT
start_date,trunc(sysdate) - trunc(start_date),sysdate
FROM project

is what I did. I am soo confused. Could somebody help me figure out why I got these strange results?

Start date |trunc(sysdate - start date)| sysdate|
----------------------------------------------------
18-FEB-09 37 24-MAY-09
06-FEB-09 37 24-MAY-09
02-FEB-09 37 24-MAY-09
11-FEB-09 37 24-MAY-09
11-FEB-09 37 24-MAY-09
19-FEB-09 37 24-MAY-09
29-JAN-09 37 24-MAY-09
12-JAN-09 37 24-MAY-09
19-FEB-09 37 24-MAY-09
13-FEB-09 37 24-MAY-09
13-JAN-09 37 24-MAY-09
12-FEB-09 37 24-MAY-09
17-FEB-09 37 24-MAY-09
04-FEB-09 37 24-MAY-09
09-FEB-09 37 24-MAY-09
21-JAN-09 37 24-MAY-09
30-JAN-09 37 24-MAY-09
28-JAN-09 37 24-MAY-09
22-OCT-08 37 24-MAY-09

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-05-24 : 01:29:34

Ohh, I figured it out. I have another table that I joined to project. It has the column start_date too.. I made a mistake below.
The query I used is :

SELECT
project.start_date,trunc(sysdate) - trunc(start_date),sysdate
FROM project

So the start_date in red is coming from the other table in my query since I didn't prefix it with the table name.

Just curious am I not supposed to get a "column ambiguously defined" error when I use start_date without prefixing with the table name when the column name is contained in more than one table? And how is it randomly taking the start_date from the other table?

Weird..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-24 : 01:31:38

are you using oracle?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-24 : 01:32:34

try posting your question in a Oracle forum. this is a Microsoft SQL Server forum

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-24 : 01:32:47

quote:
Originally posted by sqlclarify

Ohh, I figured it out. I have another table that I joined to project. It has the column start_date too.. I made a mistake below.
The query I used is :

SELECT
project.start_date,trunc(sysdate) - trunc(start_date),sysdate
FROM project

So the start_date in red is coming from the other table in my query since I didn't prefix it with the table name.

Just curious am I not supposed to get a "column ambiguously defined" error when I use start_date without prefixing with the table name when the column name is contained in more than one table? And how is it randomly taking the start_date from the other table?

Weird..

but you're refering only project table in query, then how will it take start_date from other table? or is it that posted query was not the complete one?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 04:22:18

The Microsoft SQL Server equivalent is DATEDIFF.
Read about DATEDIFF here http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-05-24 : 22:17:38

Yes,it is not the complete query.. didn't realize that the rest of the query was the reason for the error. Sorry guys.

I am using Oracle SQL developer. I assumed that since I am using sql statements in it it would be ok to post here. I will post in the oracle forums then.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources