CODEWITHSUNDEEP
sql-server-2008
MFJones
MFJones

Reputation: 39

Using the CONVERT() in WHERE Clause to convert varchar to date

I want to know how to achieve the following: I want to convert a varchar to a date format but so far I'm not very successful...

My date (varchar) looks something like this: 02-02-2012 11:48 AM

And I want to convert it to the following this: dd-mm-yyyy to be able to use the BETWEEN clause in the end.

I have the following script but it is not executing the way I want it to. What am I doing wrong and how can I get it to work? Any help will be much appreciated!

SELECT 
    Con_Consult_Date
FROM 
    Consultation
WHERE 
    CONVERT(varchar(30), Con_Consult_Date, 105) BETWEEN '01-01-2013' AND '31-12-2013'
GO

Upvotes: 1

Views: 23297

Answers (1)

SelvaS
SelvaS

Reputation: 2125

You need to convert and Cast the Date and then use it in the BETWEEN clause. Try this.

SELECT Con_Consult_Date
FROM Consultation
WHEREE CAST(CONVERT(CHAR(10), CONVERT(DATETIME, Con_Consult_Date, 105), 101) AS DATE) 
BETWEEN CAST(CONVERT(CHAR(10), CONVERT(DATETIME, '01-01-2013', 105), 101) AS DATE) 
AND CAST(CONVERT(CHAR(10), CONVERT(DATETIME, '31-12-2013', 105), 101) AS DATE)
GO

Upvotes: 2

Related Questions