problem with datetimepiker

Question

Dear every one Regards

am new in vb.net 

my problem is this!

i developed a windows form if i click on datetimepiker and then select a date then ok

if i do not click any date then massage show

Additional information: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

i have field name with opdate and its data type is also datetime

please help me

thank 

Answer 1

This question is related to windows form, you wont find good solution here  on this forum. Its does not seems directly related to SQL Server

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

Answer 2

Do you have the database field in SQL defined as a DataTime or a VarChar?  Open SQL Server Management Studio and verify the field.  Also make sure you are using the "Value" property of the DataTime picker and not the text.  You should be using DateTime in the SQL database and the VBnet application.

What object are you using in the form to display the data?  Can you post the line where the failure is occurring?

---

jdweng

Answer 3

Good day ABDULLAHAAJSOFT

I assume that in the database you are using datetime data type for the column, but in the application you are using  String type (String class). therefore when moving the data from the app to the database, the SQL Server Engine try to do implicit convert (implicit convert is when you do not use explicate CONVERT or CAST function, and the server find that there is an option to convert the data).

Now the implicit convert did not work since the server found that the string is out-of-range value for a date, and that might be related to the date format.

for example  this implicit convert will work ok:

declare @D datetime = '2015-10-22'

but if you try to use a different format for the date like yyyy-dd-mm then the convert will fail, and you will get the same error as you did. The server tried to convert the number 22 to month but this is out-of-range value for month:

declare @D datetime = '2015-22-10'

What is the solution?

1. dont ever use implicate convert!
Convert the data to the right type before you try to insert it to the database (in the application level)

2. You should not use string in the app as well, but DateTime type for example.

3. If you ahve to convert from string to database datetime then make sure that you use format like yyyy-mm-dd or use the right convert hint as you can see in this link:
http://www.sqlusa.com/bestpractices/datetimeconversion/

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

Answer 4

Thanks Ronen Ariely

You are absolutely right am using format dd/mm/yyyy

and my table column is as is it possible to insert data as dd/mm/yyyy

how 

thanks once again

Answer 5

If you *insist* on passing the date as a string, then you will have to enclose this in a CONVERT function, using the 3:rd parameter to specify the format of this string. Or, convert it in your application to a language neutral string, instead! More info at http://www.karaszi.com/SQLServer/info_datetime.asp

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 6

Thanks Ronen Ariely

You are absolutely right am using format dd/mm/yyyy

and my table column is as is it possible to insert data as dd/mm/yyyy

how 

thanks once again

While passing date values to sqlserver the value is interpreted based on language and regional settings.  So same date may be interepreted differently by another server where there are variations in regional settings. Hence best bet is to always pass dates in unambiguos universal format ie YYYYMMDD

more details here

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 7

Thanks Ronen Ariely

You are absolutely right am using format dd/mm/yyyy

 is it possible to insert data as dd/mm/yyyy

how 

thanks once again

>>  is it possible to insert data as dd/mm/yyyy

YES :-)

You should use explicit CONVERT in this case both for the insert and for the select queries, and in your case use the style number 103. I am from Israel and we use this format as well :-) Check the link that I posted above... it is ll there regarding the different style numbers.

for example dd/mm/yyyy fut for style 103:

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

http://www.sqlusa.com/bestpractices/datetimeconversion/

* Please remember that those are only display format options. In the database the data is store in the same way, no matter what is your culture! You convert the data before the insert and when you select it, so the external app will "speak" to the SQL Server currently, and both "understand" what the other meant (what part is the month and what part is the year and so on)

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

Answer 8

Bundle of thanks Ronen Ariely 

i change dd/mm/yyy  with

OpDateDateTimePicker.Format = DateTimePickerFormat.Custom
        OpDateDateTimePicker.CustomFormat = "MM/dd/yyyy"
        OpDateDateTimePicker.Text = Now.Date

now it work fine 

thanks for sport me

god bless you 

 

Answer 9

Bundle of thanks Ronen Ariely 

i change dd/mm/yyy  with

OpDateDateTimePicker.Format = DateTimePickerFormat.Custom
        OpDateDateTimePicker.CustomFormat = "MM/dd/yyyy"
        OpDateDateTimePicker.Text = Now.Date

now it work fine 

thanks for sport me

god bless you 

You are most welcome :-)

>>god bless you 
* by the way, I prefer to get people's blessing instead :-)
Thanks :-)

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]