Big Int to Date Conversion

Hi Guys!!!
i really need your help now. i tried my level best to make some
tricks on this problem but didn't make it.
i've got a Big Int datatype column contains the "Ticks" (Long value)
and i want to convert it into DateTime but i'm not able to do it. the
value is like this 632557193645506250 i want to convert it into
datetime format.
please help me out guys. i'm using ms sql server 2000.
and front end is vs .net 2005
i want to do it into sql Query please dont ask me the reason but
because of some constraints i can not do it in .net
PLEASE HELP ME GUYS.
hi,
Lucky wrote:
> Hi Guys!!!
> i really need your help now. i tried my level best to make some
> tricks on this problem but didn't make it.
> i've got a Big Int datatype column contains the "Ticks" (Long value)
> and i want to convert it into DateTime but i'm not able to do it. the
> value is like this 632557193645506250 i want to convert it into
> datetime format.
> please help me out guys. i'm using ms sql server 2000.
> and front end is vs .net 2005
> i want to do it into sql Query please dont ask me the reason but
> because of some constraints i can not do it in .net
>
> PLEASE HELP ME GUYS.
DATEDIFF() and DATEADD() builtin functions expect a parameter of the "int"
datatype... id you perform
SET NOCOUNT ON;
DECLARE @.d datetime, @.t bigint;
SET @.t = 632557193645506250;
SELECT @.d = DATEADD (ms, @.t, '19000101');
SELECT @.d AS [date as difference in ms from 01/01/1900];
which adds the specified num of ticks to a base data value (you are required
to know in order to perform calculation), you get
Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
date as difference in ms from 01/01/1900
NULL
as the provided "difference" exceeds the max supported value...
http://msdn.microsoft.com/library/de...da-db_3vtw.asp
if you can "convert" that big value into separated values as "days" for the
date part and "ms" (depending on the actual precision you are required to
provide) for the time part in order not to overflow the int datatype
(32767), you can easely use the DATEADD(..) function..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 4 Apr 2006 19:18:30 +0200, Andrea Montanari wrote:

>if you can "convert" that big value into separated values as "days" for the
>date part and "ms" (depending on the actual precision you are required to
>provide) for the time part in order not to overflow the int datatype
>(32767), you can easely use the DATEADD(..) function..
Hi Andrea,
Eh? 32767 ?
I think you meant to write 2,147,483,647 here...
To Lucky:[vbcol=seagreen]
Can you tell me how long a "tick" lasts, and what the starting point for
the meaasurement is? And what is the exact date and time corresponding
to the 632557193645506250 value in your post?
Hugo Kornelis, SQL Server MVP
|||hi Hugo
Hugo Kornelis wrote:
> On Tue, 4 Apr 2006 19:18:30 +0200, Andrea Montanari wrote:
>
> Hi Andrea,
> Eh? 32767 ?
> I think you meant to write 2,147,483,647 here...
oopsss.. just working with vb6 these days and the relative "int" dimension..
LOL.. I apologise ..
thank you for poiting it out..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply