Converting Integer to Datetime format

[Black_Lotus]

Hello Everyone,

I need help with conversion type. I'm using Visual Basic 6.0 as my frontend and SQL Server 2000 as my backend. There has been existing data in the database. I would like to know how to convert an integer to datetime format. For example:

This is the actual value from the database.

1087912290
1087912327

I'd like to know how to convert it datetime format.

Any help would be greatly appreciated.

Thanks,

Dennis

[nosepicker]

Any idea what the approximate date is supposed to be? It looks like it supposed to be the number of seconds since a certain date. For example, if it's the number of seconds since 1/1/1970, you would do this to find the date:

SELECT DATEADD(s, 1087912290, '1970-01-01')

Result: 2004-06-22 13:51:30.000

If that's not it, it could be just something like: 10/8/79 12:29:00

[Black_Lotus]

Thanks for the reply bro...I really appreciate you're help. Actually, that's my hunch too. It is possible that I'm viewing the total number of seconds. For the approximate date, I'm still trying to figure out how to get it.

I'll post more samples for you. Please help me figure this out.

[Black_Lotus]

You know something sir...the result for the date is close but not for the hours and minutes.

[Black_Lotus]

Sir, I would also like to know why am I getting the error: "Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime." when performing the following expression
select datediff(s, 1087912290, '2006-04-06')

[nosepicker]

What is the actual date? Is this data coming from a UNIX system? I think UNIX dates use the 1/1/1970 baseline, if I recall correctly.

[Black_Lotus]

Is it possible to get the difference between the total number of seconds and a given date?.... Like the current date?

[nosepicker]

DATEDIFF finds the difference between two actual dates. "1087912290" isn't an actual date. What are you trying to get from that query?

[nosepicker]

SELECT DATEDIFF(s, '2006-04-06', '2006-05-04')
or
SELECT DATEDIFF(s, '2006-04-06', getdate())

Keep in mind that getdate() includes the current time, while '2006-05-04' sets the time to midnight.

[Black_Lotus]

Sir its from an SQL Server running on a Windows 2000 Server. I'm trying to get the date and time by subtracting the total number of seconds from the actual date, since I don't have the approximate date to add to the total number of seconds.

However, since DateDiff's parameter should be in both date format, I guess its impossible. Do you have any ideas how I can go around with this?

[nosepicker]

Oh I see. You need to use the DATEADD function:

SELECT DATEADD(s, -1087912290, getdate())

[Black_Lotus]

Thanks for the reply sir....I really appreciate effort. It doesnt give me the exact results. Don't worry I'll post more samples for you. Currently, I'm still waiting for access to the server.

[Black_Lotus]

Hello Sir,

Please look at the table below. Its hard to format the table in a horizontal manner so I made it vertical.

Total number of seconds

1087912290
1087912327

Result generated added to (1970-01-01)

2004-06-22 13:51:30.000
2004-06-22 13:52:07.000

Value displayed from the existing application

06/22/2004 09:51 pm
06/22/2004 09:52 pm

Please notice that the dates are accurate between the dateadd and value displayed from the existing application, but not the hours and minutes.

Hope this helps figure out the value of the second parameter for the dateadd function.

[nosepicker]

If you subtract those number of seconds from the dates you gave, you get these results:

SELECT DATEADD(s, -1087912290, '2004-06-22 21:51:00')
Result: 1970-01-01 07:59:30.000

SELECT DATEADD(s, -1087912327, '2004-06-22 21:52:00')
Result: 1970-01-01 07:59:53.000

Since your dates don't have the seconds, I'd have to assume that the starting time is 8:00:00 AM. Therefore, the baseline date/time should be: 1970-01-01 08:00:00

Therefore, I believe your dates should be attainable by doing this:

SELECT DATEADD(s, 1087912290, '1970-01-01 08:00:00')
SELECT DATEADD(s, 1087912327, '1970-01-01 08:00:00')

[Black_Lotus]

Thanks! I really appreciate your help. I still think that they have a different algorithm, but let's just leave it to them. Coz after all, the results are the same. Thank you again for your help.