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

Hi all,
Is there a way to not allow a date older then a certain amount of time in
datetime paremeter in a report?
like from date not to allow anything older then 6 months.
thanks
gvOn May 1, 4:33 pm, "gv" <gerry.via...@.accesspointinc.com> wrote:
> Hi all,
> Is there a way to not allow a date older then a certain amount of time in
> datetime paremeter in a report?
> like from date not to allow anything older then 6 months.
> thanks
> gv
The best way to handle this would be to use a dataset tied to a stored
procedure/query that can limit the dates to the most recent 6 months.
Regards,
Enrique Martinez
Sr. Software Consultant|||ok thanks!!
gv
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178072625.417437.303620@.p77g2000hsh.googlegroups.com...
> On May 1, 4:33 pm, "gv" <gerry.via...@.accesspointinc.com> wrote:
>> Hi all,
>> Is there a way to not allow a date older then a certain amount of time in
>> datetime paremeter in a report?
>> like from date not to allow anything older then 6 months.
>> thanks
>> gv
>
> The best way to handle this would be to use a dataset tied to a stored
> procedure/query that can limit the dates to the most recent 6 months.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I'm already filtering it this way. how do you tell the user that they typed
in the wrong date?
thanks
gv
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178072625.417437.303620@.p77g2000hsh.googlegroups.com...
> On May 1, 4:33 pm, "gv" <gerry.via...@.accesspointinc.com> wrote:
>> Hi all,
>> Is there a way to not allow a date older then a certain amount of time in
>> datetime paremeter in a report?
>> like from date not to allow anything older then 6 months.
>> thanks
>> gv
>
> The best way to handle this would be to use a dataset tied to a stored
> procedure/query that can limit the dates to the most recent 6 months.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

DateTime Now Problem

Hey, basically i have a database of users and on a certain page load i want to register when they logged onto that page, so i created this snippet of code :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

but that give me the error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

So I changed it to :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

as when I looked at previous times in my database there format was "27/08/2007 15:25:45" for example. but that gives me the exact same error can anyone tell me why this doesnt work? / how to get it to work? My datatype for this feild is "datetime". Thanks John

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated= '" + DateTime.Now.ToString() + "' WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

OR , best way is

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

|||

Awesome this :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

way did work the best, for anyone else who is having this problem, thanks loads! John

Datetime Filter

For one of the tables on my report i need to allow only the records where a
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >

datepart problem with week extraction (T-SQL)

I'm using datepart combined with a count aggregate to count the number of
ws in a certain time period. Problem is, my employer starts each w on
Saturday. The T-SQL version of Datepart does not support a StartOfW
parameter. This defect is screwing up my reports.
Does anyone have a workaround?
Thanks,
Randall ArnoldLook up SET DATEFIRST in BOL to control the first day of the w for date
functions
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
> I'm using datepart combined with a count aggregate to count the number of
> ws in a certain time period. Problem is, my employer starts each w
> on Saturday. The T-SQL version of Datepart does not support a StartOfW
> parameter. This defect is screwing up my reports.
> Does anyone have a workaround?
> Thanks,
> Randall Arnold
>|||Another alternative is to use a calendar table, whcih gives you the
flexibility of using multiple calendars associated with the same date.
Stu|||But be aware that SQL Server doesn't calculate w number the way that the
majority of the world
does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl..
.
> Look up SET DATEFIRST in BOL to control the first day of the w for date
functions
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
>|||Apparently not.
First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a stored procedure didn't result in an error, but it
had no effect, either. No matter what value I set Datefirst to, the w is
still calculated wrong. For my purposes, 4/2/2005 needs to show as w 15
(first day of w = Saturday), but it always shows as w 14.
I don't see how a date lookup table will solve this... so, any other ideas?
This shortcoming in T-SQL is producing invalid results in my datasets...
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
> But be aware that SQL Server doesn't calculate w number the way that
> the majority of the world does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||> I don't see how a date lookup table will solve this...
You have a table with one row per day. One of the columns in this table is t
he w correct number.
Or, read in Books Online about the ISOW function, install and use that in
stead of DATEPART().
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
> Apparently not.
> First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a
> stored procedure didn't result in an error, but it had no effect, either.
No matter what value I
> set Datefirst to, the w is still calculated wrong. For my purposes, 4/
2/2005 needs to show as
> w 15 (first day of w = Saturday), but it always shows as w 14.
> I don't see how a date lookup table will solve this... so, any other ideas
? This shortcoming in
> T-SQL is producing invalid results in my datasets...
> Randall Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
>|||Ok, thanks Tibor. I should have realized how the date (w) lookup would
work (I've done a similar thing for Periods), my bad. I'm just already
linking to so many tables on this query it's become a nightmare (due to bad
database design by the original dba). I'll get familiar with with ISOWEEK
and if that doesn't work, I have a Period lookup table already and I'll just
add a w column to it.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>|||I decided just to add the W column to my existing Period table. It meant
changing several queries, as well as more manual maintenance, but it works.
Thanks again.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>