0

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

2
Contributors
5
Replies
17
Views
2 Years
Discussion Span
Last Post by Reverend Jim
0

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? :)

0

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.

0

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
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.