SQL Server 7 Datetime in SP 3

[logout151]

I am having a problem running a query that uses a datetime variable in the where clause in a sp.

Here is a cut of the code I am having a problem with.
________________________________________________________
Declare @StartTime as datetime
Declare @strsql as char(200)

Set @Starttime = '05:52:00 AM'

Set @Starttime = dateadd(Minute,8,@StartTime) /* I am using 8 minute intervals starting at 5:52 */

Set @strsql = "insert into mastertemp select Date, Time, App from [TempHold] where Time = " + @endtime
_________________________________________________________

I have tried using % to no avail.

I keep on receiving this error

Syntax error converting datetime from character string.

Which makes no sense because @endtime is datetime.

What should I do to correct this error?
Thank You all!

Log

 

[Bernadette]

Hi There

The problem seems to be with the line ....

Set @strsql = "insert into mastertemp select Date, Time, App from [TempHold] where Time = " + @endtime

as @strsql is a char datatype and @endtime datetme and you are trying the concatanate the two of them and set them into a string (@strsql)

Try something like


Set @strsql = "insert into mastertemp
select Date, Time, App from [TempHold] where Time = " + convert((char(15), @endtime)



Hope This Helps
Bernadette

 

[Cagliostro]

There is no problem:
Set @strsql = "insert into mastertemp select Date, Time, App from [TempHold] where Time = " + cast(@endtime as varchar)
John Fill

 

[logout151]

Thanks for your ideas, however I am still having 2 problems.

Both of your pieces of code led to the same problems, specifically.

1) The error message
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

Here is a 'select' of the @strsql right before it executes.

insert into mastertemp select Date, Time, App from temp_hold where Time = Jan 1 1900 6:00AM

2) The second problem is, I lost the seconds, which is critical to my query. Although it does not show in my previous post, I am actually using 5:59:59 AM, although for simplicity I did not include it.

Any more idea as to what to do next?

-Log

 

[Cagliostro]

Maybe you forgot about comas?
.....where Time = 'Jan 1 1900 6:00AM' John Fill

 

[logout151]

It actually does work when I add the single quote ('). But I am losing the seconds on the coversion.

If I select @endtime right before the query I get

1900-01-01 05:59:59.000

But the the query I would get 05:59 Or something to that effect. The coversion cuts of the seconds and I lose the reason for the query.

-Log

 

[Cagliostro]

Did you put the right format of target date column while designing? If yes, maybe the following will help(maybe not .
.... Time = cast('Jan 1 1900 6:00AM' as datetime) John Fill

 

[logout151]

I did, I just keep getting the error,

Syntax error converting datetime from character string.

Even though everything is datetime. I can't even do this in a select statement, so the table design is not a problem. I just can't run a query with a datetime variable as a criteria. If I key it in with the seconds using single quotes it works, but as a variable I can't get SQL server to except it.

Log

 

[Cagliostro]

I can't understand what is the trouble. Look this example in my SQLServer, it works fine. Maybe this sample will help you?

create table xxx
(
a int identity,
b datetime
)
declare @z as char(30)
set @z='1999-01-01 00:00:00.000'
insert into xxx select @z
insert into xxx select @z
insert into xxx select @z
insert into xxx select @z
insert into xxx select @z
insert into xxx select @z
insert into xxx select @z
select* from xxx John Fill

 

[Bernadette]

I have discovered why you are losing the time.

TheConvert() function drops the time from datetime datatypes when it converts the value to a character string.
However the Convert() function has a style option
(check this out in BOL).

If you change your Convert Function to

convert((char(15), @endtime, 120)
this will retain the time for you. However check out BOL to see if it is the datetime style that suits you!
Hope This Helps
Bernadette

 

[logout151]

Got it, right before I saw your thread Bernadette.

The dateadd function was the problem, it was cutting off the seconds.

Set @endTime = (convert(char(30),dateadd(MINUTE,6,@endTime),108)))

Thanks for all your help!!

-Log

 

[Bernadette]

No Bother .... I am glad it finally worked for you ! What a long day huh ! (btw ta' for the star - it is appreciated :-> Hope This Helps
Bernadette