sql query to return todays data

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
zolfAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
answer_meConnect With a Mentor Commented:
Use this

WHERE (Convert(varchar,dbo.Bookht_Port_History.StartDate,112)  = Convert(varchar,GETDATE(),112) )
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
HuyBDConnect With a Mentor Commented:
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
 
Jai STech ArchCommented:
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
 
zolfAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jai SConnect With a Mentor Tech ArchCommented:
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
 
zolfAuthor Commented:

angellll

thanks for your comment.can you please explain what your query does. or refer me to some site.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.