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

 

[kittyyo]

Hi,

please post the code you are using to create the linked server.

Greetings,
Anne

 

[atiaran]

Hi kittyyo

Thanks for replying. I actually did not put any code to create the linked server. I did it within the Enterprise Manager (Right Click on Linked Servers under Security)

Atia

 

[philhege]

Looks like the number of milliseconds since January 1, 1970 (Unix date).

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[atiaran]

It came from a MySQL database and I havent got a clue how I'm gonna convert it so that it is acceptable by SQL. Any help would be very much appreciated.

Atia

 

[r937]

in the SELECT against the mysql table, use the FROM_UNIXTIME function to convert the integer to a datetime value

r937.com | rudy.ca

 

[atiaran]

Thank for replying r937. The thing is, I am doing everything already in SQL rather than in MySQL, so can the FROM_UNIXTIME function still be used when say creating views

I dont seem to find any syntax of it at all when used against a table taken into SQL.

Cheers
Atia

 

[r937]

are you absolutely sure about those sample values?

unix timestamps start at 0 for 1970-01-01, and go up from there, finally running out of numbers some time in 2039

today, for example, 2007-02-06 = 1170738000

so if those examples you posted are real, they aren't unix timestamps



r937.com | rudy.ca

 

[SQLBill]

I suggest posting the question in the MySQL forum, they should be able to help you better.

-SQLBill

Posting advice: FAQ481-4875

 

[atiaran]

I am absolutely sure about the values.

Thanks r937 and thanks SQLBill for the suggestion. I would do that.

Atia

 

[r937]

weird

maybe they are accurate to a millisecond, but multiplied by 1000 to make them (big) integers

here's something interesting:

select t
     , from_unixtime(t/1000) as t1
  from (
       select 1170430556294 as t
       union all
       select 1126644967922
       union all
       select 1126868785238
       ) as data
       
t              t1
1170430556294  2007-02-02 10:35:56
1126644967922  2005-09-13 16:56:07
1126868785238  2005-09-16 07:06:25

if you cannot use FROM_UNIXTIME (which is a mysql-only function) when selecting data from the mysql table, then you will have to do the conversion with sql server functions, and i think i have an idea for that

but please first confirm the datetime values to make sure we understand those big integers correctly




r937.com | rudy.ca

 

[r937]

here's the sql server function i was thinking of...

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

notice there's a difference of 5 hours

this is likely a GMT offset issue (even though both database engines are on the same machine here, i guess one of them has an offset built in)

but at least you're on your way

r937.com | rudy.ca

 

[atiaran]

Thank you very much everybody, I've got it now.

A big appreciation to all of you.

 

[atiaran]

Hi chaps

It's kind of following from the question before but slightly in a different direction.

How can you check what are the tables names in the MySQL database when you use linked server to get the data from MySQL across to SQL.

Thanks a lot for your help
Nina

 

[SQLDenis]

2 ways

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
[WHERE table_schema = 'db_name']


SHOW TABLES
[FROM db_name]


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[atiaran]

Hi SQLDenis

But SELECT table_name FROM INFORMATION_SCHEMA.TABLES
[WHERE table_schema = 'db_name']
would just show all the tables that have been taken across from MySQL to SQL via the linked server.

What happens is, I suspect that there are more tables in the MySQL database that should be taken into the SQL database via the linked server but how would I know that some tables in MySQL have not been taken into the SQL database.

 

[SQLDenis]

>>but how would I know that some tables in MySQL have not been taken into the SQL database.

with a simple left join or NOT EXISTS or NOT IN query

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[atiaran]

Hi SQLDenis

Maybe I did not explain correctly.

I've got a MySQL database. We've created a linked server to that MySQL database and used a store procedure to get the data from that MySQL database into the SQL. In the store procedure I had to specify which tables I wanted to import. But because I dont know the structure of the MySQL database I cannot import other tables than what I've already got.
How can I check all the table existing in the MySQL database.

Thank you

 

[SQLWilts]

I have limited experience of MSQL but:
couldnt you just execute SHOW TABLES [FROM db_name] on the linked server and drop the resultant dataset into a table (#temp table or memory table) on the SQL server? As Denis says, if you then use a left join from that table you would easily be able to pick out which tables are not already imported.........

Just a thought - although maybe using logic is not the way to go here

 

[SQLDenis]

here is a complete example of what I am talking about
just run it in one shot and you will see that table3 will be returned since it exists in one DB and not in the other

use master 
go

create database SQL_Server
go

use
SQL_Server

create table table1 (id int)
go

create table table2 (id int)
go

create table table3 (id int)
go

use master 
go

create database MySQL
go

use
MySQL

create table table1 (id int)
go

create table table2 (id int)
go

use SQL_Server
go

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

--table 3
use master 
go

drop database MySQL,SQL_Server

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/