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

x
?
Solved

showing date and what happened before that date

Posted on 2010-09-22
15
Medium Priority
?
285 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Assisted Solution

by:wellhole
wellhole earned 800 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 1200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

926 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