SQL Newbie - date ?
-
I've got a table for contracts and when input a field dateinput as a default as getdate. What i'm having trouble with is query to show all contracts input today. Can anyone help.
-
Select * from dbo.Contracts where DateContrat >=dateadd(D, 0, datediff(D, 0, GetDate())) and < dateadd(D, 1, datediff(D, 0, GetDate()))
-
Get error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '<'.
-
How about this WHERE clause:
where ContractDate >= cast(floor(cast(getdate() as float)) as datetime)
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
That works thanks still getting used to sql from access
-
and DateContract < dateadd(D, 1, datediff(D, 0, GetDate()))
Also keep in mind that my version will allow for an seek to be made on any avalaible index for that column, while the convert version will force a scan (if made on the column part of the expression, which will always be slower).
Also Phil's version assumes that no future dates are entered in the column.
-
Hmmm - my assumption is surely valid? Assuming Doctor Who's absence, how can 'dateinput' be a future date?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I've seen this on my system... Hard too believe but sometimes the users require that (however strange that may be).
-
WHERE ContractDate = CONVERT(varchar,GETDATE(),112)
Andy
-
Where convert(varchar(50),ContractDate,101) = convert(varchar(50),getdate(),101)
-
This (convert(varchar(50),ContractDate,101)) will always force a scan to be executed. You'll kill any performance in that query right from the bat.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply