question

Jramos avatar image
0 Votes"
Jramos asked ·

Failed to convert parameter value from a DateTimeOffset to a DateTime

Failed to convert parameter value from a DateTimeOffset to a DateTime . I have a datapicker where I choose the date with sql server to enter the date in the database that then in another window of the uwp app comes date.
76237-2021-03-10.png


sql-server-generalwindows-uwp-xaml
2021-03-10.png (273.5 KiB)
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello:

I continue to miss the date issue to intorl in the database

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

That exception "System.InvalidCastException" is raised by .NET, not by SQL Server.

DatePicker.SelectedDate Property returns an object of type System.DateTimeOffset, which can not be implicit converted to date; you have to convert it first in .NET.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered ·

Adding to @OlafHelper-2800 answer, the SQL Server datetime type has no notion of an offset so you'll need to convert it to a datetime value of a specific timezone. If the client and server are in the same timezone, you could use DateNac.SelectedDate.Value.ToLocalTime(). For different timezones, standardize on storing values for a specific timezone to avoid ambiguity.. UTC is must commonly used (e.g. DateNac.SelectedDate.Value.ToUniversalTime()).

Alternatively change the column type to datetimeoffset. This allows you to preserve the original value and compare naturally compare datetime values with different offsets in T-SQL.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AryaDing-MSFT avatar image
0 Votes"
AryaDing-MSFT answered ·

Hi,

Welcome to Microsoft Q&A!

The reason for this issue is that the parameter type does not match the corresponding value type. Please check the following steps to do this.

Change the code to:

 cmd.Parameters.Add("@duoDate",SqlDbType.DateTimeOffset).Value=DateNac.SelectedDate.Value;

In addition, you need to set the corresponding column type of sql server database to datetimeoffset.


After my test, it is recommend to use SqlDbType.Date, which makes the time clearer, only the year, month and day, no minutes, seconds.
For example:

 cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value = DateNac.SelectedDate.Value.Date;

Note: set the corresponding column type of sql server database to date.



If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.











· 15 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello:
this works but I find the time in the database table as I can delete the time in the table.

0 Votes 0 ·

@Jramos You could receive the return result of SqlCommand.EndExecuteNonQuery method, which tells you the number of rows affected after performing the operation. Such as int i= cmd.ExecuteNonQuery();. You could use this number to judge whether the operation was executed successfully. This method is not suitable for query.

0 Votes 0 ·
Jramos avatar image Jramos AryaDing-MSFT ·

Seeing this could the dateTimeOffset remove the time part
date-and-time-data


0 Votes 0 ·
Show more comments
Jramos avatar image
0 Votes"
Jramos answered ·

for the database for the datepicker that best option Date or datetime so that the date is saved in the database.

· 8 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not that I know anything about datepickers, (as I said, I'm an SQL Server guys that knows about data-access code), but if you have a choice of data types when you define the date-picker, yes, you should have Date. At least if you want your life to be simple.

0 Votes 0 ·
Jramos avatar image Jramos ErlandSommarskog ·

but if I put Date in the database I get error. above in the picture

0 Votes 0 ·

Don't you get this feeling that this just going round in circles?

I think it should be like this:

cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value = DateNac.SelectedDate.Value.Date

But it would be simpler if you had DateNac as DateTime and not DateTimeOffset.

0 Votes 0 ·
Show more comments