Q:

datetime in bigint field

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

A:

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

