Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL varchar Date

Posted on 2011-09-06
1
Medium Priority
?
270 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:APD_Toronto
1 Comment
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 36491469
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question