We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

sql query to return todays data

195 Views
Last Modified: 2010-03-19
hello there,

i have this query which i want to return only todays data.i tryied
WHERE     (dbo.Bookht_Port_History.StartDate  = GETDATE()) but does not return anything.please help.

SELECT     dbo.Bookht_Port_History.CustomerNumber, dbo.Bookht_Port_History.StartDate, dbo.Bookht_Port_History.Comment, dbo.bookht_TelCenter.Name
FROM         dbo.Bookht_Port_History INNER JOIN
                      dbo.Bookht_Terminal_Ports ON dbo.Bookht_Port_History.TERMINAL_PORT_ID = dbo.Bookht_Terminal_Ports.TERMINAL_PORT_ID INNER JOIN
                      dbo.Bookht_Terminals ON dbo.Bookht_Terminal_Ports.TERMINAL_ID = dbo.Bookht_Terminals.TERMINAL_ID INNER JOIN
                      dbo.bookht_TelCenter ON dbo.Bookht_Terminals.CO_ID = dbo.bookht_TelCenter.pkid
WHERE     (dbo.Bookht_Port_History.StartDate  = GETDATE())
ORDER BY dbo.bookht_TelCenter.pkid, dbo.Bookht_Port_History.StartDate, dbo.Bookht_Port_History.Comment
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
replace:
WHERE     (dbo.Bookht_Port_History.StartDate  = GETDATE())

by:

WHERE dbo.Bookht_Port_History.StartDate  >= CONVERT(datetime, convert(varchar(10), GETDATE() , 120), 120)
  AND dbo.Bookht_Port_History.StartDate  < dateadd(day, 1, CONVERT(datetime, convert(varchar(10), GETDATE() , 120), 120))
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jai STech Arch

Commented:
use this
WHERE     (convert(varchar(10),dbo.Bookht_Port_History.StartDate,120)  = convert(varchar(10),getdate(),120))

instead of
WHERE     (dbo.Bookht_Port_History.StartDate  = GETDATE())

Author

Commented:
my column has date time.
somebody please explain to me why only using getdate() does not work in my case and why is there so many solutions to one question. i mean each Experts have given me different answers.appreciate your help
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
just an important note:
while the above suggestions (except mine) are eventually shorter in code, as they apply a function on the column, they will make any index on that column unusable/skipped...
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jai STech Arch
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:

angellll

thanks for your comment.can you please explain what your query does. or refer me to some site.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
@jaiganeshsrinivasan:
yes, it strips off the time for the comparison.
but, as explained, it will also result in skipping an index on the column StartDate

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.