[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query to return todays data

Posted on 2007-10-09
10
Medium Priority
?
164 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
0
Comment
Question by:zolf
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20046619
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))
0
 
LVL 10

Assisted Solution

by:answer_me
answer_me earned 200 total points
ID: 20046624
Use this

WHERE (Convert(varchar,dbo.Bookht_Port_History.StartDate,112)  = Convert(varchar,GETDATE(),112) )
0
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 200 total points
ID: 20046629
Check dbo.Bookht_Port_History.StartDate for only date (not time)

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     (year(dbo.Bookht_Port_History.StartDate)  = year(GETDATE())) and
(month(dbo.Bookht_Port_History.StartDate)  = month(GETDATE())) and (day(dbo.Bookht_Port_History.StartDate)  = day(GETDATE()))
ORDER BY dbo.bookht_TelCenter.pkid, dbo.Bookht_Port_History.StartDate, dbo.Bookht_Port_History.Comment

HuyBD
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 14

Expert Comment

by:Jai S
ID: 20046647
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())
0
 

Author Comment

by:zolf
ID: 20046665
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20046666
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...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 20046672
>why only using getdate() does not work
because getdate() also returns date + time.

so, if your column has store, for example:
2007-10-10 07:10:32

and getdate() returns, for me right now:
2007-10-10 09:02:34

then, using = between the 2 values will return false, obviously.

the double convert statement I suggested will transform the getdate() value into
2007-10-10 00:00:00
and
2007-10-11 00:00:00

and using the >= and <, the first value stored will indeed match that condition


0
 
LVL 14

Assisted Solution

by:Jai S
Jai S earned 400 total points
ID: 20046683
hi Guru(Angel)
this query
WHERE     (convert(varchar(10),dbo.Bookht_Port_History.StartDate,120)  = convert(varchar(10),getdate(),120))

will not take the TIME into consideration while checking...right ?
0
 

Author Comment

by:zolf
ID: 20046686

angellll

thanks for your comment.can you please explain what your query does. or refer me to some site.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20046791
@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

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question