none
How to use DateTime datatype in SQL Server query??????

    Question

  •  

    I've looked around quite a bit for this but couldn't find a definitive answer. My problem is (seemingly) very simple.

     

    I have a table with a column whose datatype is 'datetime'. I do something like this:

     

    Code Snippet

    DateTime date = dtp.Value.Date;

    command = "SELECT * FROM MyTable WHERE MyColumn LIKE '%" + date + "%'";

     

     

    The above code, when executed, does not return any rows. I have tried multiple variations of the above SQL statement.

     

    All I want to do is get all rows with a specific DATE value, I do not care about the TIME portion of the datetime value. The date in my table is stored like this: 1/20/2008 3:30:07 AM.

     

    So, if the above datetime object is a value store din my table, I want to be able to do the following:

     

    Code Snippet
    command = "SELECT * FROM MyTable WHERE MyColumn LIKE '%1/20/2008%'"

     

     

    Any feedback would be greatly appreciated!

     

    Patrick

     

     

    Friday, January 25, 2008 10:04 PM

Answers

All replies

  • I think you should use:

    SELECT * FROM MyTable WHERE MyColumn >= '1/20/2008' AND MyColumn < '1/21/2008'

    with only date part supplied, I think the system assumes 0:00:00 for the time part.
    Friday, January 25, 2008 10:09 PM
  •  

    I've tried that with no luck.... any other suggestions?

     

    It shouldn't be this difficult to create a query that returns all records from a certain date.

    Friday, January 25, 2008 10:13 PM
  • The link below contains some good information about working with DateTime in SQL.  Perhaps it will help.

     

    http://www.databasejournal.com/features/mssql/article.php/2209321

    Friday, January 25, 2008 10:27 PM
  • Fabulous, that worked well for me. I ended up using the DATEPART() function.

     

    Saturday, January 26, 2008 12:51 AM
  • You can also try this:

    DateTime date = dtp.Value.Date;

    command = "SELECT * FROM MyTable WHERE MyColumn >= @DateFrom AND MyColumn < @DateTo";

     

    SqlCommand cmd = new SqlCommand(command, "...connection string...");

    cmd.Parameters.Add("@DateFrom", SqlDbType.Smalldatetime, 4).Value = date;

    cmd.Parameters.Add("@DateTo", SqlDbType.Smalldatetime, 4).Value = date.AddDays(1);

     

    This code solves several problems like:
    - you don't have to worry about date format of current user

    - you solve the problem of sql injection

     

    Also there are several ways to get all rows for one day from sql server such as BETWEEN, DATEPART, >= & < , but the last one is accurate and possibly fastest.

    Saturday, January 26, 2008 9:32 AM
  • Thanks for your response.

     

    All of the computers that will be logging data will be doing so from a .NET application... which means that they will all be using the 'DateTime.Now' property for reporting data to the SQL server... so I don't necessarily need to worry about date and time format, right?? They will be the same, right??

     

    Also, I've tried the '>= & <' method and it also did not return any rows.

    Monday, January 28, 2008 8:35 AM
  •  remarkpk wrote:

    Thanks for your response.

     

    All of the computers that will be logging data will be doing so from a .NET application... which means that they will all be using the 'DateTime.Now' property for reporting data to the SQL server... so I don't necessarily need to worry about date and time format, right?? They will be the same, right??

     

    Also, I've tried the '>= & <' method and it also did not return any rows.

     

    You don't have to worry about datetime format only and only if you work in typed manner, which means you don't send the datetime value as string, but instead use sql parameters and work only with datetime values as was in my example. If you send this values as strings, than you do have to wory about format. Did you use DateTime.Now has nothing to do with assurance that everything about datetime format will be correct. Query will not return results if value in dtp.Value.Date have invalid value. You need to assure that this datetime value is filled and it contains only date part. You can test the query using DateTime.Today instead of this value just to check the query. Also start the SQL Profiler to see what is going on on the server side, and what is the query that is executed.

    Monday, January 28, 2008 9:10 AM
  • did you try it in query analzyer to make sure there's data between the dates? how about the following? if there's still no row returned from it, please use select * from mytable to check what datetime value those rows in the mycolumn(assume it's type datetime) has?

    SELECT * FROM MyTable WHERE MyColumn >= '1/1/1999' AND MyColumn < '1/1/2009'


     remarkpk wrote:

    Thanks for your response.

     

    All of the computers that will be logging data will be doing so from a .NET application... which means that they will all be using the 'DateTime.Now' property for reporting data to the SQL server... so I don't necessarily need to worry about date and time format, right?? They will be the same, right??

     

    Also, I've tried the '>= & <' method and it also did not return any rows.

    Monday, January 28, 2008 4:31 PM
  • I am developing a Online Lottery Project

    here is near about 500 Draw in a day, every day this draws open for sale (such as 09:00:00 AM, 09:05:00 AM, 09:10:00 AM)

    All client are not Login at same time, they login various time, when they login they should be able show those draw which are available from his login time & and previous draw or drawtimes should be hidden, how to configure this Datetime quary in SQL or Access ?

    Clientside database is access and server side is sql, 

    I tried to add time without date (hh:mm:ss), but SQL cant recognise it and another problem is All draws are available from begining at sametime in next day, thats why i didnot mentioned date, but this way all times are goes to for 31.12.1899, I tried to edit manually every day(in 500 data row)

    is there any solution to automatically date and time will fixed for next day in Access & SQL ?

    please help me


    Sunday, April 29, 2012 6:49 PM