CODEWITHSUNDEEP
sqlsql-serverasp-classicdate-format
Shyju
Shyju

Reputation:

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?

Upvotes: 2

Views: 14267

Answers (6)

AnthonyWJones
AnthonyWJones

Reputation: 189457

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415765

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

Upvotes: 0

balexandre
balexandre

Reputation: 75083

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

Upvotes: 1

Lieutenant Frost
Lieutenant Frost

Reputation: 541

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415765

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

Upvotes: 0

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

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.

Upvotes: 0

Related Questions