Days falling in a certain period

I have 4 dates:
declare @.PeriodDateBegin smalldatetime
declare @.PeriodDateEnd smalldatetime
declare @.BeginDate smalldatetime
declare @.EndDate smalldatetime
Does anyone know a function that can give me the number of days that
fall in a period?
Example:
set @.PeriodDateBegin = '2005-01-01'
set @.PeriodDateEnd = '2005-01-31'
set @.BeginDate = '2005-01-16'
set @.EndDate = '2005-06-01'
The result should be 16.
The function should work quickly so I prefer not using a calendar
table, if this is possible.
Kind regards,
Stijn Verrept.Hi
Can you be more specific? Why 16?
How does four dates relate each other?
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:%23Y%23wqO9UFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have 4 dates:
> declare @.PeriodDateBegin smalldatetime
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> Does anyone know a function that can give me the number of days that
> fall in a period?
> Example:
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> The result should be 16.
> The function should work quickly so I prefer not using a calendar
> table, if this is possible.
>
> --
> Kind regards,
> Stijn Verrept.|||Uri Dimant wrote:

> Hi
> Can you be more specific? Why 16?
> How does four dates relate each other?
Of course: you have two timespans: the one from @.PeriodDateBegin till
@.PeriodDateEnd and the one from @.BeginDate till @.EndDate. 16 is the
number of days that overlap.
I hope it is clear now.
Kind regards,
Stijn Verrept.|||Hi
A calendar table is often the fastest solution when working with
dates/periods, therefore you may not get the quickest solution without
trying that option.
John
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:%23Y%23wqO9UFHA.2520@.TK2MSFTNGP09.phx.gbl...
>I have 4 dates:
> declare @.PeriodDateBegin smalldatetime
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> Does anyone know a function that can give me the number of days that
> fall in a period?
> Example:
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> The result should be 16.
> The function should work quickly so I prefer not using a calendar
> table, if this is possible.
>
> --
> Kind regards,
> Stijn Verrept.|||declare @.PeriodDateEnd smalldatetime
declare @.BeginDate smalldatetime
declare @.EndDate smalldatetime
set @.PeriodDateBegin = '2005-01-01'
set @.PeriodDateEnd = '2005-01-31'
set @.BeginDate = '2005-01-16'
set @.EndDate = '2005-06-01'
Select DateDiff(day,
Case When @.PeriodDateBegin > @.BeginDate
Then @.PeriodDateBegin Else @.BeginDate End,
Case When @.PeriodDateEnd < @.EndDate
Then @.PeriodDateEnd Else @.EndDate End)
+ 1 -- to count days inclusively and generate 16...
"Stijn Verrept" wrote:

> Uri Dimant wrote:
>
> Of course: you have two timespans: the one from @.PeriodDateBegin till
> @.PeriodDateEnd and the one from @.BeginDate till @.EndDate. 16 is the
> number of days that overlap.
> I hope it is clear now.
>
> --
> Kind regards,
> Stijn Verrept.
>|||The + 1 is because the datediff() function actually counts date "boundaries"
between the two specified datetimes
"CBretana" wrote:
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> Select DateDiff(day,
> Case When @.PeriodDateBegin > @.BeginDate
> Then @.PeriodDateBegin Else @.BeginDate End,
> Case When @.PeriodDateEnd < @.EndDate
> Then @.PeriodDateEnd Else @.EndDate End)
> + 1 -- to count days inclusively and generate 16...
> "Stijn Verrept" wrote:
>|||CBretana wrote:

> The + 1 is because the datediff() function actually counts date
> "boundaries" between the two specified datetimes
Great thanks a lot!!! I believe this will indeed go faster than
working with calendar table.
Kind regards,
Stijn Verrept.

DateTime parameter in report

Hi All:

I wanna have DateTime calendar filter in report, so i create a parameter call EndDate which defualt vavlue today, now i have problem how can i get the value from Date Time calendar after every time user selected new date, then i need to pass this date time value to subreport.

Thanks

Nick

Not sure I understand your request well. Are you asking how to pass a parameter value from a main report to a subreport? You may want to read the following BOL page: http://msdn2.microsoft.com/en-us/library/aa337167.aspx. In particular, you can pass parameter values to subreports by editing the subreport properties in the main report and setting the parameter expression to =Parameters!EndDate.Value

-- Robert

|||

hi Robert.

Thanks for your reply, now i got another error after i set up the date time parameter in subreports.

In the property of sub report , i use =Parameters!WeekEnding.Value, weekEnding is date time type parameter both in main and sub report.

now i run the report. i got error like below:

An error occured during local report processing. The value provided for the report parameter "WeekEnding" is not valid for it's type.

I have no ideas about this error message. it works fine if i change WeekEnding type to string both in main and sub report. is that anything i missing out here?

thanks

Nick

datetime format

Hi.
I have two parameters called StartDate and EndDate.These parameters are from datetime type.
I want to view the records between these dates.I have some questions:

1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part.

2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox.
For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy

3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this?

Thanks!

Try doing a convert on your database datetime field similar to this in your query

convert(datetime, "datefield", 104)

This will format the date as dd.mm.yyyy. You can also do this on the parameter value so they are both in the same format. Your query would look something like this:

select * from table where convert(datetime, "datefield", 104) >= convert(datetime, @.StartDate, 104) and convert(datetime, "datefield", 104) <= convert(datetime, @.EndDate,104)

To display all records you can set the default values to the maximum and minimum dates in your database. The issue with this is that everytime the report is opened, it will automatically run for all dates. Not sure how to make it work only if the user doesn't select dates.

|||kmcclung thanks for the reply.
But it didn't work.
I wrote convert(datetime, myDateField, 104) and then tried the third parameter for 103, 4, ...
But it didn't change.
Then I realized that it is not dependent on that number.
It uses only the default datetime format.
The records in my database are like dd.mm.yyyy hh:mm:ss
And after I used the CONVERT function NOTHING changed.
I only want the date part to be visible.(only want this)
And the second problem is that as I said before when I click the calendar button near the date texbox area and select a date like 15.12.2001 then it is written to textbox like 12.15.2001.
And because of not existing a month number like 15 an error occurs.
I mean that I want to change that calendar's format.

How can I correct this?|||What type of database you are using?|||

0) It sounds like your database is NOT storing dates with a DATETIME format. Why not?

1) To take '15.11.1984 23:59:14' and store it as a DATETIME with time stripped off (set to midnite):

CONVERT(DATETIME, CAST(CONVERT(DATETIME, '15.11.1984 23:59:14', 104) AS INT))

DATETIME

Below SQL works
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select top 10 * from mytable where mydate between @.startdate and @.enddate
But , below one error out with message
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from mytable where mydate between ' + @.startdate +
' AND ' + @.enddate
execute @.sql
Here @.startdate and @.enddate are parameters and used inside a SP.
I have to use dynamic SQL for my logic and don't want to use CONVERT
function.
How to make this dynamic SQL work '
Thx
ShShamin,
the following code should work:
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from MyTable where MyTime between '''
+ cast(@.startdate as varchar) + ''' AND ''' + cast (@.enddate as varchar) +
''''
execute (@.sql)
hope this helps
Quentin
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#TgE1pwSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Below SQL works
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select top 10 * from mytable where mydate between @.startdate and
@.enddate
> But , below one error out with message
> Server: Msg 241, Level 16, State 1, Line 4
> Syntax error converting datetime from character string.
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select @.sql = 'select * from mytable where mydate between ' + @.startdate
+
> ' AND ' + @.enddate
> execute @.sql
> Here @.startdate and @.enddate are parameters and used inside a SP.
> I have to use dynamic SQL for my logic and don't want to use CONVERT
> function.
> How to make this dynamic SQL work '
> Thx
> Sh
>
>

Dates

trying to create a stored proc with this query:
select * from table1 where datefield BETWEEN @.startdate
AND @.enddate.
When using Crystal reports against this,it works fine
except when you are just using one day- for example your
start date and end date would both be 9/2/2004. Anyone run
into this? Should I do something different? THANKS!probably you are not considering time component. See following example.
create table t(dt datetime)
insert into t values ('20040903 12:24:15')
insert into t values ('20040903 1:24:15')
insert into t values ('20040903 16:24:15')
insert into t values ('20040903')
insert into t values ('20040904')
select * from t
where dt between '20040903' and '20040903 23:59:59'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"Ann" <anonymous@.discussions.microsoft.com> wrote in message
news:51bb01c49134$9db6b420$a501280a@.phx.gbl...
> trying to create a stored proc with this query:
> select * from table1 where datefield BETWEEN @.startdate
> AND @.enddate.
> When using Crystal reports against this,it works fine
> except when you are just using one day- for example your
> start date and end date would both be 9/2/2004. Anyone run
> into this? Should I do something different? THANKS!

datediff without weekends

How would I use the datediff function without counting wends?
declare @.StartDate SmallDateTime
declare @.EndDate SmallDateTime
set @.StartDate = '10/01/05'
set @.EndDate = getdate()
select datediff(d,@.StartDate,@.EndDate)
--except for the wends
This value returns 18, but I want it to not count wends so it should only
return 13.
--
TIA,
ChrisRChris,
Take a look at:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_threa
d/thread/fc6d9c7aa9b2580d/af9e9a9f851db285?lnk=st&q=Date+Difference+without+wen
ds&rnum=1&hl=en#af9e9a9f851db285[/url]
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||> set @.StartDate = '10/01/05'
Egads, this is a very ambiguous date format. I highly recommend using a
more standard format, e.g. YYYYMMDD, that cannot suddenly break if you
change your regional settings or run your code in a database with a
different language or dateformat setting...

> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
Use a calendar table, then you can incorporate holidays and other
non-working days too.
http://www.aspfaq.com/2519|||Look up how to design a Calendar table, which is a more general tool
for all of the temporal queries you will do.|||This link may help with Joe's suggestion.
http://www.aspfaq.com/show.asp?id=2519
HTH
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129763725.019473.177630@.o13g2000cwo.googlegroups.com...
> Look up how to design a Calendar table, which is a more general tool
> for all of the temporal queries you will do.
>|||Hey, Jerry, make the lazy bum work for it !! Google is a good habit
instead of the "do my homework for me" mentality that floods the
Newsgroups.|||Chris,
If you only want to exclude wends, here's one way to achieve this
(assuming Monday as the first day of the w):
SELECT
days/7*5 + days%7
- CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
- CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
DATEDIFF(day, @.StartDate, @.EndDate) + 1 AS days,
DATEPART(wday, @.StartDate + @.@.DATEFIRST - 1) AS wd
) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||Thanks to all...
--
TIA,
ChrisR
"ChrisR" wrote:

> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should on
ly
> return 13.
> --
> TIA,
> ChrisR