Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 64 bit FILETIME Format

Author  Topic 

davidr63
Starting Member

6 Posts

Posted - 2010-02-05 : 15:02:58
I have inherited a table that has a column in it that stores the date the record was last updated called LAST_UP. It is Char(16) and stores the date value as a 64-bit FILETIME. My question is how do I read and write these columns values.

The Example I can give is as follows:

LAST_UP = 0d4081fc676aac10

Real date = 02/05/2010 10:20:56 AM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-05 : 16:18:22
Something like this. We need another reference point to be sure.
DECLARE	@bin BINARY(8) = 0x0d4081fc676aac10

DECLARE @ns BIGINT = @bin,
@d DATETIME = '02/05/2010 10:20:56 AM'

SELECT @ns,
DATEADD(s, @ns / 1000000000, '19800101')



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

davidr63
Starting Member

6 Posts

Posted - 2010-02-06 : 21:21:00
thanks I will give it a try
Go to Top of Page

davidr63
Starting Member

6 Posts

Posted - 2010-02-08 : 10:26:18
I tried the code provided but could not get it to work. Here is an example of the data:
file_name last_mod File Date
--------- ----------------
File1 0d4081fc676aac10 2/5/2010 10:20
File2 0c03f1fc676aac10 2/5/2010 10:20
File3 0c18164bf2f7ac10 12/17/2009 10:43
File4 0bbe32fc676aac10 2/5/2010 10:20
File5 0a6a82fc676aac10 2/5/2010 10:20
File6 46bd5b9873db9c10 4/14/2009 14:30
File7 0a88d2fc676aac10 2/5/2010 10:20

Only the File_name and Last_mod are columns in the table.
The File Date is provided has a reference to what Last_mod should translate to.

I am looking for two SQL statement to read and write the last_mod value.
Thanks again for the help and sorry for being so stupid on this one.



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-08 : 11:46:52
Try this
DECLARE	@Sample TABLE
(
f CHAR(5),
lastmod CHAR(16),
filedate DATETIME
)

INSERT @Sample
VALUES ('File1', '0d4081fc676aac10', '2/5/2010 10:20'),
('File2', '0c03f1fc676aac10', '2/5/2010 10:20'),
('File3', '0c18164bf2f7ac10', '12/17/2009 10:43'),
('File4', '0bbe32fc676aac10', '2/5/2010 10:20'),
('File5', '0a6a82fc676aac10', '2/5/2010 10:20'),
('File6', '46bd5b9873db9c10', '4/14/2009 14:30'),
('File7', '0a88d2fc676aac10', '2/5/2010 10:20')

;WITH cteYak
AS (
SELECT f,
filedate,
CAST('' AS XML).value('xs:hexBinary(sql:column("Yak"))', 'BINARY(8)') AS Bin
FROM (
SELECT f,
filedate,
REVERSE(lastmod) AS Yak
FROM @Sample
) AS d
)
SELECT f,
filedate,
CAST(DATEADD(SECOND, Sec, DATEADD(MINUTE, Peso, 0)) AS DATETIME2(0)) AS Peso
FROM (
SELECT f,
filedate,
CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 / 60 AS INT) AS Peso,
CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 % 60 AS INT) AS Sec
FROM cteYak
) AS d



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-08 : 11:55:14
Also see http://weblogs.sqlteam.com/peterl/archive/2010/02/08/Convert-FILETIME-to-SYSTEM-time-using-T-SQL.aspx



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2010-02-09 : 13:49:56
See also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26544
from 2003.
Go to Top of Page
   

- Advertisement -