sql date question
-
If you wanted to select all the records where join on a date time field.
And you only want todays records.
-
SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.Date = Table2.Date
WHERE CONVERT(varchar(10), Table1.Date, 101) = '03/24/2006'
-
SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.Date = Table2.Date
AND Table1.Date >= convert(datetime,'20060324') /*always pass universal date, with function on the right allows indexes to be used*/
AND Table1.date < dateadd(d,1,convert(datetime,'20060324'))
-
I want this to run every day looking for records with today's date in it.
Table xyz
datefield char(10)
select * from xyz where datefield = ??????
something like informix I could have
select * from xyz where datefield = today
what would I use in SQL server to replace today?
-
Just note that in sql dates always have assocated times. Getdate() returns date and time, not just date. There's no built in function that only returns date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Right, but using getdate() in combination with month, day, year, or even convert will get you a date only.
-
Donald,
If datefield in table xyz is defined as char(10), it's not a datetime column. You'll have to convert today's date acquired getdate() to character to do the comparison. While you're looking up getdate() in BOL, look up convert.
Greg
Greg
-
Try this, Donald...
select * from xyz where datefield >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND datefield < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)+1
The DateAdd/DateDiff thing strips the time from the date without the expense of a character conversion and without performing a calculation on the datefield... that way, an index will still be used if there is one on datefield.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply