how to solve Passing DateTime value to paramaterized query Error

Question

here is my query string

SELECT Payment ,Balance ,PatientNo FROM [GP_DB].[dbo].[GP] where GP.GPDate= (SELECT CONVERT(VARCHAR(24),@GPDate,103))

GPDate is a Date type column, not DateTime

and i pass parameter like this

cmd_select_treatment.Parameters.AddWithValue(
            "@GPDate"
            ,Convert.ToDateTime(dateTimePicker1.Value));

but the following error occur

Conversion failed when converting date and/or time from character string.

the standard way to approach this is view the TSQL on the wire.... — Mitch Wheat, Jan 18 '14 at 03:40

score 2 · Answer 1 · answered Jan 18 '14 at 03:54

GPDate is a Date type column

If it's a DATE column and your Convert.ToDateTime call returns a DateTime object then don't bother yourself with the CAST.

WHERE GP.GPDate = @GPDate

If dateTimePicker1 might contain a time component and you don't care for that then simply discard it before using its value:

Convert.ToDateTime(dateTimePicker1.Value).Date

score 0 · Accepted Answer · answered Dec 08 '14 at 03:44

0

@var is DateTime select cast(convert (varchar(8),@var,112) as Date)

answered Dec 08 '14 at 03:44

donata

score -1 · Answer 3 · edited Dec 12 '15 at 03:22

-1

I Think you need to add picture clause in your date conversion like the example

to_date('18/01/2014 10:15:20','dd/mm/yyyy hh24:mi:ss');

this should solve your problem

edited Dec 12 '15 at 03:22

Josh Crozier

answered Jan 18 '14 at 05:55

smn_onrocks

3 Answers3