Conversion failed when converting data and/or time from character string

Question

Post reply

Conversion failed when converting data and/or time from character string

aoj85

Valued Member

Points: 62
More actions
January 20, 2010 at 6:03 am

#215818

am using visual basics to develop a search on my project using the date picker but any time i try to search for a date on my SQL database i received the following error message from my programme. "Conversion failed when converting data and/or time from character string " Any help from you guys will be highly welcome
#ous#

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 1

Too litle and vague information to support any serious advise.

What does your DBA says about it?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

BrainDonor SSCoach Points: 19243 More actions · Answer 2

This can be caused by the date format in your date control being different from the date format that the database is using. For example today's date can be '20/01/2010' for the UK format, but '01/20/2010' for the US format. Certain dates will be converted, but they won't necessarily be correct.

An easy way around this is to format the date within your vb software to 'dd mmm yyyy' format before you pass it to your Stored Procedure, then SQL will have less trouble working out the format.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

Actually, the best format to convert the date to is yyyymmdd. SQL will interpret this correctly regardless of the current language setting.

BrainDonor SSCoach Points: 19243 More actions · Answer 4

Lynn Pettis (1/20/2010)
Actually, the best format to convert the date to is yyyymmdd. SQL will interpret this correctly regardless of the current language setting.

Hi Lynn,

I was going to challenge you on that - I always always told (which of course doesn't mean it is correct) that 'dd mmm yyyy' would work in any instance, because with '20 DEC 2001' its obvious what part of the date is the month.

While putting my response together I came across 'Using date and Time Data' in BOL - ISO 8601 format for the date.

I've been lied to for all of these years!

Thanks for your interjection.

BrainDonor.

Steve Hall
Linkedin
Blog Site

aoj85 Valued Member Points: 62 More actions · Answer 5

I have tried using the format code but still i have the following error message when ever i tried to search my database table col for a match date.

ERROR MSG " Conversion from string "20/01/2010" to type 'Date' is not valid."

My code in my vb program search button is also pasted below;

....................................................................

Private Sub ButtonSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSearch.Click

Try

Me.TidesTableAdapter.FillByDate(Me.TidedataDataSet.tides, CType(DateTimePicker1.Text, Date).ToString("dd/mmm/yyyy"))

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try

End Sub

......................................................................

thanks guys for the help but still am having issues with this problem.

BrainDonor SSCoach Points: 19243 More actions · Answer 6

Me.TidesTableAdapter.FillByDate(Me.TidedataDataSet.tides, CType(DateTimePicker1.Text, Date).ToString("dd/mmm/yyyy"))

Would that be because you're not formatting it to either of the formats suggested by Lynn or myself? I still work in the deep dark pit of despair known as VB6, so I can't play with your shiny VB.NET code here, but will it allow a format of "dd mmm yyyy" or "yyyymmdd"?

BrainDonor

Steve Hall
Linkedin
Blog Site

briogene SSC-Addicted Points: 438 More actions · Answer 7

Converting date to match a specific database is nomally a trick affair. i used to have issues with date formats especially from VB to MysQL the method i use is to use stored procedure on the affected query and pass the date as a parameter.