February 20, 2009 at 2:32 pm
We have a third party applicaton. They store dates as an integer string. They supplied me with code to convert the string to a date. I'm not overly skilled with functions but it's not returning the data in a datetime format and I'm not seeing why/where it's going wrong.
Here's the function:
CREATE FUNCTION ufnGetGentranDate (@gtime int)
RETURNS datetime
as
Begin
-- vendor supplied code
declare @GMTOffsetInSeconds integer
--declare @GMTOffsetInHours integer
declare @gdate datetime
/*******************INITIALIZATIONS*************************/
--set @GMTOffsetInHours = -5
-- FORMULA:
-- OffsetInSeconds = SecondsPerHour * OffsetInHours
set @GMTOffsetInSeconds = 3600 * -5
select @gdate = dateadd(second,(@gtime + @GMTOffsetInSeconds),'1/1/1970')
--End of vendor supplied code
Return @gdate
end
When I execute it using:
-- Execution string
declare @output as datetime
set @Output = dbo.usfGetGentranDate (1220905427)
print @Output
it returns: Sep 8 2008 3:23PM - which is correct but it's not formatting it the way I'd like. Any responses are most appreciated.
-- You can't be late until you show up.
February 20, 2009 at 2:43 pm
Curious, what does select getdate() return?
February 20, 2009 at 2:56 pm
Pure datetime:
2009-02-20 15:54:26.427
That's why I am so confused. Getting close to quitting time for the week. Maybe a fresh pair of eyes from home tomorrow will yield something as I'm just not seeing it..I appreciate the quick read.
-- You can't be late until you show up.
February 20, 2009 at 3:02 pm
It has something to do with the PRINT statement. This, select dbo.ufnGetGentranDate (1220905427); returns what you'd expect.
February 20, 2009 at 3:03 pm
Regional settings??
February 20, 2009 at 3:09 pm
Lynn Pettis (2/20/2009)
It has something to do with the PRINT statement. This, select dbo.ufnGetGentranDate (1220905427); returns what you'd expect.
I knew I was getting blurry-eyed looking at it and a fresh pair would see my mistake. It's a relief to be going into the weekend with that one off my plate. Thank you so much.
-- You can't be late until you show up.
February 20, 2009 at 3:11 pm
No problem.
Nice to actually help some one, and this one was easy. You provided everything I needed, I just had to tweak for a case-sensitive system.
Have a great weekend.
February 20, 2009 at 3:21 pm
Lynn Pettis (2/20/2009)
Have a great weekend.
You too!
-- You can't be late until you show up.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply