Comparing dates in SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing dates in SQL Server

Author

Topic

namitjung
Starting Member

10 Posts

Posted - 2005-09-03 : 06:46:07

Hi all,

I am trying to compare a date with following sqlstatement

sql="select * from one where signdate <= '"&date()&"'"

This sql statement runs fine.But sql compares it as a text value.But it should be compare as a date value. I tried hash sign like this but it didn't work

sql="select * from one where signdate <= #'"&date()&"'#"

Can any one solve my problem
Thanx in advance

Kristen
Test

22859 Posts

Posted - 2005-09-03 : 07:09:20

Hi namitjung, Welcome to SQL Team!

What datatype is your "signdate" column? It needs to be a DATETIME for a true date comparison.

Using the current date from your application is a bit fraught - it will have to be formatted correctly for SQL Server to interpret it correctly - the only reliable formats are
"yyyymmdd hh:mm:ss.sss" or "yyyy-mm-ddThh:mm:ss.sss"

(Other formats may work, but will be dependant on the Locale setting of the machine SQL Server is running on, which could, of course, change in the future. You can also "tell" SQL Server the format you are using:

SET DATEFORMAT MDY
SELECT * FROM one WHERE signdate <= '09-03-05'

Your other option, if you just want "now", is to use SQL Server's current date/time. Beware if this might have issues with Time Zone etc. The sytnax for that is:

SELECT * FROM one WHERE signdate <= GetDate()

Note that the SQL Server function "GetDate()" includes TIME as well as DATE - there are ways to remove the TIME part if that will interfere with your usage.

(By the by, you should not use "SELECT *", you should always list the specific columns your application needs - that way if additional columns are added in the future they will not interfer with your application - for example, I've seen situations where several large TEXT columns were added to a table, and all the SELECT * in the application [which didn't need the new columns] then had terrible performance!)

Kristen

namitjung
Starting Member

10 Posts

Posted - 2005-09-03 : 07:21:27

Thank you for your reply

I am using Microsoft sql server 2000 and my data type field is datetime.all values are inserted in a format like yyyy-mm-dd hh:mm:ss.sss.

As i already said that i have no problem with running this query

<%
newdate=now()-10
sql="select id from one where signdate <="&newdate&""
%>
This query runs fine from an asp page. But my question is,Will sql compare this date i.e. (newdate) as a text or as a date value.And if it compares as a text then how can i compare this value as a date.

I was told to use # value for that. But it didn't work.

Thank You

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-09-03 : 07:51:50

You do not use hash or pound sign with SQL server dates, it is specific to the MS Access only.

Try this in your ASP page, SQL will compare it as a date value.
sql="select id from one where signdate <='"& newdate & "'"

Kristen
Test

22859 Posts

Posted - 2005-09-03 : 08:54:54

If you have

select id from one where SomeColumnInDateTimeDataType <= '2005-09-03'

i.e. you are comparing a DateTime datatype "object" with a "text" date, then the text will first be converted to a DateTime datatype first, and then the comparison will be made using DateTime - which is what you want.

However, if the "text" date is not presented in a format that SQL Server can interpret an error will be raised.

Also, text dates presented as, for example, "01-02-03" can be interpreted by SQL in lots of different formats (1st Feb 2003, 2nd Jan 2003, 3rd Feb 2001 and so on), so the formatting it crucial.

You mention that your date presents as "yyyy-mm-dd hh:mm:ss.sss" and as I said above I believe that there must be no hyphens (ISO format), or there must be a "T" separator (ODBC format) for total unambiguity.

However, this is from memory, so at my age I could easily have mis-remembered somethng

I was just anxious that the formatting presented by the date() function you are using may not be within your control - e.g. it may use the Locale settings of the computer on which it is running - and that could be a problem - I think it would be better to use formatting that is under your control, or [if you just want "Now"] to use SQL Servers function GetDate() [as I described above] which will return a native DateTime datatype.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources