How the record "nDateTime" in table TB_EVENT_LOG is written?

I have this SQL Database, the record nDateTime in  TB_EVENT_LOG Table is saved in a way that I cant understand, can anyone tell me how it is saved so I can read it-
In the Views -->IBA_Transactions...I cant read all the records clearly "Date-Time"
I attach the database, just unzip and add the ext .bak
Thanks
2014.zip
IhabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Habib PourfardSoftware DeveloperCommented:
instead of storing datetime values, they are storing seconds since 1970-01-01. as you can see in the view you need to decode it:

SELECT TOP 1000
        [nEventLogIdn] ,
        CONVERT(NVARCHAR(32), DATEADD(s, TB_EVENT_LOG.nDateTime, '1970-01-01'), 111) AS [Date] ,
        CONVERT(NVARCHAR(32), DATEADD(s, TB_EVENT_LOG.nDateTime, '1970-01-01'), 108) AS [Time]
FROM    [BioStar].[dbo].[TB_EVENT_LOG]

Open in new window


I have no idea why they are storing date data as int instead of datetime.
IhabAuthor Commented:
Thanks Mr. Pour
But I didnt get the idea , Please can you explain more?
IhabAuthor Commented:
I need to edit a data and time, How I can do this in the SQL Server Management Studio 2008?
Habib PourfardSoftware DeveloperCommented:
To convert datetime to int, you need to find difference between '1970-01-01' and your desired date in seconds:

SELECT DATEDIFF(s, '1970-01-01', '2001/01/01 00:02:22')
--RETURNS 978307342

Open in new window


To Convert int to datetime, you need to add seconds to '1970-01-01' to get your desired date:

SELECT DATEADD(s, 978307342, '1970-01-01')
--RETURNS 2001-01-01 00:02:22

Open in new window


To update:
DECLARE @date DATETIME = '2001/01/01 00:02:22'
UPDATE  dbo.TB_EVENT_LOG
SET     nDateTime = DATEDIFF(s, '1970-01-01', @date)
WHERE   nEventIdn = @Id

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.