Casting or converting nvarchar column in database to DateTime

Question

0.00/5 (No votes)

See more:

Hi,

I am using SQL Server 2008 and in db i am facing a problem with the casting or converting to datetime. I am actually trying to retrieve records from db which fall within say for ex: 2013-1-1 and 2013-6-20 but in my db the dates column is a nvarchar and it can be in any format say 01/01/2013 or 01-Sept-2013 or any possible way.. as it is all dependent on the user uploaded excel sheet ... means i create a db table in my db which will include all the columns and values as a user uploaded excel sheet has.. so while i create a db by default i am creating columns with data type nvarchar. Now, i want to know the best way to conert or cast the nvarchar to datetime format i tried this

SELECT CAST('20130401' as DATETIME)

but this will be working if date is saved like 01/04/2013 or 01.04.2013

what can we do to convert 01-Sept-2013 to datetime or date which includes words in SQL...

Waiting for your reply.Please help!!

Thanking you in advance!!

Posted 19-Jun-13 20:44pm

Shruthi.BT

Updated 19-Jun-13 20:46pm

v2

Add a Solution

2 solutions

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)

ArunRajendra · Accepted Answer · 2013-06-19T22:57:00

Solution 1

Try convert(datetime,FIELD_NAME). It failes for 01-Sept-2013 as Sept is not a valid abrivation. To handle this you might have to write a function to replace this string to standard abbrivation.

For ex convert(datetime,ReplaceFunction(FIELD_NAME)). Hope it helps

Posted 19-Jun-13 22:57pm

ArunRajendra

SagarRS · Accepted Answer · 2013-06-19T23:08:00

Solution 2

Use Convert instead of cast operator like

Select * from tblExcel where CONVERT(date,UploadDate,101) between '09/02/2011' and '06/29/2013'

Posted 19-Jun-13 23:08pm

SagarRS