Solved

showing date and what happened before that date

Posted on 2010-09-22
15
281 Views
Last Modified: 2012-05-10
Hello,
I am using asp, I am also using a mysql database. The date format in my database is as 2010-09-20. when my user enters 2010-09-20 I would like to show all from that date and before that date. what would I use ni this case.

I have tried this but it does not work

WHERE  thewinner.newsdate <=  '%"&Recordwinner__MMColParam&"%


I am grateful for all input.


cheers


kenny
0
Comment
Question by:thatelvis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
15 Comments
 
LVL 9

Expert Comment

by:wellhole
ID: 33735220
<= '" & Recordwinner_MMColParam & "'"

will create

<= '2010-09-20'

which will work whether your date is a string or a date.
0
 
LVL 1

Author Comment

by:thatelvis
ID: 33735399
thank you

when i have this

<= '" & Recordwinner_MMColParam & "'"

and this query is given 2010-09-20 i get no record

if i use Like then it will only show specific record for that specific date

regards

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33735460
is it definitely in that format? perhaps wrap it with date_format just to make sure
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 9

Assisted Solution

by:wellhole
wellhole earned 200 total points
ID: 33735809
thatelvis, why don't you try replacing it with

<= '2010-09-20'

and see if you get any results? If you do, that means your Recordwinner_MMColParam is in the wrong format. If not, then your newsdate is either in the wrong format or doesn't have the data you're looking for.
0
 
LVL 1

Author Comment

by:thatelvis
ID: 33736289
i have tried that but i still see all records

cheers

k
0
 
LVL 9

Expert Comment

by:wellhole
ID: 33736326
Then your Recordwinner_MMColParam is in the wrong format. I can't help you with that if you don't provide the value or the code that generates this variable.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33736555
what I was talking about with regards to date formatting. if date is definitely entered as yyyy-mm-dd format then you can perhaps check that by formatting date returned from the db, perhaps you have changed that formatting somehow
or perhaps just format the date entry variable

date_format(yourdate,"%Y-%m-%d")


but as wellhole says you need to confirm/show the format for Recordwinnder_MMCColParam
0
 
LVL 1

Author Comment

by:thatelvis
ID: 33737915
thank you the date in my db is yyyy-mm-dd


the date i was using was <= yyyy-mm-dd

I cut the date from the db and pasted it onto notepage

then i cut it from notpad placed it in my textfield and submitted it

the <= does not throw up any date the like dose throw up the date in question but I want the date in question and what was before it.

cheers

k
0
 
LVL 9

Expert Comment

by:wellhole
ID: 33738255
I'm not concerned about your input into your page. What we need you to find out is what is the value in Recordwinnder_MMCColParam. Try printing it to the page for example.
0
 
LVL 1

Author Comment

by:thatelvis
ID: 33742623
thank you sorry about been later in getting back to you. I am very grateful for your help

when I enter 2010-09-20 into the textbox and do a search.

I see the record that is returned shows the date as 9/20/2010

regards

and thanks again

k
0
 
LVL 1

Author Comment

by:thatelvis
ID: 33742643
I attach the complete recorset below in case you wanted to see it.
<%
Dim Recordthewinner__MMColParam
Recordthewinner__MMColParam = ""
If (Request.QueryString("searcher") <> "") Then 
  Recordthewinner__MMColParam = Request.QueryString("searcher")
End If
%>
<%
Dim Recordthewinner
Dim Recordthewinner_cmd
Dim Recordthewinner_numRows
Dim Recordthewinner_cmd_query
'TOM Recordset
Set Recordthewinner_cmd = Server.CreateObject ("ADODB.Command")
Recordthewinner_cmd.ActiveConnection = MM_conconlawlaw_STRING
Recordthewinner_cmd_query = "SELECT  thenews.newspk,  thenews.newsdate,  thenews.newspagetitle,  thenews.newscontent,  thenews.businessname,  contrylist.countrypk,  contrylist.countryshort,  contrylist.countrylong,  thenews.businessurl,  thenews.newkeywords,  thenews.newsmetadis  FROM  thenews  Inner Join contrylist ON contrylist.countryshort = thenews.newsmetadis  WHERE  thenews.newsdate like '%"&Recordthewinner__MMColParam&"%' OR  thenews.newspagetitle LIKE  '%"&Recordthewinner__MMColParam&"%'  ORDER BY  thenews.newsdate DESC" 
Recordthewinner_cmd.CommandText = Recordthewinner_cmd_query
Recordthewinner_cmd.Prepared = true

Set Recordthewinner = Recordthewinner_cmd.Execute
Recordthewinner_numRows = 0
%>

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33750027
so did u try using date_format?

is it possible for you to see the generated string and post it here?

"SELECT  thenews.newspk,  thenews.newsdate,  thenews.newspagetitle,  thenews.newscontent,  thenews.businessname,  contrylist.countrypk,  contrylist.countryshort,  contrylist.countrylong,  thenews.businessurl,  thenews.newkeywords,  thenews.newsmetadis  FROM  thenews  Inner Join contrylist ON contrylist.countryshort = thenews.newsmetadis  WHERE  thenews.newsdate like '%"&Recordthewinner__MMColParam&"%' OR  thenews.newspagetitle LIKE  '%"&Recordthewinner__MMColParam&"%'  ORDER BY  thenews.newsdate DESC"
0
 
LVL 1

Author Comment

by:thatelvis
ID: 33752138
This is the generated string  I place the 2010-09-20 into my text area and click the go button

http://www.litigationfunder.com/z.asp?searcher=2010-09-20&Go=Search

the above was also using the LIKE

WHERE thenews.newsdate LIKE  '%"&Recordwinner__MMColParam&"%'

if I replace the like for = and do the same actions no record is returned. I did not use the date_format as I do not know where to apply it.


thanks for yuor help


k


0
 
LVL 65

Accepted Solution

by:
rockiroads earned 300 total points
ID: 33755405
I don't know whats going on with your setup. You say a exact date match works?

I am wondering how based on your where clause

WHERE  thenews.newsdate like '%"&Recordthewinner__MMColParam&"%' OR  thenews.newspagetitle LIKE  '%"&Recordthewinner__MMColParam&"%'

Checking both newsdate and newspagetitle


Dont know if this makes a difference but remove the -

MyDate = Replace(Recordthewinner__MMColParam, "-", "")

so in your select query where clase
WHERE  date_format(thenews.newsdate,'%Y%m%d') <= '" mydate"'
0
 
LVL 1

Author Closing Comment

by:thatelvis
ID: 33797381
thank you
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 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