Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi..
Use MID function as below:
MID(Datum,1,10)&MID(Uhrzeit,12,12) as DatumUhrzeit
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;
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);
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.