SQL date format issue in select query

I have an ASP page which will fetch records from a SQL server DB table. The table "order_master" has a field called order_date. I want to frame a select query to fetch order date > a date entered by user(ex : 07/01/2008)

I tried with convert and cast, but both are not working. The sample data in order_date column is 4/10/2008 8:27:41 PM. Actually, I dont know what type it is (varchar/datetime).

Is there any way to do that?

From stackoverflow $pageUsers[$entry.posts[0].author].name

• You could create a stored procedure like this

  CREATE PROCEDURE GetOrders
      @OrderDate DATETIME
  AS
  SELECT
      *
  FROM order_master
  WHERE Order_Date > @OrderDate
  
  GO
  

  Then you can just convert the users input to a date before calling the stored procedure via your ASP code.

  Edit

  I just noticed the remark about the column type, you can run this command

  sp_help order_master
  

  to get column information to find the data type of order_date.

  From Mitchel Sellers

• Have you tried CONVERT()'ing both values to a datetime type?

  From Joel Coehoorn

• I'd check to make sure that the SQL datatype is a DateTime or SmallDateTime first, then I'd check to make sure that you're passing in a Date/DateTime value from the page.

  If those are both correct, then you'd probably be better off following Joel's advice and explicitly convert both values to dates before trying the comparison. Also, check the precision of the time values that you're looking at; it seems obvious, but 1/1/2008 12:00:00.001 AM will not be equal to 1/1/2008 12:00:00.000 AM. Yes, I am speaking from experience. :P

  From Lieutenant Frost

• the 07/01/2008 date is the British/French annotation, so all you need to do is:

  SELECT myColumn FROM myTable WHERE myDateField >= convert(datetime, '07/01/2008 00:00:00', 103)

  this code will get all rows where myDateField has the date 7th of January 2008, since 00:00:00 (hh:mm:ss) so, the first second on that day... in simple words, the entire day.

  for more info, check Books online on MSDN

  From balexandre

• Remember that when comparing dates, 4/10/2008 8:27:41 PM is not equal to 4/10/2008. SQL Server will interpret 4/10/2008 to mean 4/10/2008 12:00:00 AM, and do an exact comparison down to the second. Therefore 4/10/2008 is LESS THAN 4/10/2008 8:27:41 PM

  From Joel Coehoorn

• I shall fileter before framing the query.but how to frame the query ? thats what i am confused

  From $pageUsers[$post.author].name

• You state you don't know the field type. That would be the first problem to solve, find out. You can do that with:-

  SELECT DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Order_Master' AND
      COLUMN_NAME = 'Order_Date'
  

  If its not one of the datetime types it should be converted, if that isn't your responsibility then get on to someone who does have the responsibility.

  The fact that you are concerned about the 'format' of the date indicates that you may be building the SQL using concatenation. If so stop doing that. Use a command with a parameter and pass in the date as date type.

  Now your issue is one of how the date is entered at the client end and getting it into an unambigous format that can be parsed as a date in the ASP code.

  If that is not something you have solved add a comment to this answer and I'll expand this answer.

  From AnthonyWJones

• helow i am new here i have a table name Orde in one of the culome i have date time that the order created i want to select all the order in every mont and year

  how do i do it

  From $pageUsers[$post.author].name

• hi, take date format in C#. I have some problem so I can't solve problem.

  code: private void dtTmeStartDate_ValueChanged(object sender, EventArgs e) { TxtClintName.Text = String.Format("{0:mm/dd/yyyy}",dtTmeStartDate.Text); }

  output should be come : 05/25/2008

  From $pageUsers[$post.author].name