Incorrect Syntax 1

[CJSilver]

I have SQL 2000. I have a field in a table that is data type CHAR. But the data in that field is really time. I can't change the field. But I need to do a mass adjustment so I am trying to convert the field to time make my change then convert it back. This is the query I am using:

Update raw_clocking set [TIME] = convert((Convert([Time],datetime)-1),char) where [date]='04/04/05' and [time]<'09:00'

But when I run it I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

Could someone please tell me what is wrong with my query?

C. Johnson

http://www.SilveradoCable.com

 

[mercwrought]

something like this perhaps

Update raw_clocking set [TIME] = convert(char,(Convert(datetime,[Time])-1)) where [date]='04/04/05' and [time]<'09:00'

 

[CJSilver]

Hello Mercwrought,

Thank you for responding. I tried your suggestion and I got the following error:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

C. Johnson

http://www.SilveradoCable.com

 

[dbomrrsm]

Do

select convert(char,(Convert(datetime,[Time])-1))
from raw_clocking
 where [date]='04/04/05' and [time]<'09:00'

to see what data is returned and then check that it will fit into your time column - the message you are getting is basically saying you want to put data into a field that cant hold that data.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[CJSilver]

Thank you for helping me with this DBomrrsm,

I ran the SQL statement and the data I get back is

December 31 1899 7:22AM
December 31 1899 7:23AM
December 31 1899 7:23AM
December 31 1899 7:24AM
etc.
I am not sure where the month and year are coming from. The time is correct.



C. Johnson

http://www.SilveradoCable.com

 

[dbomrrsm]

so do you have it solved now ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[CJSilver]

DBomrrsm,

No, I do not understand how I should handle this. I assume that the reason my update isn't working is because of the month day year that is showing up. I definitely don't want that to be updated to the table that way. How can I get around that?

C. Johnson

http://www.SilveradoCable.com

 

[mercwrought]

I expected that you would have problems with what you are doing IMO your method will not suit your needs. If your time is always the same you can do something like this

Update raw_clocking 
set [TIME] = (case cast(left([time],patindex('%:%',[time])-1)as int) 
	when 0 then '23:'+right([time],2)
	else right('00'+cast(cast(left([time],patindex('%:%',[time])-1)as int)-1 as varchar(2)),2) + ':'+ right([time],2)
	end)
where [date]='04/04/05' and cast([time]as datetime)<cast('09:00'as datetime)

man that is ugly I hope that some one has a better solution than that.
I’m not sure if the where statement will work but I gave it a shot

 

[dbomrrsm]

the rows returned above - an you show me how they look at the moment in the table before the update ie the date and time fields

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[CJSilver]

I thank you both for your help.

mercwrought,

Your statement worked perfectly! You have helped me immensely!

Thank you!



C. Johnson

http://www.SilveradoCable.com

 

[mercwrought]

There are probably better ways of doing this but that was the best I could come up with. There is also an issue with the date if you ever expect the time to roll backwards past midnight. Also when thinking about this I noticed that in my query the 0 should have been a -1 because there is a 0:01 hrs with the way it is it would error out on anything that happened just after midnight and create and return the wrong data on items occurring in the 1am hour. Here is a revision hope it helps I’m not sure if the date code will work or if you are even concerned

Update raw_clocking
set [TIME] = (case cast(left([time],patindex('%:%',[time])-1)as int)
    when -1 then '23:'+right([time],2)
    else right('00'+cast(cast(left([time],patindex('%:%',[time])-1)as int)-1 as varchar(2)),2) + ':'+ right([time],2)
    end),
    [date] = (case cast(left([time],patindex('%:%',[time])-1)as int)
    when -1 then left(cast(cast([date] as datetime)-1 as varchar(15)),8)
    else [date]	
    end)
where [date]='04/04/05' and cast([time]as datetime)<cast('09:00'as datetime)

 

[CJSilver]

Thanks mercwrought,

I greatly appreciate your help.

Where I live in the U.S we do not have daylight savings time. But I use an atomic clock on the internet to keep our job clocks all acurate. But they do use daylight savings time, so Monday morning when I came in all the times for people clocking in where off by an hour. I wanted to make a mass adjustment to fix it. The times were all between 06:00 and 08:30 so nothing was near midnight.

Again, I thank you for taking the time to look into this for me.


C. Johnson

http://www.SilveradoCable.com

 

[SQLBill]

This is your problem:

,(Convert(datetime,[Time])-1)

You convert TIME to a datetime value. DATETIME is always Date AND Time. If you don't supply a date, you get the default of January 1, 1900. Subtract one day (the -1 in your convert) and you get a date of December 31, 1899.

-SQLBill

Posting advice: FAQ481-4875