Displaying values of a table that falls BETWEEN specific dates

[Joemeister]

I am quite new to SQL but I was wondering how to achieve the following. How do I get specific values that fall in the year 2013 when the date format in the table is looking something like this:

1   94  pH  02-02-2012 11:48 AM 1200001
2   103 pH  09-06-2012 11:48 AM 1200002
3   96  pH  15-06-2011 3:12 PM  1200003
4   111 HPN 02-02-2011 08:55 AM 1200015
5   89  JP  12-12-2012 1:32 PM  1200007
6   90  Nic 19-06-2012 3:12 PM  1200009
7   99  Nic 21-01-2013 9:55 AM  1200012
8   102 pH  09-01-2013 3:23 PM  1200011
9   96  pH  15-02-2013 2:54 PM  1200003

I also want to use the BETWEEN clause that will almost look something like this:

USE Daspoort_Clinic Go
SELECT
GETDATE() AS Con_Consult_Date,
CAST(GETDATE() AS nvarchar(30)) Con_Consult_Date,
CONVERT(nvarchar(30), GETDATE(), 110)AS
Con_Consult_Date ;
SELECT Consult_No, Con_Hist_BMI, Con_Hist_UrineTest, Con_Consult_Date,  Patient_No
FROM Consultation
WHERE Con_Consult_Date BETWEEN '01-01-2013' AND '31-12-2013'
Go

I can't seem to get this to work because its sort of creating a 'new' table on top of the one that I already have when I am seleting the top 1000 rows.

Any help will be much appreciated.

Joe

[Reverend Jim]

Why not just

SELECT * FROM Consultation
 WHERE Con_Consult_Date BETWEEN '01-01-2013' AND '31-12-2013'

[Joemeister]

Because Con_Consult_Date is of type VARCHAR and will not work with BETWEEN clause. I must convert the VARCHAR to type DATE and then I will only be able to use the BETWEEN clause. But how? :)

[Reverend Jim]

My choice would be to create a new table with an actual Date field and doing a copy/convert, then manually checking the fields to make sure the dates were properly converted. That's the problem with storing a date as a string. You never know if "02-03-2012" is going to convert as Feb 3, 2012 or Mar 2, 2012. By writing the code to manually convert each record you can force the correct interpretation.

[Joemeister]

I came up with the solution. Thanks a lot!

SELECT Con_Consult_Date
FROM Consultation
WHERE 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

[Reverend Jim]

That's not a solution. It's a workaround and should be fixed. If you were to hand that off to another programmer he would be (in my opinion) justified in taking retribution.