Query Help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Query Help

Author

Topic

suketu9
Starting Member

13 Posts

Posted - 2006-08-25 : 17:26:02

I am new to SQL Server and SQL, I am trying to run this query that was given to me to figure out whats wrong and I keep getting error:
The syntax represenation of string datetime value is not valid.

Following is the query:
select
boa.pid_entry Entry,
timestamp(boa.first_date, boa.first_time) START_TIME,
boa.SDR_DSPL_STATUS CIMS_LOAD_STATUS,
timestamp(pull.date_stamp,
pull.end_time) PULL_TIME,
boa.PRILD_VPLOC_TMSP VP_Load_Time,
boa.DIST_SORT_TYPE SORT_TYPE,
pull.items_pulled,
pull.dollars_pulled
from
IQ000.CSM_SY_LOG_PULL PULL,
IQ000.CSM_BOA_VIEWER BOA
where
pull.process_id = boa.process_id
and boa.PID_CYCLE_DATE ='%sDate'
and boa.pid_entry not like 'C%'
order by
boa.pid_entry;

If I remove the boa.PID_CYCLE_DATE = '%sDate' then query works fine, so has to be some syntax issue here. Please help me out.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-25 : 17:31:48

Are you using Microsoft SQL Server? If so, which version?

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-25 : 22:39:39

it appears your error is

timestamp(boa.first_date, boa.first_time) START_TIME

First, in sql the data and time should be contained in the same column with a type of DateTime. SQL does not like splitting up the two.

Second, the Timestamp function is not used in the method you described in either SQL, or Mysql, But I am going to assume what you are looking to do is take the date from boa.first_date and the time from boa.First_Time and combine.

for this you will probably want to use the datepart function

http://support.microsoft.com/?id=186265

suketu9
Starting Member

13 Posts

Posted - 2006-08-27 : 18:07:57

I think we are using the latest version. We dont own the query , we are just trying to help. So what should timestamp be replaced with, if someone can provide with the code change that would be great. I dont know SQL much and have no knowledge of T-SQL. Thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-27 : 18:59:11

I don't think the issue is the timestamp. As you said

quote:
If I remove the boa.PID_CYCLE_DATE = '%sDate' then query works fine

and boa.PID_CYCLE_DATE = '%sDate'

Looks like PID_CYCLE_DATE is a datetime data type and you are comparing it with a string '%sDate'.

What is sDate ? Is it a variable that you suppose to pass in ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 07:37:14

Do a LIKE operation on the boa.PID_CYCLE_DATE ='%sDate' too.

AND CONVERT(VARCHAR, boa.PID_CYCLE_DATE, 112) LIKE '%' + @sDate

Peter Larsson
Helsingborg, Sweden

suketu9
Starting Member

13 Posts

Posted - 2006-08-28 : 10:09:29

I get error when I replace my code with CONVERT(VARCHAR, boa.PID_CYCLE_DATE, 112) LIKE '%' + @sDate. The error says unexpected token found ' + '. Please let me know what do I need to change. I know its this one code thats causing the query not to work. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 10:23:50

What is sDate made of?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-28 : 11:16:36

Can you post table structure, some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

suketu9
Starting Member

13 Posts

Posted - 2006-08-28 : 16:45:53

We got it to work with some minor changes done by our DBA. Thanks everyone for help

vishnu.cm
Starting Member

7 Posts

Posted - 2006-08-29 : 04:49:53

What solved the problem?

suketu9
Starting Member

13 Posts

Posted - 2006-08-29 : 09:39:45

they just changed the query with no sdate variable in it.

suketu9
Starting Member

13 Posts

Posted - 2006-08-29 : 10:24:18

Ok, this is the new query, but we had to add the sDate code back in and we are back to the same DateTime String syntax error. I am not able to find out what it the datatype for sDate or Pid_cycle_date. So can anyone provide me with some code that I can convert both of them or try convertin one of them and then do the compare. Here is the latest query.

select
boa.pid_entry Entry,
timestamp(boa.first_date, boa.first_time) START_TIME,
boa.SDR_DSPL_STATUS CIMS_LOAD_STATUS,
timestamp(pull.date_stamp, pull.end_time) PULL_TIME,
boa.PRILD_VPLOC_TMSP VP_Load_Time,
boa.DIST_SORT_TYPE SORT_TYPE,
pull.items_pulled,
pull.dollars_pulled,
timestamp(ACT.status_date, ACT.status_time) CIMS_LOAD_TIME
from
IQ000.CSM_SY_LOG_PULL PULL
RIGHT JOIN IQ000.CSM_BOA_VIEWER BOA
ON pull.process_id = boa.process_id
RIGHT JOIN
(select
process_id,
activity_id,
status_date,
status_time
FROM iq000.csm_in_activity
where
activity_id = 'SDR' and
status = '02'
order by process_id) as ACT
ON ACT.process_id = BOA.process_id
where
pull.process_id = boa.process_id and
and boa.PID_CYCLE_DATE ='%sDate'
boa.pid_entry not like 'C%'
order by
boa.pid_entry;

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-29 : 11:27:26

Again, are you using Microsoft SQL Server ?? You never answered the question.

In addition, what datatype is boa.PID_CYCYLE_DATE? Is it DateTime? Then why are you comparing it to a string literal '%sDate'? that will never be true.

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-29 : 11:38:29

I don't think it can be SQL Server. "timestamp" is a datatype, not a function in SQL Server.

I think suketu9 is just not answering that question because he knows we'll tell him to post his question on dbforumns.

CODO ERGO SUM

suketu9
Starting Member

13 Posts

Posted - 2006-08-29 : 12:44:30

we are using SQL Server, TSQL. Again this is not our query and I am just trying to help in order to solve this. The person who supports this is on 2 weeks vacation.

suketu9
Starting Member

13 Posts

Posted - 2006-08-29 : 12:57:00

I will post more about the datatype once I get some information. thanks everyone for your help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-29 : 13:55:53

I don't see how that query could be TSQL.

There are just too many things in the query that are incompatible with TSQL syntax.
1. The function "timestamp" is not qualified by the owner.
2. The derived table has an ORDER BY without a TOP.
3. This obviously makes no sense in SQL Server:
" boa.PID_CYCLE_DATE ='%sDate' "

CODO ERGO SUM

suketu9
Starting Member

13 Posts

Posted - 2006-08-29 : 14:31:03

Ok I will find out what exactly is this

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-29 : 15:32:48

You should find out what kind of technology you are working with before coming to a forum that is for a particular technology, Microsoft SQL Server.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources