datetime in bigint field

Question

I am trying to add a date time to a bigint field and then display it in a gridview using SQL query... First im not sure how to add the date time in big int field

so im using

long s= DateTime.Now.Ticks;

which is storing a sample value like 633896886277130000

then i want get only the date from it using the SQl Select statement and display on gridview..

SELECT MachineGroups.MachineGroupID, MachineGroups.MachineGroupName,
   MachineGroups.MachineGroupDesc, **MachineGroups.TimeAdded**,
   MachineGroups.CanBeDeleted, COUNT(Machines.MachineName) AS 'No. of PCs'
FROM MachineGroups
FULL OUTER JOIN Machines ON Machines.MachineGroupID = MachineGroups.MachineGroupID
GROUP BY MachineGroups.MachineGroupID, MachineGroups.MachineGroupName,
   MachineGroups.MachineGroupDesc, MachineGroups.TimeAdded, MachineGroups.CanBeDeleted"

can i use this

SELECT CONVERT(BIGINT, DATEDIFF(SECOND, '1970-01-01T00:00:00',
'2008-05-20T06:00:00'));

long TimeAdded = (DateTime.Now.ToFileTime() - 116444736000000000) / 10000000; i get this value 1254157826

Answer 1

I will suggest you to get the data in ticks only to your asp.net code, then convert it to whatever format you want.

Answer 2

In the first example, you're storing ticks, and then it looks like you're converting them back in your second example as if they're seconds, which is going to be way off. You'd have to treat them as either ticks or seconds, but not both. If you have the flexibility and you don't need the added precision, I imagine seconds would be much easier to work with. Also, it doesn't looks like SQL Server supports Ticks, so if you need that level of precision, you'll need to convert it to milliseconds, or something of that nature.

Then, you'd convert back using:

SELECT DATEADD(seconds, YourValueFromTable, CONVERT(DATETIME, 0))

Obviously, if you're not using seconds, substitute the appropriate keyword.

Answer 3

1 second is 10,000,000 ticks. If you want to work in ticks in the application, you'll need to remove the "T"s and multiple by 10,000,000:

SELECT CONVERT(BIGINT, DATEDIFF(SECOND, '1970-01-01 00:00:00',
'2008-05-20 06:00:00'))*10000000

Answer 4

If you are saving .NET Ticks in your MachineGroups.TimeAdded field, then it can be converted to SQL DateTime like below. (Subtract Ticks of 1900-01-01 and divide by ticks per day)

Select convert(datetime, (MachineGroups.TimeAdded - 599266080000000000)/864000000000) FROM MachineGroups

If you want just date use FLOOR:

Select convert(datetime, FLOOR((MachineGroups.TimeAdded - 599266080000000000)/864000000000)) FROM MachineGroups

EDIT You will get error if ticks is invalid you can use this for error checking:

Select CASE WHEN MachineGroups.TimeAdded < 599266080000000000 THEN null ELSE convert(datetime, (MachineGroups.TimeAdded - 599266080000000000)/864000000000) END FROM MachineGroups