Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Datetime Query

  1. #1
    Join Date
    May 2011
    Posts
    8

    Unanswered: Datetime Query

    I've problem with this

    OleDbCommand cmd = new OleDbCommand("select * from mydb where "+DateTime.Now.ToString("dd/MM/yyyy")+"> job_deadline", con);

    job_deadline is datetime field in db.

    I want to check data in my db if the date is passed but i'll always get syntax error what am i missing or what is wrong here? please help.
    Last edited by xack; 05-02-11 at 11:36. Reason: Misspell

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What language are you using here? It doesn't appear to be Access VBA or even SQL normally used in Access databases.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2011
    Posts
    8
    asp.net C# its just one line of it and problem should be sql string, its giving syntax error missing parameter.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Usually you cannot pass a date/time string formatted as 'dd/mm/yyyy'. To what data provider do you connect (i.e. what's the contents of the con parameter), Access, SQL Server, Excel?
    Have a nice day!

  5. #5
    Join Date
    May 2011
    Posts
    8
    Access database

    protected void JobFiltre2_CheckedChanged(object sender, EventArgs e)
    {

    if (JobFiltreUnDone.Checked == true)
    {
    OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\acer\documents\visual studio 2010\Projects\WebApplication2\WebApplication2\app_ data\Database111.accdb;Persist Security Info=True");
    OleDbCommand cmd = new OleDbCommand("select * from mydb where #" + DateTime.Now.ToString("dd/MM/yyyy") + "# > job_deadline", con);
    OleDbDataAdapter da = new OleDbDataAdapter](cmd);
    DataTable dt = new DataTable();
    con.Open();
    da.Fill(dt);
    con.Close();

    GridView1.DataSource = dt;
    GridView1.DataBind();
    lbl_JobFiltreDone.Text = "Yapılanlar";
    JobEntry.Visible = false;
    JobAdd.Visible = false;



    }
    Last edited by xack; 05-03-11 at 06:26. Reason: Added Code

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't do vb, .net, ole, or any of that stuff

    but i do know access sql, and date strings require octothorps

    try this --

    OleDbCommand cmd = new OleDbCommand("select * from mydb where #"+DateTime.Now.ToString("dd/MM/yyyy")+"# > job_deadline", con);
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by xack View Post
    asp.net C# its just one line of it and problem should be sql string, its giving syntax error missing parameter.
    That's what I thought! You really need to post this in a language appropriate forum, even if you are connecting to an Access back end, which is still not clear form your posts.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    r937 is right, plus the format should be mm/dd/yyyy, whatever the Windows international settings are.
    Have a nice day!

  9. #9
    Join Date
    May 2011
    Posts
    8
    This is the error => Syntax error in date in query expression '#03.05.2011# > job_deadlin'.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    '#mm.dd.yyyy#' is not the same as #mm/dd/yyyy#
    Have a nice day!

  11. #11
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by Sinndho View Post
    '#mm.dd.yyyy#' is not the same as #mm/dd/yyyy#
    Nope didnt work,

    also this didnt work => ("select * from mydb where #"+ DateTime.Today.Date + "# > job_deadline", con);

    Syntax error in date in query expression '#03.05.2011 00:00:00# > job_deadlin'.

    In these error sentences job_deadline becomes job_deadlin with missing e is this a problem? I tryed so many things and i apologize if it isn't appropriate forum section to ask this problem...

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so just guessing you got a syntax error, not an SQL error?

    if so what do you think may be causing the error?

    bear in mind JET SQL expects the date to encapuslated by the hash # symbols as Rudy says in post #6.

    if you are still struggling look carefully at what rudy suggested
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by healdem View Post
    so just guessing you got a syntax error, not an SQL error?

    if so what do you think may be causing the error?

    bear in mind JET SQL expects the date to encapuslated by the hash # symbols as Rudy says in post #6.

    if you are still struggling look carefully at what rudy suggested
    Well i'm new in this and What am i trying to do is check job_deadline < Today's date ,i'm using vs2010 .net framework 4.0 asp.net,database is access, i'm sending command through oledbcommmand and problem should be the query because i used same codes for another process with just different query "OleDbCommand cmd = new OleDbCommand("select * from mydb where job_complete=true", con);" it gets all the completed jobs to my gridview. This is a simple ToDo list and i'm trying to do add some filter, i did one for completed jobs now i'm trying to filter all the deadline passed jobs...

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xack View Post
    What am i trying to do is check job_deadline < Today's date
    if that's all you want to do, then you should use this --

    select * from mydb where job_deadline < DATE()

    there's no need to feed in a string containing today's date, because access has a built-in date function!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    if that's all you want to do, then you should use this --

    select * from mydb where job_deadline < DATE()

    there's no need to feed in a string containing today's date, because access has a built-in date function!!

    Thats it thnx a lot Now i need to eleminate if job_complete=true , i need to list only undone jobs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •