Converting to DATETIME from separate date and time fields

I have a MYSQL database in my company's workplace and the timestamps are often stored like this:

ModDate  |  ModTime  |  Other fields开发者_StackOverflow中文版
------------------------------
20110405 |  203055   |  .....
20110506 |  214016   |  .....

I'm sure there was probably a valid reason for doing it at the time (developers have left), but we now mainly use SQL Server.

I've written a query to turn the ModDate field into standard DATETIME, but obviously I always get the time as 00:00:00.

How do I get a valid DATETIME object from SQL Server which combines the ModTime and ModDate field so that I get a combined correct value? Note, I've already extracted them into a local result set and I have no intention of changing the MYSQL database (I'm simply extracting from it with an OPENQUERY).

Assuming the table is now in SQL Server and the question has nothing to do with MySQL anymore.

UPDATE dbo.TableName
    SET ModDate = CONVERT(DATETIME, 
    CONVERT(CHAR(8), ModDate, 112) + ' ' 
    + STUFF(STUFF(ModTime,3,0,':'),6,0,':'));

If you want to make sure it's right first, just run:

SELECT
    ModDate, ModTime, NewModDate = CONVERT(DATETIME, 
    CONVERT(CHAR(8), ModDate, 112) + ' ' 
    + STUFF(STUFF(ModTime,3,0,':'),6,0,':'))
FROM dbo.TableName;

If you are indeed looking for a MySQL solution this will fit the bill, assuming YYYYMMDD and HHMMSS :

update yourtable set datetimecol =
    cast(concat(substring(moddate,1,4),'-', 
     substring(moddate,5,2),'-',
     substring(moddate,7,2),' ', 
     substring(modtime,1,2),':', 
     substring(modtime,3,2),':', 
     substring(modtime,5,2)) as datetime) 

If you don't want to persist the data then just do a select, rather than an update, like so:

select cast(concat(substring(moddate,1,4),'-', 
     substring(moddate,5,2),'-',
     substring(moddate,7,2),' ', 
     substring(modtime,1,2),':', 
     substring(modtime,3,2),':', 
     substring(modtime,5,2)) as datetime) as DateTimeCol
from yourtable

...assuming that 20110405 is the 5th of April, 2011 and that you have a new column named datetimecol you could do this :

update yourtable set datetimecol =
    convert(datetime, substring(moddate,1,4) + '-' + 
     substring(moddate,5,2) + '-' +
     substring(moddate,7,2)+ ' ' + 
     substring(modtime,1,2) + ':' + 
     substring(modtime,3,2) + ':' + 
     substring(modtime,5,2)) 

To add the time part into the datetime column: You can query MySQL to return the total seconds by using TIME_TO_SEC then, use DATEADD in MSSQL

Something like this:

UPDATE <TABLE>
SET <datetime column> = DATEADD(second, <MYSQL_QUERY>.TOTAL_SECONDS, <datetime column>)
FROM <TABLE>
INNER JOIN <MYSQL_QUERY> ON <TABLE>.<KEY> = <MYSQL_QUERY>.<KEY>

The <MYSQL_QUERY> is :

SELECT <KEY>,TIME_TO_SEC(MOD_TIME) FROM ...

I'm not not sure if you can use OpenQuery in joins but I'm positive that it will work. If OpenQuery doesn't work with joins, then maybe store the query result into a temp table in MSSQL.