SQL date format issue in select query

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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