Date into SQL from MySQL via linked server

[atiaran]

Hi chaps

I created a linked server within sql2000 to access data from a MySQL database. When the dates came across, they have a format of 13 digits that I cannot find anywhere on how to read it or how to convert it.

Please can somebody help I am a bit desperate as I've looked everywhere without any answers at all.

Here are some examples: 1170430556294 1126644967922 1126868785238

Appreciate your help.
Atia

 

[atiaran]

when i run the code I get the following error message

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MySQL.information_schema.tables'.

 

[SQLDenis]

>>when i run the code I get the following error message

Duh!, this was just an example that you could run on a SQL server box so that you could see how to construct the left join


so basically

select s.table_name from information_schema.tables s left outer join YourLinkedServerMySQLName.information_schema.tables m
on s.table_name = m.table_name
where m.table_name is null

of course you have to change YourLinkedServerMySQLName to your linked server name (probabaly needs to be 4 part)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[atiaran]

which I did...

 

[SQLDenis]

then how can you get this error message?

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MySQL.information_schema.tables'.

what is the name of your linked server?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[atiaran]

It is actually called MySQLServer

And this is what I run:

SELECT s.table_name
FROM information_schema.tables s left OUTER JOIN
MySQLServer.information_schema.tables m ON s.table_name = m.table_name
WHERE (m.table_name IS NULL)

 

[SQLWilts]

do you not have to do:

SELECT     s.table_name
FROM         information_schema.tables s left OUTER JOIN
                      MySQLServer.MySQLDBName.information_schema.tables m ON s.table_name = m.table_name
WHERE     (m.table_name IS NULL

Where MySQLDBName is the database name you are trying to find the tables from?

 

[SQLDenis]

true, i did mentioned that it probably needs to be 4 part notation

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[atiaran]

Hi

This is actually for r937 but would be very grateful if other people could answer as well.

This code that r937 gave further up is as follow:

select
dateadd(s,1170430556294/1000,'1970-01-01') as t1
, dateadd(s,1126644967922/1000,'1970-01-01') as t2
, dateadd(s,1126868785238/1000,'1970-01-01') as t3

t1 t2 t3
2007-02-02 15:35:56 2005-09-13 20:56:07 2005-09-16 11:06:25

and it worked perfectly but I have also a 8 digit field, which I believe is hours minutes and second (16016000 - equivalent to 4hrs 26min).

My question is how can i convert it.

Thanks a lot
Atia

 

[SQLWilts]

I can get it to 4 minutes 26 seconds, but not 4 hours.

Select Convert(Char(8),(dateadd(s,((16016000 / 1000)/60),'1970-01-01')),8)

Any further thoughts on where I may have gone wrong?

 

[atiaran]

Seen on the front end 16016000 is actually equivalent to 4hrs 26mins

I wanted to ask why it is divided by 60? is that because 1hour=60mins?

I've got a different digit: 422611000 (shown as equal to 117hrs 23min) but when I use your code:

Select Convert(Char(8),(dateadd(s,((16016000 / 1000)/60),'1970-01-01')),8)

it gives 01:57:23 meaning 1day57hours23mins, which I dont believe is equal to 117hours 23mins. Am I going mad here?

Please help

 

[SQLWilts]

OK, this is a little clumsy. You will have to substitute your variable for 422611000.

Select Hours = Cast((DatePart( dd, (Select dateadd(ms,422611000, '1969-12-31'))) * 24) 
+ (DatePart(hh, (Select dateadd(ms,422611000, '1969-12-31')))) As VarChar(16))
+ ':' +
Cast((DatePart(minute, (Select dateadd(ms,422611000, '1969-12-31')))) As VarChar(2))

Anyone have a better/cleaner way of doing this?

 

[atiaran]

hiya there

It does not really matter in what format it appears even in day:hour:min as long as it can be used again for calculation.
If I follow your code:

Select Hours = Cast((DatePart( dd, (Select dateadd(ms,422611000, '1969-12-31'))) * 24)
+ (DatePart(hh, (Select dateadd(ms,422611000, '1969-12-31')))) As VarChar(16))
+ ':' +
Cast((DatePart(minute, (Select dateadd(ms,422611000, '1969-12-31')))) As VarChar(2))

the result will not be of datatype time and therefore I wont be able to manipulate it further.

The calculation I am trying to have is: there is a Service Agreement Level (SLA) of 8 hours for example and I would have to compare it with the time spent, which I am desperately trying to convert. I appreciate the 117:23 means 117hrs23mins but I cannot use it as a time as it's the combination of 4 parts including the colon ) part.

 

[AlexCuse]

Atiaran - If you modify this function to chop off the seconds, it will give you the format you want. (hh:mm). If you want to be able to include days, look in the FAQ's for this forum.

Also, I think you would benefit from reading this:

http://www.tek-tips.com/faqs.cfm?fid=2244

here is the function

CREATE  function SecToTime(@sec integer)
returns varchar(12)

as

begin

declare @min integer, @hour integer, @time varchar(12), @dz char(2)

set @dz = '00'

If @sec >= 3600
Begin
    set @hour = (@sec / 3600)
    set @min = (@sec / 60) - (@hour * 60)
    set @sec = @sec - ((@min + (@hour * 60)) * 60)
    set @time = cast(@hour as varchar(6)) + ':' + 
        (right(@dz + cast(@min as varchar(2)), 2)) + 
        ':' + right(@dz + cast(@sec as varchar(2)), 2)
End
Else
Begin
    set @min = @sec / 60
    set @sec = @sec - (@min * 60)
    set @time = cast(@min as varchar(2)) + ':' + 
        right(@dz + cast(@sec as varchar(2)), 2)
End

return @time

end

Call it like this:

select dbo.SecToTime(16016000/1000)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLWilts]

In that case I suggest you do the following:

Convert your SLA times to minutes by multiplying them, by whatever it takes (60 if hours, 1440 if days) then comparing them with this:

Select TimeDiff = DateDiff(minute, '1970-01-01', (Select dateadd(ms,422611000, '1970-01-01')))

Remember to exchange your variable for 422611000
The above code will get the number of minutes associated with that figure.

 

[atiaran]

Thank you very much everybody.

I really appreciate your help.

Atia