Using a DateTime field in a select statement

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

Transact-SQL (2000)

Using a DateTime field in a select statement

Author

Topic

neostar
Starting Member

18 Posts

Posted - 2006-01-31 : 03:42:31

I have to say I find the whole date system very confusing in SQL.

I basically have a very simple table, where by one of the field is a Datetime datatype and has the GetDate() function asigned to its defualt value so that when ever a new record is added it has the current date and time assigned to it.

The problem I am having is actually querying data against that field, no matter what format I enter a date it returns no results. How do I enter the query string in a format it will accept without converting it to a string etc?

Here is what I have so far

SELECT ID, SurName, AddDate
FROM dbo.DateTest
WHERE (AddDate = '31/01/2006')

Thanks

Dan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 03:47:50

As your AddDate field contains Date & time, your where statement AddDate = '31/01/2006' effectively constraint the AddDate to '2006-01-31 00:00:00' (0 Hour 0 Minutes 0 secods etc). That's why no result is return.
Change your code to below

SELECT ID, SurName, AddDate
FROM dbo.DateTest
WHERE AddDate >= '2006-01-31'
and   AddDate < '2006-02-01'

SELECT ID, SurName, AddDate
FROM dbo.DateTest
WHERE AddDate >= '2006-01-31'
and   AddDate < dateadd(day, 1, '2006-01-31')

If you are using a variable change the red '2006-01-31' to your variable

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 04:07:05

And refer this also
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

neostar
Starting Member

18 Posts

Posted - 2006-01-31 : 04:23:17

I understand now, and that worked spot on thank you!

Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources