quote:

---

Originally posted by khtan

this http://msdn.microsoft.com/en-us/library/ms679430(VS.85).aspx ?

---

KH
Time is always against us

---

quote:

---

Originally posted by khtan

no worries. The datetime expert will be here soon

He just posted over at another datetime thread.

---

KH
Time is always against us

---

DECLARE	@SourceValue BIGINT

SELECT	@SourceValue = 128605122619735209

SELECT	@SourceValue = (@SourceValue - 47966688000000000) / 600000000.0

SELECT	DATEADD(MINUTE, @SourceValue, '17530101')

quote:

---

Originally posted by Peso

There are 55517 days between 16010101 and 17530101.

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by Peso

DECLARE	@SourceValue BIGINT

SELECT	@SourceValue = 128605122619735209

SELECT	@SourceValue = (@SourceValue - 47966688000000000) / 600000000.0

SELECT	DATEADD(MINUTE, @SourceValue, '17530101')

---

E 12°55'05.25"
N 56°04'39.16"

---

DECLARE	@SourceValue BIGINT

SELECT	@SourceValue = 128605122619735209

SELECT	@SourceValue = (@SourceValue - 47958048000000000) / 600000000.0

SELECT	DATEADD(MINUTE, @SourceValue, '17530101')

quote:

---

Originally posted by Peso

According to this page http://www.timeanddate.com/date/duration.html
there are 55507 days between the two dates, not 55517.

---

quote:

---

Originally posted by Peso

According to this page http://www.timeanddate.com/date/duration.html
there are 55507 days between the two dates, not 55517.

DECLARE	@SourceValue BIGINT

SELECT	@SourceValue = 128605122619735209

SELECT	@SourceValue = (@SourceValue - 47958048000000000) / 600000000.0

SELECT	DATEADD(MINUTE, @SourceValue, '17530101')

---

E 12°55'05.25"
N 56°04'39.16"

---

select
	DT = 
	dateadd(minute,(convert(bigint,pwdLastSet)-47966688000000000)/600000000.0,'17530101')
from
	(
	-- Test Data
	select pwdLastSet = 128605122619735209
	) a

DT
------------------------
2008-07-14 12:31:00.000

(1 row(s) affected)

quote:

---

Originally posted by Michael Valentine Jones

Works OK for me:

select
	DT = 
	dateadd(minute,(convert(bigint,pwdLastSet)-47966688000000000)/600000000.0,'17530101')
from
	(
	-- Test Data
	select pwdLastSet = 128605122619735209
	) a

DT
------------------------
2008-07-14 12:31:00.000

(1 row(s) affected)

CODO ERGO SUM

---

select
	pwdLastSet,
	DT = 
	dateadd(minute,(convert(bigint,nullif(pwdLastSet,0))-47966688000000000)/600000000.0,'17530101')
from
	(
	-- Test Data
	select pwdLastSet = 128437745474218750	union all
	select pwdLastSet = 0
	) a

Results:
pwdLastSet           DT
-------------------- ------------------------
128437745474218750   2008-01-02 19:09:00.000
0                    NULL

(2 row(s) affected)

quote:

---

Originally posted by Michael Valentine Jones

I think the overflows come from the values of 0.

The code below should eliminate the error.

select
	pwdLastSet,
	DT = 
	dateadd(minute,(convert(bigint,nullif(pwdLastSet,0))-47966688000000000)/600000000.0,'17530101')
from
	(
	-- Test Data
	select pwdLastSet = 128437745474218750	union all
	select pwdLastSet = 0
	) a

Results:
pwdLastSet           DT
-------------------- ------------------------
128437745474218750   2008-01-02 19:09:00.000
0                    NULL

(2 row(s) affected)

CODO ERGO SUM

---

DECLARE	@Year2000 BIGINT

SET	@Year2000 = 125902944000000000	-- 100 nanoseconds between 16000101 and 20000101
--SET	@Year2000 = 125911584000000000	-- Without the 10 days deduction

SELECT	*,
	DATEADD(SECOND, (NULLIF(t, 0) - @Year2000) / 10000000.0, '20000101')
FROM	@Sample