showing date and what happened before that date

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
LVL 1
thatelvisAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rockiroadsConnect With a Mentor Commented:
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
 
wellholeCommented:
<= '" & Recordwinner_MMColParam & "'"

will create

<= '2010-09-20'

which will work whether your date is a string or a date.
0
 
thatelvisAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rockiroadsCommented:
is it definitely in that format? perhaps wrap it with date_format just to make sure
0
 
wellholeConnect With a Mentor Commented:
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
 
thatelvisAuthor Commented:
i have tried that but i still see all records

cheers

k
0
 
wellholeCommented:
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
 
rockiroadsCommented:
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
 
thatelvisAuthor Commented:
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
 
wellholeCommented:
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
 
thatelvisAuthor Commented:
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
 
thatelvisAuthor Commented:
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
 
rockiroadsCommented:
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
 
thatelvisAuthor Commented:
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
 
thatelvisAuthor Commented:
thank you
0
All Courses

From novice to tech pro — start learning today.