Solved

showing date and what happened before that date

Posted on 2010-09-22
15
275 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

10 Experts available now in Live!

Get 1:1 Help Now