This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
Hello:
I continue to miss the date issue to intorl in the database
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.
Adding to @Olaf Helper 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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(156.8, 46%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
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.
Hello:
this works but I find the time in the database table as I can delete the time in the table.
@Javier R 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.
Seeing this could the dateTimeOffset remove the time part
date-and-time-data
If you only care about the date, why do you have datetimeoffset in the first place?
Hello :
If I put Date causes me the failure:
Not sure what you are doing since I don't even see the full line. But obviously, you should have date in SQL SQL Server as well as in the client code.
@Javier R Does DatePicker control have a selected value？If DateNac.SelectedDate is null, .Value will throw the exception.
this code:
cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value = DateNac.SelectedDate.Value;
@Javier R You could change to DateNac.SelectedDate.Value.Date
if I change as DateNac.SelectedDate.Value.Date might be if in the seletedValue
@Javier R I don't understand well about your description. You mean that if you change the code to DateNac.SelectedDate.Value.Date, it might be a duplicate value in the database, right?
if I put the code I get this bugDateNac.SelectedDate.Value.Date
So what is the data type of this DateNac?
Datenac is of type date in the database. At best Datenac needs a convert
OK, so I am the database guy, but what I was asking for was the data type of the DateNac variable in C#. It is obviously there the problem lies. You have DateTimeOffset, when you should have Date.
for the database for the datepicker that best option Date or datetime so that the date is saved in the database.
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.
but if I put Date in the database I get error. above in the picture
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.
I set the date this way:**`cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value= DateNac.SelectedDate.Value.ToString("dd/MM/yyyy");****
doubt is whether in another language the date will change according to the code to adapt each language`
I can't see that ToString will be needed. Rather it will be harmful, because if the user's Regional Settings have a different format, the date will be misinterpreted.
Dates should be strings when presented to the user. Else they should be stored and transferred in binary format.
It can also be like this
cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value= DateNac.SelectedDate.Value.LocalDateTime;
@Javier R I suggest you could choose DateNac.SelectedDate.Value.LocalDateTime. Because if you choose DateNac.SelectedDate.Value.ToString("dd/MM/yyyy"), you need to convert the string parameter type to DataTime.
in the end I put to get the date out alone so I've tried LocalDateTime:cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value= DateNac.SelectedDate.Value.LocalDateTime;