MS SQL 2k
I hae a simple search

SELECT *FROM dbo.tbljobs WHERE dtdateexpires >= GETDATE()

however its not including the records where dtdateexpires is the same as the current date.. any ideas ?

Hi Adam
getdate refers to both the date and time of a datetime field so if you don't include the time when you insert a date dql adds 00:00-000 (I think), so you won't ever get the data for today as midnight is before the getdate() function results. Get what I mean? If you don't specify the time portion ever use getdate()-1, quick and dirty fix, if you do you'll have to work it out another way by building your own date using datepart

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

ahh that would explain it cheers for the heads up

one quick question if im stripping out the time info from the expirey field and compared it to a getdate() would the time factor still come into play ?

e.g.

SELECT CAST(CONVERT(VARCHAR,dateField,101) AS DATETIME) AS dateField WHERE dateField &gt;=GETDATE()

?

Hi Adam

Sorry for not getting back to you before, my laptop died and needed a few bits to fix.

Anyway back to your question, I believe it does because datetime in SQL always has both date and time associated with the value.

I ran this:
declare @date datetime

set @date = 'Jun 26 2005 10:31PM'

SELECT CAST(CONVERT(VARCHAR,@date,101) AS DATETIME) AS dateField
WHERE @date &gt;=GETDATE()

And got this:
dateField
------------------------------------------------------
2005-06-26 00:00:00.000

(1 row(s) affected)

So looks like the time is still an issue as you're using getdate() which will contain the time as well as the date. Any date entered into SQL without a time will have the 00:00:00.000 time value appended.

So why not try this:

declare @date datetime

set @date = 'Jun 25 2005 10:31PM'

SELECT CAST(CONVERT(VARCHAR,@date,101) AS DATETIME) AS dateField
WHERE @date &gt;= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)

Edited by - Digga the Wolf on 27 Jun 2005 00:48:29