SQL Data change

[#1]

Hello together,

I load from my SQL Server to rows "Datum" and "Uhrzeit". Like this:

Datum	Uhrzeit
2016-04-28 00:00:00.000	1899-12-30 04:00:00.000
2016-04-28 00:00:00.000	1899-12-30 05:00:00.000
2016-04-28 00:00:00.000	1899-12-30 06:00:00.000
2016-04-28 00:00:00.000	1899-12-30 06:19:05.000
2016-04-28 00:00:00.000	1899-12-30 07:00:00.000
2016-04-28 00:00:00.000	1899-12-30 08:00:00.000
2016-04-28 00:00:00.000	1899-12-30 09:00:00.000
2016-04-28 00:00:00.000	1899-12-30 10:00:00.000

From the row "Datum" I just need the 2016-04-28

and from the row "Uhrzeit" I need the 04:00:00.000 and 05:00:00.000 and so on

Like this:

DatumUhrzeit
2016-04-28 04:00:00.000
2016-04-28 05:00:00.000
2016-04-28 06:00:00.000
2016-04-28 06:19:05.000
2016-04-28 07:00:00.000
2016-04-28 08:00:00.000
2016-04-28 09:00:00.000
2016-04-28 10:00:00.000

How I can realized this?

[Anonymous]

Hi..

Use MID function as below:

MID(Datum,1,10)&MID(Uhrzeit,12,12) as DatumUhrzeit

[Anonymous]

Do you want to combine both fields then

try this in the script like this

load

Date(timestamp(Datum,'YYYY-MM-DD') &'  '& Time(Timestamp(Uhrzeit),'hh:mm:ss:sss') as DatumUhrzeit

from your table;

[zhadrakas]

Maybe like this

SourceData:
Load
Timestamp(Date#(date(Datum)) + time#(time(Uhrzeit)), 'DD.MM.YYYY hh:mm:ss.fff') as TEST
;
//YOUR SQL DATA
Load Timestamp('2016-04-28 00:00:00.000', 'DD.MM.YYYY hh:mm:ss.fff') as Datum,
Timestamp('1899-12-30 04:00:00.000', 'DD.MM.YYYY hh:mm:ss.fff') as Uhrzeit
autogenerate(1);

[TKendrick20]

This solution is long, but I think it will work with a wide variety of data types in your columns:

SELECT CONVERT(Date,CAST(Datum as DateTime)) + CONVERT(Datetime,CAST(Uhrzeit as Time))

Though it might be better to just fix this in your original data source.