selecting first 7 days from database
-
hi, i got a database table that have 4 column id,subject,contents,date the date column is varchar datatype and its the data is DateTime.Now.ToString() How do i select 7 days record from the table.. Select * from announcement WHERE ???? thanks
-
hi, i got a database table that have 4 column id,subject,contents,date the date column is varchar datatype and its the data is DateTime.Now.ToString() How do i select 7 days record from the table.. Select * from announcement WHERE ???? thanks
if your date and datetime information were stored as such (and not as a string), then you could simply use SQL stuff such as "ORDER BY" and "TOP N" since databases that support date types also know how to sort them chronologically. If you insist on storing datetime information as strings, you will: - be subject to problems due to different regional settings (which day/month/year is 02/03/04 in each country on Earth?) - need conversion functions everywhere in your SQL statements. You might want to read this little article[^]. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
[The QA section does it automatically now, I hope we soon get it on regular forums as well]
-
hi, i got a database table that have 4 column id,subject,contents,date the date column is varchar datatype and its the data is DateTime.Now.ToString() How do i select 7 days record from the table.. Select * from announcement WHERE ???? thanks
As Luc has said you need to change your data type to datetime, leaving it as varchar is a disaster. Think about this, to get the top 7 records you could do the following
SELECT TOP 2 Dt FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt FROM Period ) X ORDER BY Dt descThat will have to convert EVERY datestring in your table, creating an index is a waste of time and all the regional problem Luc mentioned will be visited upon you. FIX YOUR DATA NOW!Never underestimate the power of human stupidity RAH
-
hi, i got a database table that have 4 column id,subject,contents,date the date column is varchar datatype and its the data is DateTime.Now.ToString() How do i select 7 days record from the table.. Select * from announcement WHERE ???? thanks
What do you mean by "select 7 days record from the table"? Do you mean that you want all the records from the table for the last 7 days? Or do you mean that you want the top 7 records from the table irrespective of date? Or do you mean that you want all the records for the most recent 7 days which are actually in the database (which chronologically may range over more than 7 days if for example you have no entries at the weekend)? Each one of these will require a different query. None of which are particularly straightforward given the way in which the dates are stored in the table.
-
As Luc has said you need to change your data type to datetime, leaving it as varchar is a disaster. Think about this, to get the top 7 records you could do the following
SELECT TOP 2 Dt FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt FROM Period ) X ORDER BY Dt descThat will have to convert EVERY datestring in your table, creating an index is a waste of time and all the regional problem Luc mentioned will be visited upon you. FIX YOUR DATA NOW!Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
to get the top 7 records you could do the following SELECT TOP 2 Dt FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt FROM Period ) X ORDER BY Dt desc
Call me picky, but won't this give the top 2 records? Or am I missing something here?
-
Mycroft Holmes wrote:
to get the top 7 records you could do the following SELECT TOP 2 Dt FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt FROM Period ) X ORDER BY Dt desc
Call me picky, but won't this give the top 2 records? Or am I missing something here?
I only had 3 records in the test table I wrote this against :-O . If the OP can't work out the difference then I probably can't help him. And yep I call you picky :rolleyes:
Never underestimate the power of human stupidity RAH
-
I only had 3 records in the test table I wrote this against :-O . If the OP can't work out the difference then I probably can't help him. And yep I call you picky :rolleyes:
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
And yep I call you picky
Every forum needs at least one anal-retentive pedant to keep things in line. I do my best.
-
Mycroft Holmes wrote:
And yep I call you picky
Every forum needs at least one anal-retentive pedant to keep things in line. I do my best.
David I doubt you are the only one - AR is a required trait for some aspects of development so you are welcome.
Never underestimate the power of human stupidity RAH