In SQL How is code written for Updating a SQL Database with Dates Between Date From and DateTo and Using ID Column

Question

In SQL How is code written for Updating a SQL Database with Dates Between Date From and DateTo and Using ID Column

Gary Simpson 471

Hi Good People

How Is Code Written for updating A SQL Database, Where the Date is between a Date From to a Date To AND where a ID column is used.

The code I have at the moment. Does not work, After " WHERE "

Code Below

 Private Sub UpdatePayment()

        SQL.AddParam("@DriverID", LbDriverID.Text)
        SQL.AddParam("@DateFrom", LbDateFrom.Text)
        SQL.AddParam("@DateTo", LbDateTo.Text)
        SQL.AddParam("@DatePaid", LbDatePaid.Text)
        SQL.AddParam("@GrossPay", LbGrossPay.Text)
        SQL.AddParam("@TaxableIncome", LbTaxableIncome.Text)
        SQL.AddParam("@TaxDeductions", LbTaxDeductions.Text)
        SQL.AddParam("@N_I_Deductions", LbN_I_Deductions.Text)
        SQL.AddParam("@NetPay", LbNetPay.Text)
        SQL.AddParam("@UpdatedBy", LbUpdatedBy.Text)
        SQL.AddParam("@DateUpdated", LbDateUpdated.Text)

        SQL.ExecQuery("UPDATE DailyRecords " &
                       "SET DatePaid=@DatePaid, " &
                       "GrossPay=@GrossPay, " &
                       "TaxableIncome=@TaxableIncome, " &
                       "TaxDeductions=@TaxDeductions, " &
                       "N_I_Deductions=@N_I_Deductions, " &
                       "NetPay=@NetPay, " &
                       "UpdatedBy=@UpdatedBy, " &
                       "DateUpdated=@DateUpdated " &
                       "WHERE DriverID=@DriverID And DateFrom=@DateFrom Between DateTo=@DateTo")



    End Sub

Can anyone help with the Code after Where. I need to use the DriverID column then Dates Between.

Kind Regards
Gary

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-02-25T23:10:47.833+00:00
We would need to see sample data and the desired result given the sample to be able to say what is right. Else, we can only guess. Maybe it is as simple as'

DateFrom = @DateFrom AND DateTo = @DateTo
LiHong-MSFT 10,061 Reputation points

2022-03-04T06:52:12.81+00:00

Hi @Gary Simpson
Does your code work now?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer accepted by question author

Viorel 127K

If there is a column called "Date", then try this: "WHERE DriverID=@DriverID And [Date] between @DateFrom And @DateTo".

Gary Simpson 471 Reputation points

2022-02-25T14:46:51.283+00:00

Hi Viorel-1
Thank you for getting back to me.

The only date columns I have are DateFrom and DateTo, I do Have another Date Column called DateUpdated, But this is used for the Date and time of input/Update.

DateFrom and DateTo are both date columns.

So Would It be beneficial to put a date column in the database, For the sole purpose of getting the code to work?

Kind Regards
Gary

2 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-02-25T23:10:47.833+00:00

We would need to see sample data and the desired result given the sample to be able to say what is right. Else, we can only guess. Maybe it is as simple as'

DateFrom = @DateFrom AND DateTo = @DateTo
LiHong-MSFT 10,061 Reputation points

2022-03-04T06:52:12.81+00:00

Hi @Gary Simpson
Does your code work now?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!
Gary Simpson 471 Reputation points

2022-02-25T14:46:51.283+00:00

Hi Viorel-1
Thank you for getting back to me.

The only date columns I have are DateFrom and DateTo, I do Have another Date Column called DateUpdated, But this is used for the Date and time of input/Update.

DateFrom and DateTo are both date columns.

So Would It be beneficial to put a date column in the database, For the sole purpose of getting the code to work?

Kind Regards
Gary

Answer 1

LiHong-MSFT 10,061

Hi @Gary Simpson
Please check :

WHERE DriverID=@DriverID And DateFrom>=@DateFrom AND DateTo<=@DateTo  
--Or  
WHERE DriverID=@DriverID And DateFrom Between @DateFrom AND @DateTo And DateTo Between @DateFrom AND @DateTo

Best regards,
LiHong

0 comments

Answer 2

Naomi Nosonovsky 8,906

Try
"WHERE DriverID=@DriverID And (DateFrom<=@DateTo and DateTo>=@DateFrom")

0 comments

Share via

In SQL How is code written for Updating a SQL Database with Dates Between Date From and DateTo and Using ID Column

2 additional answers

Your answer