SQL varchar Date

Hi Experts,

I just inheritted a badly setup database, where paymDate is varchar(20), but its contents is formatted as m/d/yyyy

My question is, in this case, how can I SELECT all records between 6/1/2011  and 6/30/2011?

When i do the following, i also get June 2010 data.

Thank you.
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [id]
      ,[invoiceNumber]
      ,[paymentDate]
  FROM [citysightcarts].[dbo].[Invoices]
  
  WHERE [paymentDate] >= '7/1/2011'
    AND [paymentDate] <= '7/31/2011'
    
 ORDER BY id

Open in new window

APD TorontoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wdosanjosConnect With a Mentor Commented:
Please try:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [id]
      ,[invoiceNumber]
      ,[paymentDate]
  FROM [citysightcarts].[dbo].[Invoices]
  
  WHERE CONVERT(datetime, [paymentDate], 101) BETWEEN '7/1/2011' AND '7/31/2011'
    
 ORDER BY id

Open in new window


I hope this helps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.