sql server msde: 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,
[quoted text, click to view]

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/default.asp?url=/library/en-us/tsqlref/ts_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.shtm http://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

hi Hugo
[quoted text, click to view]

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.shtm http://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

[quoted text, click to view]

Hi Andrea,

Eh? 32767 ???

I think you meant to write 2,147,483,647 here....

To Lucky:
[quoted text, click to view]

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?

--