How would i do this select?

.NET
1

Hey,

I want to do a select where the date = the current month and year (MMMM YYYY)

My current select is as so:-


SELECT tbl_articles.article_id, tbl_articles.name, tbl_articles.info, " +
                "tbl_articles.date_added, tbl_articles.cat_id, tbl_article_cat.cat_id, tbl_article_cat.cat_name " +
            "FROM tbl_articles INNER JOIN tbl_article_cat on tbl_articles.cat_id = tbl_article_cat.cat_id WHERE tbl_article_cat.cat_name = ?????"

As you can see the ??? is where i get stuck. How would i select the current month and year? Lets say for example the result of the select for this month should be:-


SELECT tbl_articles.article_id, tbl_articles.name, tbl_articles.info, " +
                "tbl_articles.date_added, tbl_articles.cat_id, tbl_article_cat.cat_id, tbl_article_cat.cat_name " +
            "FROM tbl_articles INNER JOIN tbl_article_cat on tbl_articles.cat_id = tbl_article_cat.cat_id WHERE tbl_article_cat.cat_name = 'February 2010'"

And then in March it should change accordingly, any ideas who i can do this?

Thanks

2

var dateAndMonth = DateTime.Today.ToString(“MMMM YYYY”) ?

var query = "SELECT tbl_articles.article_id, tbl_articles.name, tbl_articles.info, tbl_articles.date_added, tbl_articles.cat_id, tbl_article_cat.cat_id, tbl_article_cat.cat_name
FROM tbl_articles INNER JOIN tbl_article_cat on tbl_articles.cat_id = tbl_article_cat.cat_id WHERE tbl_article_cat.cat_name = @MonthAndYear";

using(var connection = …)
using(var command = …)
{
command.Parameters.Add(“@MonthAndYear”, dateAndMonth);
}


if you are using plain ADO.NET… are you?

3

Hey,

Thanks alot for your response. I currently have this code:-


            var dateAndMonth = DateTime.Today.ToString("MMMM YYYY");

            string sqlStrBlog = "SELECT tbl_articles.article_id, tbl_articles.name, tbl_articles.info, tbl_articles.date_added, " +
                "tbl_articles.cat_id, tbl_article_cat.cat_id, tbl_article_cat.cat_name " +
                "FROM tbl_articles INNER JOIN tbl_article_cat on tbl_articles.cat_id = tbl_article_cat.cat_id " +
                "WHERE tbl_article_cat.cat_name = @MonthAndYear";

            SqlCommand dbCommandBlog = new SqlCommand(sqlStrBlog, dbConn);

            dbCommandBlog.Parameters.Add("@MonthAndYear", SqlDbType.Char).Value = dateAndMonth;

            SqlDataReader dbReaderBlog = dbCommandBlog.ExecuteReader();

            while (dbReaderBlog.Read())
            {
                //content here
            }

            dbReader.Close();

But this not work, i don’t get any errors but it does not show anything when it should…

Any ideas?

4

SqlDbType.VarChar

5

have you run the statement in managent studio to see if it returns anything?

Also… you should call Dispose() on those Adot.net types.

using(var dbcon = new SqlConnection(“…”))
{
using(var command = new SqlCommand(“…”, dbcon))
{
using(var reader = command.ExecuteReader())
{
while(reader.Read())
{
// do Stuff
}
} // reader is closed and disposed
} // command is disposed
} // connection is closed and disposed

6

Hey,

Thanks, but that didnt work either. I tried a different approach:-


        DateTime dt = new DateTime();
        dt = DateTime.Now;
.
.
.
.
            dbCommandBlog.Parameters.Add("@MonthAndYear", SqlDbType.VarChar).Value = dt.ToString("MMMM yyyy");

And it worked perfectly :slight_smile:

Thanks again

7

Are you using varchar as the database type for dates? Is there a reason you’re not using datetime? That way they are date sortable and not alphabetically sortable.

Nevermind me if I’m reading this wrong.

8

If I were to select against a month and a year I’d do something like this (needs to be parameterized):

... WHERE db.ArticleDate.Year = DateTime.Now.Year 
AND db.ArticleDate.Month = DateTime.Now.Month ...

Don’t trust my ADO skills though, I’ve abandoned everything for Linq. It makes the grass grow greener and the sun shine brighter.