Converting a particular date format to datetime format in SQL Server 2008

Question

3.50/5 (2 votes)

See more:

Hi,

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:

11.07.2013
11/07/2013
11-07-2013
11th Mar 2013
11th March 2013
2013-07-11

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!!

Posted 11-Jul-13 3:00am

Shruthi.BT

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Adarsh chauhan · Answer 1 · 2013-07-11T20:58:00

Hi,

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= &amp;#39;12-March-2013&amp;#39;

select @date

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