Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL varchar Date

Posted on 2011-09-06
1
Medium Priority
?
271 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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