October 7, 2002 at 11:42 am
Hi All,
In MYTABLE i have 2 cols as MYDATE, MYTIME which are having values for Eastern Time Zone. But i have to run some quries after converting these cols to Central Time Zone.
What i am planning is that i will merge MYDATE and MYTIME into MY_CENTRAL_TIME and then i will subtract one hour from MY_CENTRAL_TIME then i will get the real central time.
To achieve the above target i am running the following Queries:
UPDATE MYTABLE SET MY_CENTRAL_TIME = MY_DATE + SPACE(1) + MY_TIME
UPDATE MYTABLE SET MY_CENTRAL_TIME= DateAdd(hour,-1,MY_CENTRAL_TIME)
It is not working, please help me.
Thanks
October 7, 2002 at 12:00 pm
Is this what you are looking for?
create table mytable (time text, mydate text)
insert into mytable values('00:10:10','20021007')
select * from mytable
DECLARE @TIME CHAR(8)
declare @MYDATE CHAR(8)
SELECT @TIME=TIME, @MYDATE = MYDATE FROM MYTABLE
DECLARE @MY_CENTRAL_TIME DATETIME
SET @MY_CENTRAL_TIME = @MYDATE + ' ' + @TIME
SELECT @my_CENTRAL_TIME
SET @MY_CENTRAL_TIME = DATEADD(HOUR,-1,@MY_CENTRAL_TIME)
update mytable
set time=convert(char(8),@MY_CENTRAL_TIME,108),
MYDATE= convert(char(8),@MY_CENTRAL_TIME,112)
from mytable
select * from mytable
drop table mytable
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 7, 2002 at 1:17 pm
I was exactly looking for this. I appreciate your help.
You are a REAL GuRu.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply