Solved

SQL LIKE problem

Posted on 2010-11-14
3
465 Views
Last Modified: 2012-05-10
Hi.

I am trying to get some data from SQL database with this string:

strDate = CDate(mydate)
sql = "select NoRcpt from RcptMain where Dates LIKE '%@" & strDate & "%' order by NoRcpt DESC"

However nothing returns.

NoRcpt is Integer column (used as Identity)
Dates is Datetime column



There is data in that table, and number returned should be 1 but i am receving nothing so I guess the problem is in  Dates LIKE '%@" & strDate & "%'.

Please assist.

P.S. In string strDate looks like "14.11.2010" in database in Dates column that data is 14.11.2010. 0:00:00 that's why i am using LIKE. I could format database input to ignore hh:mm:ss but I will use that eventualy.
0
Comment
Question by:Grogel
3 Comments
 
LVL 10

Accepted Solution

by:
Jini Jose earned 250 total points
ID: 34131767
it is not good to use the like for date fields.
if you want to use like for date fields use the below.

select * from rcptmain where cast(dates as varchar)
like '%'+ cast(year(getdate()) as varchar)+'%'
and cast(dates as varchar) like '%'+ cast(month(getdate()) as varchar)+'%'
and cast(dates as varchar) like '%'+ cast(day(getdate()) as varchar)+'%'

othewise you can use the between stmnt

select * from rcptmain where dates between '2010-01-01 00:00' and '2010-01-30 23:59'
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 34131786
You should be using parameters, which will take care of formatting the date for SQL.
But since you are using a string literal, I think you want anything that is on that day regardless of time right?

strDate = CDate(mydate)
sql = "select NoRcpt from RcptMain where DateDiff(d, Dates, '" & Year(strDate) & "-" & Month(strDate) & "-" & Day(strDate) & "') = 0 order by NoRcpt DESC"
0
 

Author Closing Comment

by:Grogel
ID: 34131956
Excelent, fast and smart guys.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now