Member Avatar for 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

  • 2 Contributors
  • 5 Replies
  • 184 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Good find, — and fair point from : converting the string on every query is brittle. The long-term fix is to convert the column once to a real date/datetime type, validate any bad rows, then query using date values. Below is a compact, safe migration + query pattern you can apply on a copy of the table.

Add a proper datetime column and parse the existing strings (the sample looks day-first, so use a culture-aware parser):

ALTER TABLE Consultation
ADD Con_Consult_Date_dt datetime2 NULL;

UPDATE Consultation
SET Con_Consult_Date_dt = TRY_PARSE(Con_Consult_Date AS datetime2 USING 'en-GB')
WHERE Con_Consult_Date IS NOT NULL;

Find and fix rows that failed parsing before you drop the old column:

SELECT Consult_No, Con_Consult_Date
FROM Consultation
WHERE Con_Consult_Date_dt IS NULL AND Con_Consult_Date <> '';

When the new column is clean, remove the old varchar, rename the new column, and make it not-null (do this in a transaction or on a test copy). Then query using a half-open range to include the full end day (BETWEEN with datetime literals can unintentionally exclude rows that have non‑midnight times):

-- includes every timestamp on 2013-12-31
SELECT Consult_No, Con_Hist_BMI, Con_Hist_UrineTest, Con_Consult_Date, Patient_No
FROM Consultation
WHERE Con_Consult_Date >= '2013-01-01' AND Con_Consult_Date < '2014-01-01';

Final notes: back up before schema changes; check SQL Server version if TRY_PARSE/TRY_CONVERT aren’t available; add an index on the new date column for performance; add a constraint or change the application layer so future inserts use native date types. This turns a one-off workaround into a robust, maintainable fix.

Recommended Answers

All 5 Replies

Member Avatar for Reverend Jim

Why not just

SELECT * FROM Consultation
 WHERE Con_Consult_Date BETWEEN '01-01-2013' AND '31-12-2013'
Member Avatar for 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? :)

Member Avatar for 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.

Member Avatar for 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
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.