Converting CST to EST

[hne]

Hi,
I am newbie to SQL, please be gentle with me (DISCLAIMER). Anyway, I have a few questions and would like to throw them out one at a time.

Frist question, I get the error "The number of variables declared in the INTO list must match that of selected columns." Help please?!

This is what I have:

declare @mytoday as char(10)
declare @c_myday as char(2)
declare @c_mymonth as char(2)
declare @c_myyear as char(4)
declare @mytable as char(16)
declare @c_rowdate as char(10)
declare @myhour as char(2)
declare @myrowdate as int
declare @ihour as int
declare @myCounter as int
declare @i_myday as int
declare @i_mymonth as int
declare @i_myyear as int

set @myCounter = 0
set @i_myday = datepart(dd, getdate())
set @i_mymonth = datepart(mm, getdate())
set @i_myyear = datepart(yyyy, getdate())
set @c_myday = cast(@i_myday AS char(2))
set @c_mymonth = cast(@i_mymonth AS char(2))
set @c_myyear = cast(@i_myyear AS char(4))
IF LEN(@c_myday) = 1
set @c_myday = '0' + @c_myday
IF LEN(@c_mymonth) = 1
set @c_mymonth = '0' + @c_mymonth
set @mytoday = @c_myyear + @c_mymonth + @c_myday

declare cch CURSOR FOR
select sourceid, rowdate, count(*)
from cmscallhistory
where left(rowdate, 8) = @mytoday
group by sourceid, rowdate
order by sourceid, rowdate

OPEN cch

FETCH NEXT FROM cch
INTO @myrowdate

while @@FETCH_STATUS = 0
BEGIN
set @myCounter = @myCounter + 1
set @c_rowdate = cast(@myrowdate AS char(10))
set @myhour = cast(right(@c_rowdate, 2) as int)
set @ihour = cast(@myhour AS int) + 1
set @myhour = cast(@ihour AS char(2))
IF LEN(@myhour) = 1
set @myhour = '0' + @myhour
set @c_rowdate = right(@c_rowdate, 8) + @myhour
set @myrowdate = cast(@c_rowdate AS int)

FETCH NEXT FROM cch -- get next value
INTO @myrowdate
END

CLOSE cch --- close CURSOR
DEALLOCATE cch --- memory cleanup

 

[Pattycake245]

In your cursor declaration you have three fields:

select sourceid, rowdate, count(*)
from cmscallhistory
where left(rowdate, 8) = @mytoday
group by sourceid, rowdate
order by sourceid, rowdate

when you fetch the first record into the cursor you are only assigning one variable instead of three:

OPEN cch

FETCH NEXT FROM cch
INTO @myrowdate

Tim

 

[dbomrrsm]

You are selecting three columns of data in the select and the variable @myrowdate can only hold one value - you need to select your date into three variables in this case ie

FETCH NEXT FROM cch
INTO @myrowdate,@anothervariable, @anothervariable1

make sure the variables are ordered in the same order as the values gained from the select statement.

[blue]DBomrrsm[/blue]

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

 

[hne]

Thank you. I added the variables:

INTO @mysourceid, @myrowdate, @mycount

However, I do not get an error, but I also do not get an output. Am I missing something else?

Thx.

 

[dbomrrsm]

what output do you want - if you just want to see what the variables have been set to do PRINT statements

ie

PRINT @mysourceid

before the END

However I guess this isnt what you are trying to do with the cursor - can you elaborate ?

[blue]DBomrrsm[/blue]

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

 

[hne]

I need to print @mysourceid, @myrowdate and @mycount.

What I am trying to do is to look at @myrowdate (which is an int) and make sure the date is EST and not CST. I also need to verify that the count is not less than a certain value. I decided to use CURSOR because I wanted to check each row and then proceed to the next. This is the gist of what I am attempted to do. Thx.

 

[Catadmin]

I don't think the DateTime datatype actually tracks timezones. I think it only tracks Year, Month, Day, Hours, Minutes and Seconds.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[hne]

How can I use PRINT to print all three variables (@mysourceid, @myrowdate, @mycount) on one row at a time?

 

[Pattycake245]

print @mysourceid + @myrowdate + @mycount

Tim

 

[JamesLean]

Or:

SELECT @mysourceid, @myrowdate, @mycount

--James

 

[hne]

Thanks! BTW, when I use SELECT, it prints my output, but it also prints out dashes(-----) on the next row. Is there a way I can get it to print out only the output data and not the dashes?

 

[hne]

When I use SELECT to print out my output, I get dashes or separator for each column on the next row. Therefore, if I have 100+ rows, I'll also get 100+ dash/separators. Is there a way to remove those dashes? Thx.

 

[Pattycake245]

In query analyzer go to tools-->options, click on the results tab and de-select print column headers.

Tim

 

[hne]

Thanks PattyCake245,
I have another question. My hour (which outputs as an 'int') looks at the hour in a 24-hour format. That is, '00' for '24' or 12am, '01' for 1am,... '23' for 11pm. The problem is that the database output hour is based on GMT. I need to convert this to EST and concatinate it to my date (as yyyymmddhh). Concatinattion is not the problem, what I would like to know is how can I convert my date from GMT to EST and also be mindful of the date going on? This is what I did to get the hour difference:

declare @diffhour as int

set @diffhour = datediff(hh, getutcdate(), getdate())

What I need to know is apply my '@diffhour' to the current hour to get the difference in time for each hour in the CURSOR loop?

 

[Catadmin]

GMT is 5 hours before EST. If you check your computer clock, you can see that EST has a (GMT-05.00) at the front of it. Have you tried using the DateAdd feature with the hour increment, a -5 as what you want to add and then the datevariable?

I.E. DateAdd(hh, -5, <datetimevariable>) ?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[hne]

Would this be the appropriate way to convert GMT time to EST?

-- GMT time returned in hour
set @diffhour = datediff(hh, '00:00', getutcdate())

-- Convert GMT to EST hour
set @dt_GMTEST_hour = dateadd(hh, @diffhour, getutcdate())

-- Get converted the EST Hour(HH)
set @ihour = datepart(hh, @dt_GMTEST_hour)

 

[hne]

I have another question:
I receive my date in GMT (as int). I know how to return the difference in time from GMT to EST. However, when my time is now EST (i.e. 00GMT = 19EST [-5 hours]), I need to set my day to the previous day (which is my EST day), until my EST day changes to the current day. How can I manipulate this datetime conversion in SQL?