Date between 12 and 12

Hi

I am trying to create a daily report that will only select the values between 12 o' clock last night and 12 o'clock the previous night. I want to see all the data for the previous day. If someone will please be able to help me with this i would be very greatful.

Here is my query, i even tried to use the NOW() function:

I'm pasting a few queries that i tried, maybe just a slight adjustment will do the trick

Code Snippet

Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, SUBSTRING(CONVERT(VARCHAR(25),m.SentDateTime),0,15) as 'NewDate'

from Members m, client c

WHERE c.clientID = m.clientID

AND m.SentDateTime < {fn NOW()}

AND m.Active = 1

Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime

Order By c.ClientName

Another one i tried

Code Snippet

Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, DateDiff(hh,({fn NOW()}-1),MAX(m.SentDateTime)) as 'NewDate'

from Members m, client c

WHERE c.clientID = m.clientID

AND m.SentDateTime > (GETDATE() - DAY(0.5))

AND m.Active = 1

Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime

Order By c.ClientName

And this one too. (Although this is from a seperate report.

Code Snippet

SELECT v.ContentId, v.ContentType, COUNT(h.Description) AS Hits, h.Description, DATEPART(yy, v.RenderDate) AS y, DATEPART(dy, v.RenderDate) AS d,

CONVERT(CHAR(12), v.RenderDate, 106) AS date

FROM ViewerPaneRenderHistory AS v INNER JOIN

Members AS M ON v.MemberId = M.MemberID INNER JOIN

HealthBytes AS h ON v.ContentId = h.HealthbyteID

WHERE (v.ContentType = 1)

GROUP BY h.Description, v.ContentType, v.ContentId, DATEPART(yy, v.RenderDate), DATEPART(dy, v.RenderDate), CONVERT(CHAR(12), v.RenderDate, 106)

HAVING (CONVERT(CHAR(12), v.RenderDate, 106) > { fn NOW() } - 1)

ORDER BY Date, COUNT(v.MemberId) DESC

I'v tries a few different ways, but just can't seem to get it right.

Any help would be greatly appreciated.

Kind Regards

Carel Greaves

use the following contion on the where clause...

Code Snippet

ColumnName < Cast(Convert(varchar,@.EndDate,101) as datetime)

and ColumnName >= Cast(Convert(varchar,@.EndDate,101) as datetime) - 1

or

ColumnName < Cast(Convert(varchar,GetDate(),101) as datetime)

and ColumnName >= Cast(Convert(varchar,GetDate(),101) as datetime) - 1

|||

Thanks

|||

Hi,carel:

I am not sure if i known your really means from the upstair threes samples,Following is my reply;

About figure 1:

i think the correct sql is :

Code Snippet

select c.clientname,m.memberid,m.name,m.surname,m.email,m.sentdatetime,SUBSTRING(CONVERT(VARCHAR(25),m.SentDatetime),0,15) as 'NewDate'

from Members as m

inner join client as c

on m.clientID=m.clientID

where m.Active=1

and m.SentDateTime>=Convert(varchar(10),dateadd(dd,-1,getdate()),120)
and m.SentDateTime<Convert(varchar(10),getdate(),120)

i don't know why you use "group by" in the first example.

if you only want previous day's data, i think you could use the condition (Marked with green) only.

NOTE:

the dateformat i used in this example Convert(varchar(10),getdate(),120) returns Date fromat 'YYYY-MM-DD'. and it length is 10

please correct this as your local format, meanwhile you should notice the length.