CODEWITHSUNDEEP
sqldatatypesazure-data-factory
user2181700
user2181700

Reputation: 177

Mapping Dataflows time column to Sql time column

SQL server db has a time column datatype of

[start_time] time NULL, [end_time] time NULL,

but dataflows doesn't have a function for this. The only way I can think of doing this is a post query (if you fully recreate the table)

  alter table dbo.[testTable] 
  alter column [start_time] time(0) 
  alter column [end_time] time(0)

I tried using timestamp but again it's not a matching datatype

toTimestamp(substring(start_date,12,9),'HH:mm:ss')

so this doesn't work.

Any help on understanding this would be great

** updating with screens shots So this issue is for parquet or csv to Sql db tables.

if you have a column that looks like DateTime you need to keep it as a string as there is no toDateTime function only toTimestamp. Neither string or Timestamp can be converted to DateTime datatype in SQLdb sink. You will end up with nulls in your column

enter image description here enter image description here enter image description here

Sample befor using expression to change the start_date to yyyy-mm-dd THH:mm:ss

Blockquote

Upvotes: 1

Views: 329

Answers (2)

ray
ray

Reputation: 15227

Here is my testing CSV input:

start_date,end_date,start_date_time,end_date_time,start_time,end_time
09/01/2020,09/01/2020,09/01/2020 11:01,09/01/2020 11:01,11:01:46,11:01:52
09/01/2020,,09/01/2020 11:01,,11:01:47,
09/01/2020,09/01/2020,09/01/2020 11:01,09/01/2020 11:50,11:01:49,11:50:41
09/01/2020,09/01/2020,09/01/2020 11:01,09/01/2020 11:01,11:01:51,11:01:55
09/01/2020,09/01/2020,09/01/2020 11:01,09/01/2020 11:01,11:01:52,11:01:56

You may specify the data/time/datetime format for CSV source data: enter image description here

You can see the correct parsing result in data preview: enter image description here

After that, a simple sink activity should achieve what OP wants to do: enter image description here

The sink table schema I used for testing:

CREATE TABLE [dbo].[tempTargetTable](
    [start_date] date NULL,
    [end_date] date NULL,
    [start_date_time] datetime NULL,
    [end_date_time] datetime NULL,
    [start_time] time NULL,
    [end_time] time NULL
)

Result in DB: enter image description here

Upvotes: 1

ray
ray

Reputation: 15227

You can simply map the DATETIME column to the target TIME column in the sink activity.

enter image description here

Make sure the option "Allow schema drift" in sink activity is unchecked.

Make sure the option "Allow schema drift" is unchecked

My test schema:

-- source table
DROP TABLE IF EXISTS [dbo].[tempSourceTable]

CREATE TABLE [dbo].[tempSourceTable](
    [id] int IDENTITY(1,1) NOT NULL,
    [key] nvarchar(max) NULL,
    [start_date] datetime NULL
)

INSERT INTO [dbo].[tempSourceTable] VALUES ('key1', '2021-10-14 12:34:56')

SELECT * FROM [dbo].[tempSourceTable]

-- target table

DROP TABLE IF EXISTS [dbo].[tempTargetTable]

CREATE TABLE [dbo].[tempTargetTable](
    [id] int IDENTITY(1,1) NOT NULL,
    [key] nvarchar(max) NULL,
    [start_time] time NULL
)

result after execute the dataflow in a pipeline: enter image description here enter image description here

Upvotes: 1

Related Questions