DateTime data insert to sql server

[Shyam_Pragash]

Hi,

This is my input data:

i try to insert data into sql server query… sometime date and time missing…

i tried code:

DateValue(Exl_Created_At)
System.Convert.ToDateTime(Exl_Created_At)

i want to convert the datetime format and insert into sql server.

All Variable is System.DateTime.

any other possibility tell me…

Thanks
Shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

You can use this conversion where you can include time as well

Datetime.ParseExact(“yourdatestring”,”MM/dd/yyy hh:mm”,System.Globalization.CulturalInfo.InvariantCulture)

MM/dd/yyyy hh:mm - this is the input format so chamge it according to the input you get from excel

If you want any specific date format then use .Tostring(“format you need”) like “dd-MM-yy hh:mm”

Cheers

[Shyam_Pragash]

Hi @Anil_G

i tried as you said… but i am facing issue on sometime date is empty or time is missing on the date.

how to do this case ?

Thanks
Shyam

[Shyam_Pragash]

Hi…

How to insert empty data in sql server ?

Thanks
Shyam

[RajKumar_DC (RajKumar Durai)]

Hi @Shyam_Pragash ,

Use Insert Query in ‘Run query’ Activity.

Hope you know create SQL connection

“Insert into insurer_master (Column1) values (‘ABC’)”

Thanks,
Rajkumar

[Shyam_Pragash]

Hi @RajKumar_DC

i have input file in excel file…that input excel details insert into SQL Server Database…

i have written the DB connection and Script for insert the data.

excel file —> for each activity —> using assign the value

“INSERT INTO HF_master_1 (”“Date”“, ““Closed Date””, ““Created At””) Values (@Date_BD, @Created_At, @Closed_Date)” – for Date columns to be inserted.

Since the excel cell has the date format defined but values were inserting blank and in some cell it is getting inserted with date and time.

Error:

Main.xaml: Compiler error(s) encountered processing expression “Closed_Date”.(2) : error BC30512: Option Strict On disallows implicit conversions from ‘Date?’ to ‘Date’.

Thanks
Shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

You cannot insert a blank value as date. So either donot process it in insert or insert a dummy date time when ever you have that scenario. That will solve your issue

Cheers

[Shyam_Pragash]

Hi…

Using if condition
var ==System.Nullable<System.DateTime>

Assign Closed_Date =If( String.IsNullOrEmpty(Exl_Closed_Date.ToString),System.Convert.ToDateTime(DBNull.Value), DateValue(Exl_Closed_Date))

Error

Assign: Object cannot be cast from DBNull to other types.

Thanks
Shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

Dbnull cannot be converted …try assigning dbnull directly if it doesnot work then you have to assign a dummy date like 01/01/1900 00:00 so that you know it is a dummy date

Cheers

[Shyam_Pragash]

Hi @Anil_G

Null value is allowed…

i cant write dummy value in the DB.

Thanks
Shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

Can you try like this

If( String.IsNullOrEmpty(Exl_Closed_Date.ToString),DBNull.Value, DateValue(Exl_Closed_Date))

By giving only DBNull.Value

Cheers

[Shyam_Pragash]

Hi @Anil_G

As per ur advice.

i am getting complier error

whether i have to change the varable type or not ?

used variable type:

Thanks
shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

Please change the datataype of the variable to object from datetime and check that shpuld solve it

Cheers

[Shyam_Pragash]

Hi @Anil_G

Still i am facing issue…

Thanks
Shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

I still see date_Db as datetime type. Can you change that to object please

Cheers

[Shyam_Pragash]

Hi @Anil_G

Same issue.

Cheers

Thanks
Shyam

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

Instead of using if like this …can you drag a if activity and assign both in two conditions please

This way you wpuld be able to assign for the old nullable datetime type also i beleive

Or try new object(DBNull.Value) repeat same for the date value as well

Cheers

[Shyam_Pragash]

Hi @Anil_G

I will check and Update u…

Thanks
Shyam

[Shyam_Pragash]

Hi @Anil_G

Unable to insert the data to sql server…

Thanks
Shaym

[Anil_G (Anil Gorthi)]

Hi @Shyam_Pragash

Is it because we are using object or because of Null?

If it is because we changed to object now we can change to Nullable datatetime I believe as we are using a IF activity

If it is because of Null try to Give Nothing

Insert Null into a date database field via database execute query

Hi Ritesh, please find below example:- “DECLARE @test_value int EXEC @test_value = [dbo].[Tasktest] @testId, NULL, NULL, NULL, NULL, NULL, @ID OUTPUT” you need to directly pass “NULL” at the column position. no need to specify the column name. let me know if it clarifies.

can you show the error

cheers