Click here to Skip to main content
14,882,311 members
Please Sign up or sign in to vote.
3.50/5 (2 votes)
See more:

i am having a functionality in my project where i can select date and sort the records from database accordingly. I was using convert(datetime,columnname,111) to convert the column name which contains date to datetime format. This was working fine ... but the date column is not static means... in my project user will import excel sheet based on excel sheet datas database table is created defaultly as nvarchar all columns .... so if the excel sheet has date it can be in any format based on the excel sheet the user enters.. for ex:

11th Mar 2013
11th March 2013

any format... it can be... i need to convert this to datetime and then compare to values entered in textbox which is in datetime format and display result accordingly... i had used

ColoumnValues = "convert(datetime," + ColoumnNames + ", 111) between '" + ds.Tables[0].Rows[i]["StartDuration"].ToString() + "' and '" + ds.Tables[0].Rows[i]["EndDuration"].ToString() + "'";

this worked but not for datetime values and also 12th March 2013 ... such values...

then i changed code to

ColoumnValues = "convert(datetime," + ColoumnNames + ") between convert(datetime,'" + ds.Tables[0].Rows[i]["StartDuration"].ToString() + "') and convert(datetime,'" + ds.Tables[0].Rows[i]["EndDuration"].ToString() + "')";

this also did not work for 12th March 2013 format...

it shows error when i use these in queries as

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

so how can i convert the 12th March 2013 to datetime format ... plz help..

thanking u in advance!!

1 solution


Try this

ColoumnValues = "convert(datetime," + replace(ColoumnNames,'th','') + ") between convert(datetime,'" + replace(ds.Tables[0].Rows[i]["StartDuration"].ToString(),'th','') + "') and convert(datetime,'" + replace(ds.Tables[0].Rows[i]["EndDuration"].ToString(),'th','') + "')";

declare @date varchar(20)

set @date= '12-March-2013'

select @date

select CONVERT(datetime,replace(@date,'th',''),111)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900