Date Range for SQL 2005

I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:

How to get the last day of the previous month? And the last day of the
current month?

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')

Thanks,
AaronAaron (odysseus183@.hotmail.com) writes:

Quote:

Originally Posted by

I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
>
How to get the last day of the previous month? And the last day of the
current month?
>
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')

SELECT dateadd(DAY, -1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01')

SELECT dateadd(DAY, -1,
dateadd(MONTH, 1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01'))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||How to get the last day of the previous month? And the last day of the

Quote:

Originally Posted by

current month?

Below alternative to the technique Erland posted. Although it's not as
clear as building a date string, it performs better in situations where
dates are calculated for each row. The overhead of converting to/from
date/string can add up for large tables.

SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1,
0))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aaron" <odysseus183@.hotmail.comwrote in message
news:1184361210.176551.223620@.n2g2000hse.googlegro ups.com...

Quote:

Originally Posted by

>I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
>
How to get the last day of the previous month? And the last day of the
current month?
>
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')
>
Thanks,
Aaron
>

|||On Fri, 13 Jul 2007 14:13:30 -0700, Aaron wrote:

Quote:

Originally Posted by

>I was reading the article and was unable to get some of queries to
>execute. Can anyone help get these to work:
>
>How to get the last day of the previous month? And the last day of the
>current month?
>
>SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
>'19991231')
>SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
>'20000131')

Hi Aaron,

When I copy and paste these queries into SSMS, I get the desired
results: June 30 and July 31. Your request for help to get them to work
implies that you got different results. Could you expand on that?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Date Range for Previous Week

How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!

Code Snippet

CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]

(@.Classification varchar(50))

AS

BEGIN

SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,

RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],

RC_STAT.dbo.Brand_Dimension.Item_Merged,

Qry_Sales_Group.Region_Key,

Qry_Sales_Group.Region,

Qry_Out_Of_Stock.product_structure_level,

Qry_Out_Of_Stock.product_entity_code,

Qry_Out_Of_Stock.cycle_day,

Qry_Out_Of_Stock.customer_code,

Qry_Out_Of_Stock.description,

Qry_Sales_Group.Code,

Qry_Sales_Group.SR_Name,

Qry_Sales_Group.Name AS Territory_Name,

Qry_Out_Of_Stock.Store_Name,

Qry_Out_Of_Stock.time_log,

Qry_Out_Of_Stock.out_of_stock,

Period_Code

FROM RC_STAT.dbo.Brand_Dimension INNER JOIN

dbo.Qry_Out_Of_Stock ON

RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN

dbo.Qry_Sales_Group ON

dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS

Where Classification=@.Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =

case

when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3

else datediff(dd,0,GetDate()) - 1

end

END

SET NOCOUNT OFF

Here it is,

Code Snippet

CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]

(@.Classification varchar(50))

AS

BEGIN

SELECT

RC_STAT.dbo.Brand_Dimension.Report_Level,

RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],

RC_STAT.dbo.Brand_Dimension.Item_Merged,

Qry_Sales_Group.Region_Key,

Qry_Sales_Group.Region,

Qry_Out_Of_Stock.product_structure_level,

Qry_Out_Of_Stock.product_entity_code,

Qry_Out_Of_Stock.cycle_day,

Qry_Out_Of_Stock.customer_code,

Qry_Out_Of_Stock.description,

Qry_Sales_Group.Code,

Qry_Sales_Group.SR_Name,

Qry_Sales_Group.Name AS Territory_Name,

Qry_Out_Of_Stock.Store_Name,

Qry_Out_Of_Stock.time_log,

Qry_Out_Of_Stock.out_of_stock,

Period_Code

FROM

RC_STAT.dbo.Brand_Dimension

INNER JOIN dbo.Qry_Out_Of_Stock

ON RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS

Where

Classification=@.Classification

And dbo.Qry_Out_Of_Stock.time_log >= dateadd(dd,-6,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))), --Monday

And dbo.Qry_Out_Of_Stock.time_log < dateadd(dd,-1,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))) -- Less than Saturday

END

SET NOCOUNT OFF

|||Thanks!! now how would i do this for periods. I do have a period field, but its a Char so i cant really do the (-1) . What would be a good aproach for this, is there a way i can have the timelog=period, each period has a date.|||What do you meant "Period", can you provide some sample data (both input & expected output)

|||

I have a "Period_code" Field in the stored procedure. The field is a char. And looks like this 200708 (for period 8). Should i convert this number to an INT and then do a -1? for the where clause. And do a trim, to just grab the last 2 digits.

I want to get all the records for the previous period. So if today is period 200709 , i want period 200708 etc etc. I hope this helps.

Also how would i get it to realized that today=period 200709 or whatever period it is, on the date the report is run. Im trying to find a way to link these.

Date query

Hi, I'm new to SQL. I need to return a recordset which will include only
records with a date after previous June. (Could be this year or last) It's
for tax purposes. How can i specify that it's the last June, not 'June' as a
value.
Hope somebody can help. Many thanks for your ideas.
Ant
SELECT date,
payment
FROM table
WHERE "date is greater than beginning of this financial year"That would be a option to use a Fical table which is populate with the dates
of the fiscal year and the fiscal period, there for you could easily
determine the appopiate date within the fiscal year and join it to the data
table. So for you there would be not need to touch the condition on the data
table because the join would do all the work for you.
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ant" <Ant@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E9F635A9-8517-49F3-A7C3-318B9D82BC22@.microsoft.com...
> Hi, I'm new to SQL. I need to return a recordset which will include only
> records with a date after previous June. (Could be this year or last) It's
> for tax purposes. How can i specify that it's the last June, not 'June' as
> a
> value.
> Hope somebody can help. Many thanks for your ideas.
> Ant
> SELECT date,
> payment
> FROM table
> WHERE "date is greater than beginning of this financial year"|||Hi Ant
Probably this can solve the problem
SELECT date, payment
FROM table
WHERE
date > (
SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
datepart(yy,getdate())-1
ELSE datepart(yy,getdate())
END as varchar(4)))
Please let me know if there is any thing that u wanted to know.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ant" wrote:

> Hi, I'm new to SQL. I need to return a recordset which will include only
> records with a date after previous June. (Could be this year or last) It's
> for tax purposes. How can i specify that it's the last June, not 'June' as
a
> value.
> Hope somebody can help. Many thanks for your ideas.
> Ant
> SELECT date,
> payment
> FROM table
> WHERE "date is greater than beginning of this financial year"|||Hello Chandra,
Thank you very much for your idea. It looks great. It almost works.
Unfortunately it returns records from January 31-2004, however the earliest
record should be no earlier than the previous June. I'll play around with it
but if you have any ideas, I'd be most grateful.
Many thanks
Ant
"Chandra" wrote:
> Hi Ant
> Probably this can solve the problem
>
> SELECT date, payment
> FROM table
> WHERE
> date > (
> SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> datepart(yy,getdate())-1
> ELSE datepart(yy,getdate())
> END as varchar(4)))
> Please let me know if there is any thing that u wanted to know.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ant" wrote:
>|||Hi Ant
Then it might be with the date format.
just try this way.
FYI: i changed from 01/06 to 06/01
SELECT date, payment
FROM table
WHERE
date > (
SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
datepart(yy,getdate())-1
ELSE datepart(yy,getdate())
END as varchar(4)))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ant" wrote:
> Hello Chandra,
> Thank you very much for your idea. It looks great. It almost works.
> Unfortunately it returns records from January 31-2004, however the earlies
t
> record should be no earlier than the previous June. I'll play around with
it
> but if you have any ideas, I'd be most grateful.
> Many thanks
> Ant
> "Chandra" wrote:
>|||Hi Chandra,
Thanks very much for that help. It works. I was actually thinking on the way
home, "I wonder if I change the date around..." But I would never have been
able to work out that date string in the WHERE clause. I understand Transact
SQL language but not good at working out queries like this one. Can you
recommend any good books on useful business queries that I might gain more
understanding by? Kind of techniques, rather than syntax. If anybody knows,
it would be great.
Chandra, Thanks heaps!
"Chandra" wrote:
> Hi Ant
> Then it might be with the date format.
> just try this way.
> FYI: i changed from 01/06 to 06/01
> SELECT date, payment
> FROM table
> WHERE
> date > (
> SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> datepart(yy,getdate())-1
> ELSE datepart(yy,getdate())
> END as varchar(4)))
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ant" wrote:
>|||Hi Ant
I can understand your curiosity. I never came across and book that can give
you an idea on how to do it. Most of the books give u only the syntax.
To get more ideas on how to build business logic, u need to practice. Just
think of a situation and try to build the query for yourself. One place u ca
n
find to get a lot of ideas is newsgroups and user-groups.
There are also a lot of websites that are developed by MVPs. Just go through
them, u might pick up ideas and techniques.
Please let me know if you would like to know anything else
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ant" wrote:
> Hi Chandra,
> Thanks very much for that help. It works. I was actually thinking on the w
ay
> home, "I wonder if I change the date around..." But I would never have bee
n
> able to work out that date string in the WHERE clause. I understand Transa
ct
> SQL language but not good at working out queries like this one. Can you
> recommend any good books on useful business queries that I might gain more
> understanding by? Kind of techniques, rather than syntax. If anybody knows
,
> it would be great.
> Chandra, Thanks heaps!
>
> "Chandra" wrote:
>|||Thank you Chandra, you've been a great help. I'll keep on practising & check
out the News groups.
Cheers
Ant
"Chandra" wrote:
> Hi Ant
> I can understand your curiosity. I never came across and book that can giv
e
> you an idea on how to do it. Most of the books give u only the syntax.
> To get more ideas on how to build business logic, u need to practice. Just
> think of a situation and try to build the query for yourself. One place u
can
> find to get a lot of ideas is newsgroups and user-groups.
> There are also a lot of websites that are developed by MVPs. Just go throu
gh
> them, u might pick up ideas and techniques.
> Please let me know if you would like to know anything else
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ant" wrote:
>sql

Date Picker in US Format after SP2

Updated SQL 2005 to SP2 and Reporting Services Date Picker now has dates defaulting to US format (MM/DD/YYYY) instead of our previous English-Australian (DD/MM/YYYY). Both the clients and server are set to English Australian regional settings in the O/S and the reports are composed for English Australian language. Reports have correct format in Visual Studio development and re-deploying them had no effect. Everything was fine under SP1 - not sure what is going on. Any ideas?

I have had so much trouble with date fields that I now keep them as text fields in Reporting Services and pass then through to SQL Server as VarChars(). Using three letters for the month (Jan, Feb, Mar etc) is the best way to may sure that SQL Server gets it right. I always set a default in the format "12 Apr 07 13:00" so the use sees what format is required. This means not using the date control but in my applications we time as well as date and the date control doesn't do this anyway.

The problem seems to be that there are many places to set the "country" or "language" etc.

Places that I can think of are:-

1. Control Panel/Regonal and Language Settings.

2. SQL Server using Set Language (or Set DateFormat). I believe that this affects the current session and so must be used in every report query.

3. Intenet Explorer, which is relevant when accessing Report Server.

I am not sure which SP I have but the patch level is 9.1.2153.

|||After doing some more analysis; I have found that if a datetime parameter prompt is placed after two parameters that cascade, then the correct date format (in our case English-Australian DD/MM/YYYY) is used in the Date Picker (probably due to the language settings in the client and report). Otherwise you get the US Date format (MM/DD/YYYY). This means that if you have datetime parameters on their own or even with drop downs, multi-selects etc.. then you could be in for trouble. I could be mistaken but this seems like a bug in SQL 2005 SP2.|||The problem has now been narrowed down to the Reporting Services Sharepoint web parts: rswebparts.cab (v2.0). Reports run with the correct date picker format from the report server but are incorrect if they are run from the SharePoint report viewer web part. I believe these web parts were not updated in SP2 (see http://msdn2.microsoft.com/en-us/library/ms159772.aspx)|||We get this same problem outside of sharepoint after sp2. If you go directly to the reportserver (http://yourserver/reportserver) and navigate to the report you may find the same. This problem has surfaced after the sp2 upgrade - strange thing is that is does not occur if we run the report via report manager - I expect why the sharepoint is showing the problem is because it is making a direct call to the reportserver.

Date parameter problem

I found a possible solution to my problem in a previous post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1177748&SiteID=1

I need to do the same thing. However, I have problem in getting the exposed parameter to the hidden parameter. My date dimension is not continuous. If the user select a date that doesn't have the correct date in the dimension, how can I find the nearest one?

Best bet is to use the OLAP data set builder in SSRS and set parameters there. It should build a date parameter for you that populates from your cube. The only dates displayed would then be valid dates in the dimension.

Good luck,

Bryan

|||The problem with this approach is a result of long date list after running the cube for years.

I have used the method of passing parameter to report viewer through another web page. The only problem that I got is about the parameter value. I understand that the parameter must use the format like [Dim Name].[Att name].[Value]. This solves most of my problem now.

Thanks for your input.|||

Alex,

I struggled with MDX date parameters in SSRS for a while. In the end, the approach I adopted was the following:

1. create a visible non-queried parameter strongly typed as date. (say it's called FromDate)

2. create a hidden parameter typed as a string (say, FromDateHiddenString)

3. set the default value for the hidden parameter to be somethign like: ="[TIME].[Date].&[" & Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "T00:00:00]"

Benefit of this is that you get a calendar picker for the date (FromDate). Then the value chosen by the user is passed into the hidden parameter (FromDateHiddenString), which is the parameter linked to your mdx query.

Unfortunately you cannot restrict the dates available via the calendar. but I've found that it drives the users more crazy to have to scroll through a massive list of dates, than to get no data when they choose an invalid date (after all, they generally know which dates they are interested in!)

i am also in HKSAR struggling to learn SSRS/SSAS/SSIS/MDX .. let me know if you want ot chat offline.

tx,

JG

|||Dear JG,

I've found this trick in somewhere else before. This seems nice but it can't suit my need. The main problem is I can't guarantee the date dimension is continuous. If the user pick that missing date from the date picker unluckily, they will not understand what's happening... after that, complain may come saying the report doesn't working. So, my current approach is creating another aspx and use the ReportViewer control. Then, I can have full control over the date selection. More than that, users will be happier to have a similar web interface to access their reports.

Regards,
Alex

Date in subject line of email subscription

Hi,
is there any way to automatically put the date of previous day in
subject line of email subscription? Can anybody tell me how to do it?The only way to do this would be with Data Driven subscriptions. First you
have to have Enterprise edition or Data Driven subscriptions are not
present. If you do have the Enterprise edition then you can generate the
subject from a SQL query.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"mentor" <daniel.pater@.pl.ibm.com> wrote in message
news:1126166552.090643.170840@.f14g2000cwb.googlegroups.com...
> Hi,
> is there any way to automatically put the date of previous day in
> subject line of email subscription? Can anybody tell me how to do it?
>|||Thanks a lot
i resolved this problem in different way. I save the reports to shared
folder and send them using vbs file :)

Date function

Hi!

I have a report that needs to be run on the seventh of every month for
the dates from 6th of the previous months to the 5th of the current
month. For example, I have to run a report on February 7th for the
01/06/2007 to 02/05/2007. Right now I am doing it manually but I was
curious if there a function or something that will give me the
required date range on the 7th of every month.

Any ideas?

Thanks,
T.tolcis (nytollydba@.gmail.com) writes:

Quote:

Originally Posted by

I have a report that needs to be run on the seventh of every month for
the dates from 6th of the previous months to the 5th of the current
month. For example, I have to run a report on February 7th for the
01/06/2007 to 02/05/2007. Right now I am doing it manually but I was
curious if there a function or something that will give me the
required date range on the 7th of every month.

SELECT @.today = convert(char(8), getdate(), 112)
IF DAY(@.today) = 7
BEGIN
SELECT @.startdate = dateadd(DAY, -2, dateadd(MONTH, -1, @.today))
SELECT @.enddate = dateadd(DAY, -1, @.today)
-- Run uery
END

You can learn more about the date functions in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Date difference in month

Hi

How can I show the date difference from a previous day till today in month. I need output like this -

Date Month

2007-01-01 3

2007-02-01 2

Thnx

Kapalic

Try:

SELECT DATEDIFF(m,'2007-01-01',GETDATE())

You'll get the amount off months from 1-1-2007 till now.

|||take not that datediff(month, returns the different in month as a whole even if the day different is 1 day.

Try this
select datediff(month, '2007-01-31', '2007-02-01')

date default parameter

Hello,

I have a date Start and End parameters. I want to set default value as previous quarters start and end date, how can I do this?

Thanks,

When you are passing the parameters to the report put the values there like
in reportpath = /<RerportDir>/ReportName&startdate=01/01/2005&enddate=01/03/2005
Hope this will solve U'r problem|||

hi JIM.H

Create a start & end date parameter and paste this code in the SRS code page and pass it today's date or a date entered by the user. This will return the Qtr's as I can understand from u'r question

PublicFunction parmQTRStartDate(ByVal currentDateAs DateTime)As System.DateTime

Dim iCurrentMonthAsInteger

' mm/dd/yyyy is format

iCurrentMonth = currentDate.Month()

SelectCase iCurrentMonth' Evaluate Number.

Case 1To 3' in First Quater return Previous Year

Return System.Convert.ToDateTime("09/01/" & currentDate.AddYears(-1).Year.ToString())

Case 4To 6' Second Quater in Return 1 Qtr

Return System.Convert.ToDateTime("01/01/" & currentDate.Year.ToString())

Case 7To 9' Third Quater in Return 2 Qtr

Return System.Convert.ToDateTime("04/01/" & currentDate.Year.ToString())

Case 10To 12' Fourth Quater in Return 3 Qtr

Return System.Convert.ToDateTime("07/01/" & currentDate.Year.ToString())

EndSelect

EndFunction

PublicFunction parmQTREndDate(ByVal currentDateAs DateTime)As System.DateTime

' mm/dd/yyyy is format

Dim iCurrentMonthAsInteger

iCurrentMonth = currentDate.Month()

SelectCase iCurrentMonth' Evaluate Number.

Case 1To 3' in First Quater return Previous Year

Return System.Convert.ToDateTime("12/31/" & currentDate.AddYears(-1).Year.ToString())

Case 4To 6' Second Quater in Return 1 Qtr

Return System.Convert.ToDateTime("03/31/" & currentDate.Year.ToString())

Case 7To 9' Third Quater in Return 2 Qtr

Return System.Convert.ToDateTime("06/30/" & currentDate.Year.ToString())

Case 10To 12' Fourth Quater in Return 3 Qtr

Return System.Convert.ToDateTime("09/30/" & currentDate.Year.ToString())

EndSelect

EndFunction
,Cheers
l0n3i200n

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.