Arrival_Time(int, NotNull) how do I convert

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 2000 Forums

SQL Server Development (2000)

Arrival_Time(int, NotNull) how do I convert

Author

Topic

iushahid
Starting Member

5 Posts

Posted - 2008-01-09 : 12:09:33

How do I convert this to date and time where I can select data greater then say 01/01/2008 06:00:00.example of data "1102507923"
It is a Remedy ARSystem Database and all datetime fields are like this in SQL tables.
Need help
so that I can do

Select count(*) from HPD_Helpdesk where Arrival_Time > {ts '2008-01-08'}

iushahid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-09 : 12:28:19

Here are two great topics covering your situation

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88250
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74033

E 12°55'05.25"
N 56°04'39.16"

iushahid
Starting Member

5 Posts

Posted - 2008-01-09 : 16:09:02

It flew over my head I am a baby you have to grab my finger and walk.
I have a field Arrival_Time in my Table name HPD_Helpdesk it has values in whole numbe
eg
1102507923
1102508493
so on
I have no idea what these number correspond to what date and time. what is the expression i use in query analyzer to return a meaning full date and time

iushahid

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-09 : 16:37:10

Iushahid,

Storing a date as a number in sql is not "standard." If it is done that way then, usually, the number is an offset from some base date.

If you do not know what that base date nor what the number represents, then we cannot know what it means either.

We can guess and try adding those numbers to a date in minutes or seconds or something else, but we would have know way to know what is correct. If you give us a number and the date it represents, then we can work backwards to figure out the base date.

iushahid
Starting Member

5 Posts

Posted - 2008-01-09 : 17:17:56

OK here we go
1136121419 -> 01/01/2006 8:16:59AM
1136126626 -> 01/01/2006 9:43:46AM

That is how I retreive these numbers from SQL table, first I got data out through Crystal Reports. Ahh Remedy Guys must have it in there ODBC what base to be for dates so when Crystal goes through Remedy ODBC it get all the info from there.
Understand this part now.
iushahid

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-09 : 18:39:58

If you subtract off those number from the supplied date you inseconds you get: 1969-12-31 19:00:00.00

That seems odd to me, but here is the way to convert those numbers to the supplied dates:

SELECT DATEADD(SECOND, 1136121419   , CAST('1969-12-31 19:00:00.000' AS DATETIME))
SELECT DATEADD(SECOND, 1136126626   , CAST('1969-12-31 19:00:00.000' AS DATETIME))

iushahid
Starting Member

5 Posts

Posted - 2008-01-09 : 19:11:41

I am off from my work will test tomorrow morning

iushahid

iushahid
Starting Member

5 Posts

Posted - 2008-01-10 : 08:58:58

From above understanding I did this statement from Query Analyzer
SELECT DATEADD(SECOND, (Select Create_Time from HPD_Helpdesk where Case_ID_ = 'HD0000000114690') , CAST('1969-12-31 19:00:00' AS DATETIME))

It gave me a single result
2006-01-01 08:16:59.000
Now how do I create an statement which run on Helpdesk table and show me all the dates in a column. This statement fails when I take away the where clause from the statement
Up till now I really appreciate all helpss from Lamprey and Peso
Sincerely
Iftekhar Shahid

iushahid

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 13:16:58

Try this:

SELECT 
	DATEADD(SECOND, Create_Time, CAST('1969-12-31 19:00:00' AS DATETIME))
FROM 
	HPD_Helpdesk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources