Ambigious Column Name 'TimeStamp'

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

SQL Server 2000 Forums

Transact-SQL (2000)

Ambigious Column Name 'TimeStamp'

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-21 : 14:44:26

I'm attempting to do two things in a query, first convert a field to a more readable format and then doing a join so that I can data within a certain time frame. Here's the query that I have so far:

select moncalladd.opname, moncalladd.schedname, moncalladd.adddate, moncalladd.initials, dateadd(n,Timestamp, '12/31/1899')-([timestamp]/1000)/60/1440 +1 as date
into temptable3
from mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete
on moncalladd.schedname = moncalldelete.schedname

what I'd like to do is to add one line to this

where date >= (this value will be a variable that I'll be passing from a web page)

Can someone please assist?

Thank you

Doug

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-21 : 14:51:41

sorry ... let me edit that entry: here is the updated query .. it's failing on the second [timestamp]

select moncalladd.opname, moncalladd.schedname, moncalladd.adddate, moncalladd.initials, dateadd(n,moncalladd.Timestamp, '12/31/1899')-([timestamp]/1000)/60/1440 +1 as date
into temptable3
from mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete
on moncalladd.schedname = moncalldelete.schedname

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 14:59:27

You have a column called timestamp in one of the other tables. You have to include the table name when you reference it.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-21 : 16:24:35

Rob,

Where would I reference it? I tried in the second part [timestamp] statement to reference it as [moncalldelete.timestamp] and it was still giving me an error. That's what's got me confused.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 16:25:43

Take the brackets off , moncalldelete.timestamp instead of [moncalldelete.timestamp]. If you use two part names, it has to be [moncalldelete].[timestamp].

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-21 : 17:37:08

rob,

thank you. I removed that ... lastly ... if I want to enter a date to check for this ... by putting a where clause:

select moncalladd.opname, moncalladd.schedname, moncalladd.adddate, moncalladd.initials, dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440 +1 as date
from mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete
on moncalladd.schedname = moncalldelete.schedname
where moncalladd.date = '3/11/2011'

I get the error "Invalid column name 'date' how can i use my where clause here to check for the value of <="some date"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 17:48:26

Two problems:

- "Date" is a reserved word in SQL Server. (so is "timestamp")
- Unless you have a column named "date" in that table, the WHERE clause will not work. You cannot reference column aliases in the WHERE clause.

Best advice: don't use reserved words for column or object names, e.g. date, timestamp, etc. If you can't change the names, then ALWAYS ALWAYS ALWAYS put brackets around your object identifiers.

I'm not exactly sure what you're trying to do with the query, but the whole "timestamp/1000/60/1440" part can likely be written more cleanly. My next suggestion is to modify the table to use datetime columns if you have to store dates.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-22 : 10:44:08

Rob,

Thank you for that information but this is a table that is created for us by a software vendor and the fields can't be changed. I did change the "date" part to "oncalldate." Essentially what I'm trying to do is to compare two databases based on two common things timestamp and schedname. The timestamp conversion is because the software vendor writes their time in seconds and that's the formula I was given to convert it to "real time." What would be your suggestion then for dealing with what I'm faced with?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 11:00:38

Just change the WHERE clause:

WHERE dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440+1 = '3/11/2011'

My original suggestion was to do another DATEADD using seconds but that formula doesn't look right for that. If it gives you the correct results then don't change it.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-22 : 12:02:16

Rob,

One last question. It seems that my WHERE clause needs dates to be formatted like this: '2011-03-11', rather than '3/1/2011'

here's a line of sample output from my query:


opname   schedname     adddate     initials          oncalldate
 5555	  TESTSCHED      40611	   AH	       2011-03-11 07:10:00.000

how do I format the date correctly?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 12:34:09

Is OnCallDate a datetime or varchar type?

If datetime, try this:

WHERE dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440+1 BETWEEN '2011-03-11' and '2011-03-12'

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-22 : 12:52:40

Oncalldate is a datetime. Since this is going to be data coming from a web page, I'd like to format that correctly. I know that I have to use convert but I don't know how I'd use it in my query.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 12:56:01

You don't need to format date strings if they're being compared to a datetime column. Although it is better to use yyyy-mm-dd since it is unambiguous.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 13:20:55

quote:
Originally posted by dougancil

Rob,

Thank you for that information but this is a table that is created for us by a software vendor

Of Course it...g$dD@Mn &*(^&^% ^*&%&^% *&%*&^% *&%^*&^ 3rd party vendors

I've sen more garbage than I can shake a stick at

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-22 : 13:45:06

Rob,

My options on this are to either convert it on the web page that will be feeding the queries for this data or to do it in the sql query. Users won't feel comfortable having to type dates as yyyy/mm/dd. That's why I would need to format the dates correctly, though I do understand what you're saying.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 14:01:55

Unless you have a mix of people entering dates as mm/dd/yyyy and dd/mm/yyyy you really won't need to convert them to another format. SQL Server will interpret them as dates as long as they're a valid format.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-03-22 : 14:14:50

Rob,

Sorry that was my mistake ... I had run a query earlier and it wasn't producing any results. I see now that it was my formatting in the query that was wrong.

Thank you

Doug

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources