Solved

showing date and what happened before that date

Posted on 2010-09-22
15
277 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 44
export sql results to csv 6 36
Query Syntax 17 36
SQL Syntax: How to force case sensitive query? 2 30
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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