I have custom .net web based application running on IIS 6 which has SQL2000
running in background, access to this application is based on UIDs and
password from "users" table in SQL, my question is regarding web.config
file... this file has user name and password that allow web application talk
to SQL db, what sql role should this account have in order to .net
application work corectly? db owner will do but I'm wondering this is too
much...
TIAFor a qick improvement, membership in the
db_datareader (can select all data from any user table in the database) and
db_datawriter (can modify any data in any user table in the database)
roles should be enough. Then you can study grainer permissions needed.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> I have custom .net web based application running on IIS 6 which has
SQL2000
> running in background, access to this application is based on UIDs and
> password from "users" table in SQL, my question is regarding web.config
> file... this file has user name and password that allow web application
talk
> to SQL db, what sql role should this account have in order to .net
> application work corectly? db owner will do but I'm wondering this is too
> much...
> TIA|||thanks for response, will this allow execute sp_ ?
"Dejan Sarka" wrote:
> For a qick improvement, membership in the
> db_datareader (can select all data from any user table in the database) an
d
> db_datawriter (can modify any data in any user table in the database)
> roles should be enough. Then you can study grainer permissions needed.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
> "Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
> news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> SQL2000
> talk
>
>|||For stored procedures in your database, you will have to give an explicit
EXECUTE permission to this user. I you are talking about system procedures
to get some info, like sp_help, then the user will be able to execute them
without an explicit permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:F1EDD860-343A-421A-A303-2FBB25687147@.microsoft.com...[vbcol=seagreen]
> thanks for response, will this allow execute sp_ ?
> "Dejan Sarka" wrote:
>
and[vbcol=seagreen]
web.config[vbcol=seagreen]
application[vbcol=seagreen]
too[vbcol=seagreen]sql
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Tuesday, March 27, 2012
DayOfWeek Function
Would like to set a date parameter default in the report designer based on
the day of the week. So if it was Monday, then the default date would be set
to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
there a dayOfWeek function that be used in an expression that returns either
the numeric or the alpha of the week?
GlassHi,
You can easely use the expression <code>=WeekDay(Now())</code> for
retrieving the actual weekday. This combined with an IIF expression you can
create the behaviour you need, like
<code>
=IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
</code>
Hope this would help you
Jan Pieter Posthuma
"Glass" wrote:
> Would like to set a date parameter default in the report designer based on
> the day of the week. So if it was Monday, then the default date would be set
> to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> there a dayOfWeek function that be used in an expression that returns either
> the numeric or the alpha of the week?
> Glass|||Jan Pieter... It worked great. Have two ancillary question: what is the
difference between today and now? Is there a list of functions that are
valid in report server for use in expressions? Online books didn't seem to
help here.
Appreciate the help...
Glass
"Jan Pieter Posthuma" wrote:
> Hi,
> You can easely use the expression <code>=WeekDay(Now())</code> for
> retrieving the actual weekday. This combined with an IIF expression you can
> create the behaviour you need, like
> <code>
> =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> </code>
> Hope this would help you
> Jan Pieter Posthuma
>
> "Glass" wrote:
> > Would like to set a date parameter default in the report designer based on
> > the day of the week. So if it was Monday, then the default date would be set
> > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > there a dayOfWeek function that be used in an expression that returns either
> > the numeric or the alpha of the week?
> >
> > Glass|||Glass,
There is a little difference between Now() and Today(). Both return the same
date, but Now returns the actual time and Today will allways return 12AM
back. So for today:
=Now() returns 6/22/2005 9:55:04 AM
=Today() returns 6/22/2005 12:00:00 AM
I must say: I use Now mainly because of my history with VB.NET.
Jan Pieter Posthuma
"Glass" wrote:
> Jan Pieter... It worked great. Have two ancillary question: what is the
> difference between today and now? Is there a list of functions that are
> valid in report server for use in expressions? Online books didn't seem to
> help here.
> Appreciate the help...
> Glass
> "Jan Pieter Posthuma" wrote:
> > Hi,
> >
> > You can easely use the expression <code>=WeekDay(Now())</code> for
> > retrieving the actual weekday. This combined with an IIF expression you can
> > create the behaviour you need, like
> > <code>
> > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > </code>
> >
> > Hope this would help you
> >
> > Jan Pieter Posthuma
> >
> >
> >
> > "Glass" wrote:
> >
> > > Would like to set a date parameter default in the report designer based on
> > > the day of the week. So if it was Monday, then the default date would be set
> > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > there a dayOfWeek function that be used in an expression that returns either
> > > the numeric or the alpha of the week?
> > >
> > > Glass|||Thank you very much...
Glass
"Jan Pieter Posthuma" wrote:
> Glass,
> There is a little difference between Now() and Today(). Both return the same
> date, but Now returns the actual time and Today will allways return 12AM
> back. So for today:
> =Now() returns 6/22/2005 9:55:04 AM
> =Today() returns 6/22/2005 12:00:00 AM
> I must say: I use Now mainly because of my history with VB.NET.
> Jan Pieter Posthuma
> "Glass" wrote:
> > Jan Pieter... It worked great. Have two ancillary question: what is the
> > difference between today and now? Is there a list of functions that are
> > valid in report server for use in expressions? Online books didn't seem to
> > help here.
> >
> > Appreciate the help...
> >
> > Glass
> >
> > "Jan Pieter Posthuma" wrote:
> >
> > > Hi,
> > >
> > > You can easely use the expression <code>=WeekDay(Now())</code> for
> > > retrieving the actual weekday. This combined with an IIF expression you can
> > > create the behaviour you need, like
> > > <code>
> > > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > > </code>
> > >
> > > Hope this would help you
> > >
> > > Jan Pieter Posthuma
> > >
> > >
> > >
> > > "Glass" wrote:
> > >
> > > > Would like to set a date parameter default in the report designer based on
> > > > the day of the week. So if it was Monday, then the default date would be set
> > > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > > there a dayOfWeek function that be used in an expression that returns either
> > > > the numeric or the alpha of the week?
> > > >
> > > > Glass|||"Glass" skrev:
> Thank you very much...
> Glass
> "Jan Pieter Posthuma" wrote:
> > Glass,
> >
> > There is a little difference between Now() and Today(). Both return the same
> > date, but Now returns the actual time and Today will allways return 12AM
> > back. So for today:
> > =Now() returns 6/22/2005 9:55:04 AM
> > =Today() returns 6/22/2005 12:00:00 AM
> >
> > I must say: I use Now mainly because of my history with VB.NET.
> >
> > Jan Pieter Posthuma
> >
> > "Glass" wrote:
> >
> > > Jan Pieter... It worked great. Have two ancillary question: what is the
> > > difference between today and now? Is there a list of functions that are
> > > valid in report server for use in expressions? Online books didn't seem to
> > > help here.
> > >
> > > Appreciate the help...
> > >
> > > Glass
> > >
> > > "Jan Pieter Posthuma" wrote:
> > >
> > > > Hi,
> > > >
> > > > You can easely use the expression <code>=WeekDay(Now())</code> for
> > > > retrieving the actual weekday. This combined with an IIF expression you can
> > > > create the behaviour you need, like
> > > > <code>
> > > > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > > > </code>
> > > >
> > > > Hope this would help you
> > > >
> > > > Jan Pieter Posthuma
> > > >
> > > >
> > > >
> > > > "Glass" wrote:
> > > >
> > > > > Would like to set a date parameter default in the report designer based on
> > > > > the day of the week. So if it was Monday, then the default date would be set
> > > > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > > > there a dayOfWeek function that be used in an expression that returns either
> > > > > the numeric or the alpha of the week?
> > > > >
> > > > > Glass
anna jag behöver verkligen din hjälp nuu !!sql
the day of the week. So if it was Monday, then the default date would be set
to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
there a dayOfWeek function that be used in an expression that returns either
the numeric or the alpha of the week?
GlassHi,
You can easely use the expression <code>=WeekDay(Now())</code> for
retrieving the actual weekday. This combined with an IIF expression you can
create the behaviour you need, like
<code>
=IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
</code>
Hope this would help you
Jan Pieter Posthuma
"Glass" wrote:
> Would like to set a date parameter default in the report designer based on
> the day of the week. So if it was Monday, then the default date would be set
> to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> there a dayOfWeek function that be used in an expression that returns either
> the numeric or the alpha of the week?
> Glass|||Jan Pieter... It worked great. Have two ancillary question: what is the
difference between today and now? Is there a list of functions that are
valid in report server for use in expressions? Online books didn't seem to
help here.
Appreciate the help...
Glass
"Jan Pieter Posthuma" wrote:
> Hi,
> You can easely use the expression <code>=WeekDay(Now())</code> for
> retrieving the actual weekday. This combined with an IIF expression you can
> create the behaviour you need, like
> <code>
> =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> </code>
> Hope this would help you
> Jan Pieter Posthuma
>
> "Glass" wrote:
> > Would like to set a date parameter default in the report designer based on
> > the day of the week. So if it was Monday, then the default date would be set
> > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > there a dayOfWeek function that be used in an expression that returns either
> > the numeric or the alpha of the week?
> >
> > Glass|||Glass,
There is a little difference between Now() and Today(). Both return the same
date, but Now returns the actual time and Today will allways return 12AM
back. So for today:
=Now() returns 6/22/2005 9:55:04 AM
=Today() returns 6/22/2005 12:00:00 AM
I must say: I use Now mainly because of my history with VB.NET.
Jan Pieter Posthuma
"Glass" wrote:
> Jan Pieter... It worked great. Have two ancillary question: what is the
> difference between today and now? Is there a list of functions that are
> valid in report server for use in expressions? Online books didn't seem to
> help here.
> Appreciate the help...
> Glass
> "Jan Pieter Posthuma" wrote:
> > Hi,
> >
> > You can easely use the expression <code>=WeekDay(Now())</code> for
> > retrieving the actual weekday. This combined with an IIF expression you can
> > create the behaviour you need, like
> > <code>
> > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > </code>
> >
> > Hope this would help you
> >
> > Jan Pieter Posthuma
> >
> >
> >
> > "Glass" wrote:
> >
> > > Would like to set a date parameter default in the report designer based on
> > > the day of the week. So if it was Monday, then the default date would be set
> > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > there a dayOfWeek function that be used in an expression that returns either
> > > the numeric or the alpha of the week?
> > >
> > > Glass|||Thank you very much...
Glass
"Jan Pieter Posthuma" wrote:
> Glass,
> There is a little difference between Now() and Today(). Both return the same
> date, but Now returns the actual time and Today will allways return 12AM
> back. So for today:
> =Now() returns 6/22/2005 9:55:04 AM
> =Today() returns 6/22/2005 12:00:00 AM
> I must say: I use Now mainly because of my history with VB.NET.
> Jan Pieter Posthuma
> "Glass" wrote:
> > Jan Pieter... It worked great. Have two ancillary question: what is the
> > difference between today and now? Is there a list of functions that are
> > valid in report server for use in expressions? Online books didn't seem to
> > help here.
> >
> > Appreciate the help...
> >
> > Glass
> >
> > "Jan Pieter Posthuma" wrote:
> >
> > > Hi,
> > >
> > > You can easely use the expression <code>=WeekDay(Now())</code> for
> > > retrieving the actual weekday. This combined with an IIF expression you can
> > > create the behaviour you need, like
> > > <code>
> > > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > > </code>
> > >
> > > Hope this would help you
> > >
> > > Jan Pieter Posthuma
> > >
> > >
> > >
> > > "Glass" wrote:
> > >
> > > > Would like to set a date parameter default in the report designer based on
> > > > the day of the week. So if it was Monday, then the default date would be set
> > > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > > there a dayOfWeek function that be used in an expression that returns either
> > > > the numeric or the alpha of the week?
> > > >
> > > > Glass|||"Glass" skrev:
> Thank you very much...
> Glass
> "Jan Pieter Posthuma" wrote:
> > Glass,
> >
> > There is a little difference between Now() and Today(). Both return the same
> > date, but Now returns the actual time and Today will allways return 12AM
> > back. So for today:
> > =Now() returns 6/22/2005 9:55:04 AM
> > =Today() returns 6/22/2005 12:00:00 AM
> >
> > I must say: I use Now mainly because of my history with VB.NET.
> >
> > Jan Pieter Posthuma
> >
> > "Glass" wrote:
> >
> > > Jan Pieter... It worked great. Have two ancillary question: what is the
> > > difference between today and now? Is there a list of functions that are
> > > valid in report server for use in expressions? Online books didn't seem to
> > > help here.
> > >
> > > Appreciate the help...
> > >
> > > Glass
> > >
> > > "Jan Pieter Posthuma" wrote:
> > >
> > > > Hi,
> > > >
> > > > You can easely use the expression <code>=WeekDay(Now())</code> for
> > > > retrieving the actual weekday. This combined with an IIF expression you can
> > > > create the behaviour you need, like
> > > > <code>
> > > > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > > > </code>
> > > >
> > > > Hope this would help you
> > > >
> > > > Jan Pieter Posthuma
> > > >
> > > >
> > > >
> > > > "Glass" wrote:
> > > >
> > > > > Would like to set a date parameter default in the report designer based on
> > > > > the day of the week. So if it was Monday, then the default date would be set
> > > > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > > > there a dayOfWeek function that be used in an expression that returns either
> > > > > the numeric or the alpha of the week?
> > > > >
> > > > > Glass
anna jag behöver verkligen din hjälp nuu !!sql
Sunday, March 25, 2012
Day of the week
Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
--
Anith|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.sql
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
--
Anith|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.sql
Monday, March 19, 2012
Datetime Parameter
I have deployed a report which accepts a from and to date parameter, the
report links to another report based upon the contents of the cell which is
selected and passes the cell content and the 2 date parameters to the next
report.
Now this is all fine however when the date is passed to the second report it
appears as non en-GB. i.e. I am passing 07/01/2006 (7th of January 2006)
which on the second report becomes 1st July 2006. Passing a date such as the
14th January returns an invalid date error (no 14th month).
I have got around the error by accepting the date into the second report as
a string, however the dates appear (from my perspective) incorrectly
formatted and I can't have a datetime picker.
Incidentally if I change the second reports parameter back to datetime the
date pickers appears correctly and work as expected. Both reports are
formatted en-GB.
Does any one know where I am going wrong?
--
Regards
Darren FranceWhat is the language of the Report set to?
Have you tried explicitly converting the value passed in the Jump To.
ie =Convert.ToDateTime(Parameters!DateParam.Value) ?
Andy Potter|||If I change the parameter back to datetime (with or without the explicit
convert) I get the following error with the dates 08/01/2006 and 15/01/2006
(8th and 15th January:
The value provided for the report parameter 'UserEndDate' is not valid for
its type. (rsReportParameterTypeMismatch)
All reports are set to English (United Kingdom)
XML: <Language>en-GB</Language>
I should have pointed out (my apologies for not doing so) is that I am using
Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00
(2005). Deploying to a server with Reporting Services 2005 but with SQL 7.0
(SP4) as both the report database host and data source.
Regards
Darren France
"Potter" wrote:
> What is the language of the Report set to?
> Have you tried explicitly converting the value passed in the Jump To.
> ie => Convert.ToDateTime(Parameters!DateParam.Value) ?
> Andy Potter
>|||Darren,
I tried to reproduce this error and I cannot.
How are you passing parameters to the second report? Are you using the
Jump To navigation?
Andy Potter|||I am using jump to navigation, and passing: =Parameters!<parameter>.Value
I have also tried the convert.todate and also splitting the date into it's
constituant parts (day, month, year) and converting back to a date. If I add
textboxes with the parameter(s) value in it, it is correct on the first
report but appears in the wrong format on the second report (which is what I
expected but wanked to make sure it was not the date time picker).
I have also explicitly converted the date parameter in the stored procedure
- convert(smalldatetime, <parameter>, 103). But given that I am not passing a
date back as part of the query, and the reports work individually (i.e.
without the parameter passing) I don't think this affecting the results.
I have just double checked that the regional settings of both my machine and
the server (data source and report source) are correct.
I have noticed that previewing the report within the designer errors even on
the source report. I have been through the options in visual studio and
provided I avoid clicking on the 'data customisation ui' which results in a
nice error and a restart of visual studio, I personally cannot find anything
which could be causing this and I am out of ideas.
Regards
Darren France
"Potter" wrote:
> Darren,
> I tried to reproduce this error and I cannot.
> How are you passing parameters to the second report? Are you using the
> Jump To navigation?
> Andy Potter
>|||I set up the same scenario without a problem.
2 Reports. Each have a parameter called gbdate and a single textbox
with a value of parameters!gbdate.value. The first report has a
navigation jump to to the second report, passing the param value to the
second report. Both reports have a language of en-GB.
One thing I did notice. Unless you have your browser language set to
en-GB, you won't see the date value in the parameter selection textbox
in the dd/mm/yyyy format.
Andy Potter|||I ran into the same problems with the 2000 SP 2 version of RS. My only
solution was to include custom code to every report (created an empty report
with the code so I can use copy + paste) and run the code on all passed
parameters. it was like:
Type in the "Code" section of report properties:
Public Function GetISODate(tmDate as DateTime)
return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
End Function
and used it on the navigation:
=Code.GetISODate(Parameters!<parameter>.Value)
It seems that the ISO formatted date (year-month-day) will be parsed the
same by every culture.
"Darren France" wrote:
> I am using jump to navigation, and passing: =Parameters!<parameter>.Value
> I have also tried the convert.todate and also splitting the date into it's
> constituant parts (day, month, year) and converting back to a date. If I add
> textboxes with the parameter(s) value in it, it is correct on the first
> report but appears in the wrong format on the second report (which is what I
> expected but wanked to make sure it was not the date time picker).
> I have also explicitly converted the date parameter in the stored procedure
> - convert(smalldatetime, <parameter>, 103). But given that I am not passing a
> date back as part of the query, and the reports work individually (i.e.
> without the parameter passing) I don't think this affecting the results.
> I have just double checked that the regional settings of both my machine and
> the server (data source and report source) are correct.
> I have noticed that previewing the report within the designer errors even on
> the source report. I have been through the options in visual studio and
> provided I avoid clicking on the 'data customisation ui' which results in a
> nice error and a restart of visual studio, I personally cannot find anything
> which could be causing this and I am out of ideas.
>
> --
>
> Regards
> Darren France
>
> "Potter" wrote:
> > Darren,
> >
> > I tried to reproduce this error and I cannot.
> >
> > How are you passing parameters to the second report? Are you using the
> > Jump To navigation?
> >
> > Andy Potter
> >
> >|||I have the same problem. One report has default datetime parameters. It uses
the navigation feature to call a second report, with the same parameters
passed to the second report. In the designer this works fine but when
deployed the second report crashes with an rsReportParameterTypeMismatch
error.
This only happens when the dates being passed are non/ambiguous US/English.
In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly 6-Jan-06
works, 16-Jan-06 doesn't.
So it's not really a type mismatch error but a dataconversion issue.
Any help welcome!
Bob
--
Bob
"Potter" wrote:
> I set up the same scenario without a problem.
> 2 Reports. Each have a parameter called gbdate and a single textbox
> with a value of parameters!gbdate.value. The first report has a
> navigation jump to to the second report, passing the param value to the
> second report. Both reports have a language of en-GB.
> One thing I did notice. Unless you have your browser language set to
> en-GB, you won't see the date value in the parameter selection textbox
> in the dd/mm/yyyy format.
> Andy Potter
>|||Works a treat, thank you very much.
p.s.
I would like to apologise and point out that there was a typing mistake in
my last post, it should have read 'but wanted', I was not sharing my personal
hobbies with the newsgroup. Unfortunately I cannot find a regional setting to
blame that on!
--
Regards
Darren France
"GreyMana" wrote:
> I ran into the same problems with the 2000 SP 2 version of RS. My only
> solution was to include custom code to every report (created an empty report
> with the code so I can use copy + paste) and run the code on all passed
> parameters. it was like:
> Type in the "Code" section of report properties:
> Public Function GetISODate(tmDate as DateTime)
> return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
> End Function
> and used it on the navigation:
> =Code.GetISODate(Parameters!<parameter>.Value)
> It seems that the ISO formatted date (year-month-day) will be parsed the
> same by every culture.
> "Darren France" wrote:
> > I am using jump to navigation, and passing: =Parameters!<parameter>.Value
> >
> > I have also tried the convert.todate and also splitting the date into it's
> > constituant parts (day, month, year) and converting back to a date. If I add
> > textboxes with the parameter(s) value in it, it is correct on the first
> > report but appears in the wrong format on the second report (which is what I
> > expected but wanked to make sure it was not the date time picker).
> >
> > I have also explicitly converted the date parameter in the stored procedure
> > - convert(smalldatetime, <parameter>, 103). But given that I am not passing a
> > date back as part of the query, and the reports work individually (i.e.
> > without the parameter passing) I don't think this affecting the results.
> >
> > I have just double checked that the regional settings of both my machine and
> > the server (data source and report source) are correct.
> >
> > I have noticed that previewing the report within the designer errors even on
> > the source report. I have been through the options in visual studio and
> > provided I avoid clicking on the 'data customisation ui' which results in a
> > nice error and a restart of visual studio, I personally cannot find anything
> > which could be causing this and I am out of ideas.
> >
> >
> > --
> >
> >
> > Regards
> >
> > Darren France
> >
> >
> > "Potter" wrote:
> >
> > > Darren,
> > >
> > > I tried to reproduce this error and I cannot.
> > >
> > > How are you passing parameters to the second report? Are you using the
> > > Jump To navigation?
> > >
> > > Andy Potter
> > >
> > >|||Hi,
what do you mean by "Used it on the Navigation"?
thanks
Matt
"GreyMana" wrote:
> I ran into the same problems with the 2000 SP 2 version of RS. My only
> solution was to include custom code to every report (created an empty report
> with the code so I can use copy + paste) and run the code on all passed
> parameters. it was like:
> Type in the "Code" section of report properties:
> Public Function GetISODate(tmDate as DateTime)
> return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
> End Function
> and used it on the navigation:
> =Code.GetISODate(Parameters!<parameter>.Value)
> It seems that the ISO formatted date (year-month-day) will be parsed the
> same by every culture.
> "Darren France" wrote:
> > I am using jump to navigation, and passing: =Parameters!<parameter>.Value
> >
> > I have also tried the convert.todate and also splitting the date into it's
> > constituant parts (day, month, year) and converting back to a date. If I add
> > textboxes with the parameter(s) value in it, it is correct on the first
> > report but appears in the wrong format on the second report (which is what I
> > expected but wanked to make sure it was not the date time picker).
> >
> > I have also explicitly converted the date parameter in the stored procedure
> > - convert(smalldatetime, <parameter>, 103). But given that I am not passing a
> > date back as part of the query, and the reports work individually (i.e.
> > without the parameter passing) I don't think this affecting the results.
> >
> > I have just double checked that the regional settings of both my machine and
> > the server (data source and report source) are correct.
> >
> > I have noticed that previewing the report within the designer errors even on
> > the source report. I have been through the options in visual studio and
> > provided I avoid clicking on the 'data customisation ui' which results in a
> > nice error and a restart of visual studio, I personally cannot find anything
> > which could be causing this and I am out of ideas.
> >
> >
> > --
> >
> >
> > Regards
> >
> > Darren France
> >
> >
> > "Potter" wrote:
> >
> > > Darren,
> > >
> > > I tried to reproduce this error and I cannot.
> > >
> > > How are you passing parameters to the second report? Are you using the
> > > Jump To navigation?
> > >
> > > Andy Potter
> > >
> > >
report links to another report based upon the contents of the cell which is
selected and passes the cell content and the 2 date parameters to the next
report.
Now this is all fine however when the date is passed to the second report it
appears as non en-GB. i.e. I am passing 07/01/2006 (7th of January 2006)
which on the second report becomes 1st July 2006. Passing a date such as the
14th January returns an invalid date error (no 14th month).
I have got around the error by accepting the date into the second report as
a string, however the dates appear (from my perspective) incorrectly
formatted and I can't have a datetime picker.
Incidentally if I change the second reports parameter back to datetime the
date pickers appears correctly and work as expected. Both reports are
formatted en-GB.
Does any one know where I am going wrong?
--
Regards
Darren FranceWhat is the language of the Report set to?
Have you tried explicitly converting the value passed in the Jump To.
ie =Convert.ToDateTime(Parameters!DateParam.Value) ?
Andy Potter|||If I change the parameter back to datetime (with or without the explicit
convert) I get the following error with the dates 08/01/2006 and 15/01/2006
(8th and 15th January:
The value provided for the report parameter 'UserEndDate' is not valid for
its type. (rsReportParameterTypeMismatch)
All reports are set to English (United Kingdom)
XML: <Language>en-GB</Language>
I should have pointed out (my apologies for not doing so) is that I am using
Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00
(2005). Deploying to a server with Reporting Services 2005 but with SQL 7.0
(SP4) as both the report database host and data source.
Regards
Darren France
"Potter" wrote:
> What is the language of the Report set to?
> Have you tried explicitly converting the value passed in the Jump To.
> ie => Convert.ToDateTime(Parameters!DateParam.Value) ?
> Andy Potter
>|||Darren,
I tried to reproduce this error and I cannot.
How are you passing parameters to the second report? Are you using the
Jump To navigation?
Andy Potter|||I am using jump to navigation, and passing: =Parameters!<parameter>.Value
I have also tried the convert.todate and also splitting the date into it's
constituant parts (day, month, year) and converting back to a date. If I add
textboxes with the parameter(s) value in it, it is correct on the first
report but appears in the wrong format on the second report (which is what I
expected but wanked to make sure it was not the date time picker).
I have also explicitly converted the date parameter in the stored procedure
- convert(smalldatetime, <parameter>, 103). But given that I am not passing a
date back as part of the query, and the reports work individually (i.e.
without the parameter passing) I don't think this affecting the results.
I have just double checked that the regional settings of both my machine and
the server (data source and report source) are correct.
I have noticed that previewing the report within the designer errors even on
the source report. I have been through the options in visual studio and
provided I avoid clicking on the 'data customisation ui' which results in a
nice error and a restart of visual studio, I personally cannot find anything
which could be causing this and I am out of ideas.
Regards
Darren France
"Potter" wrote:
> Darren,
> I tried to reproduce this error and I cannot.
> How are you passing parameters to the second report? Are you using the
> Jump To navigation?
> Andy Potter
>|||I set up the same scenario without a problem.
2 Reports. Each have a parameter called gbdate and a single textbox
with a value of parameters!gbdate.value. The first report has a
navigation jump to to the second report, passing the param value to the
second report. Both reports have a language of en-GB.
One thing I did notice. Unless you have your browser language set to
en-GB, you won't see the date value in the parameter selection textbox
in the dd/mm/yyyy format.
Andy Potter|||I ran into the same problems with the 2000 SP 2 version of RS. My only
solution was to include custom code to every report (created an empty report
with the code so I can use copy + paste) and run the code on all passed
parameters. it was like:
Type in the "Code" section of report properties:
Public Function GetISODate(tmDate as DateTime)
return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
End Function
and used it on the navigation:
=Code.GetISODate(Parameters!<parameter>.Value)
It seems that the ISO formatted date (year-month-day) will be parsed the
same by every culture.
"Darren France" wrote:
> I am using jump to navigation, and passing: =Parameters!<parameter>.Value
> I have also tried the convert.todate and also splitting the date into it's
> constituant parts (day, month, year) and converting back to a date. If I add
> textboxes with the parameter(s) value in it, it is correct on the first
> report but appears in the wrong format on the second report (which is what I
> expected but wanked to make sure it was not the date time picker).
> I have also explicitly converted the date parameter in the stored procedure
> - convert(smalldatetime, <parameter>, 103). But given that I am not passing a
> date back as part of the query, and the reports work individually (i.e.
> without the parameter passing) I don't think this affecting the results.
> I have just double checked that the regional settings of both my machine and
> the server (data source and report source) are correct.
> I have noticed that previewing the report within the designer errors even on
> the source report. I have been through the options in visual studio and
> provided I avoid clicking on the 'data customisation ui' which results in a
> nice error and a restart of visual studio, I personally cannot find anything
> which could be causing this and I am out of ideas.
>
> --
>
> Regards
> Darren France
>
> "Potter" wrote:
> > Darren,
> >
> > I tried to reproduce this error and I cannot.
> >
> > How are you passing parameters to the second report? Are you using the
> > Jump To navigation?
> >
> > Andy Potter
> >
> >|||I have the same problem. One report has default datetime parameters. It uses
the navigation feature to call a second report, with the same parameters
passed to the second report. In the designer this works fine but when
deployed the second report crashes with an rsReportParameterTypeMismatch
error.
This only happens when the dates being passed are non/ambiguous US/English.
In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly 6-Jan-06
works, 16-Jan-06 doesn't.
So it's not really a type mismatch error but a dataconversion issue.
Any help welcome!
Bob
--
Bob
"Potter" wrote:
> I set up the same scenario without a problem.
> 2 Reports. Each have a parameter called gbdate and a single textbox
> with a value of parameters!gbdate.value. The first report has a
> navigation jump to to the second report, passing the param value to the
> second report. Both reports have a language of en-GB.
> One thing I did notice. Unless you have your browser language set to
> en-GB, you won't see the date value in the parameter selection textbox
> in the dd/mm/yyyy format.
> Andy Potter
>|||Works a treat, thank you very much.
p.s.
I would like to apologise and point out that there was a typing mistake in
my last post, it should have read 'but wanted', I was not sharing my personal
hobbies with the newsgroup. Unfortunately I cannot find a regional setting to
blame that on!
--
Regards
Darren France
"GreyMana" wrote:
> I ran into the same problems with the 2000 SP 2 version of RS. My only
> solution was to include custom code to every report (created an empty report
> with the code so I can use copy + paste) and run the code on all passed
> parameters. it was like:
> Type in the "Code" section of report properties:
> Public Function GetISODate(tmDate as DateTime)
> return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
> End Function
> and used it on the navigation:
> =Code.GetISODate(Parameters!<parameter>.Value)
> It seems that the ISO formatted date (year-month-day) will be parsed the
> same by every culture.
> "Darren France" wrote:
> > I am using jump to navigation, and passing: =Parameters!<parameter>.Value
> >
> > I have also tried the convert.todate and also splitting the date into it's
> > constituant parts (day, month, year) and converting back to a date. If I add
> > textboxes with the parameter(s) value in it, it is correct on the first
> > report but appears in the wrong format on the second report (which is what I
> > expected but wanked to make sure it was not the date time picker).
> >
> > I have also explicitly converted the date parameter in the stored procedure
> > - convert(smalldatetime, <parameter>, 103). But given that I am not passing a
> > date back as part of the query, and the reports work individually (i.e.
> > without the parameter passing) I don't think this affecting the results.
> >
> > I have just double checked that the regional settings of both my machine and
> > the server (data source and report source) are correct.
> >
> > I have noticed that previewing the report within the designer errors even on
> > the source report. I have been through the options in visual studio and
> > provided I avoid clicking on the 'data customisation ui' which results in a
> > nice error and a restart of visual studio, I personally cannot find anything
> > which could be causing this and I am out of ideas.
> >
> >
> > --
> >
> >
> > Regards
> >
> > Darren France
> >
> >
> > "Potter" wrote:
> >
> > > Darren,
> > >
> > > I tried to reproduce this error and I cannot.
> > >
> > > How are you passing parameters to the second report? Are you using the
> > > Jump To navigation?
> > >
> > > Andy Potter
> > >
> > >|||Hi,
what do you mean by "Used it on the Navigation"?
thanks
Matt
"GreyMana" wrote:
> I ran into the same problems with the 2000 SP 2 version of RS. My only
> solution was to include custom code to every report (created an empty report
> with the code so I can use copy + paste) and run the code on all passed
> parameters. it was like:
> Type in the "Code" section of report properties:
> Public Function GetISODate(tmDate as DateTime)
> return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
> End Function
> and used it on the navigation:
> =Code.GetISODate(Parameters!<parameter>.Value)
> It seems that the ISO formatted date (year-month-day) will be parsed the
> same by every culture.
> "Darren France" wrote:
> > I am using jump to navigation, and passing: =Parameters!<parameter>.Value
> >
> > I have also tried the convert.todate and also splitting the date into it's
> > constituant parts (day, month, year) and converting back to a date. If I add
> > textboxes with the parameter(s) value in it, it is correct on the first
> > report but appears in the wrong format on the second report (which is what I
> > expected but wanked to make sure it was not the date time picker).
> >
> > I have also explicitly converted the date parameter in the stored procedure
> > - convert(smalldatetime, <parameter>, 103). But given that I am not passing a
> > date back as part of the query, and the reports work individually (i.e.
> > without the parameter passing) I don't think this affecting the results.
> >
> > I have just double checked that the regional settings of both my machine and
> > the server (data source and report source) are correct.
> >
> > I have noticed that previewing the report within the designer errors even on
> > the source report. I have been through the options in visual studio and
> > provided I avoid clicking on the 'data customisation ui' which results in a
> > nice error and a restart of visual studio, I personally cannot find anything
> > which could be causing this and I am out of ideas.
> >
> >
> > --
> >
> >
> > Regards
> >
> > Darren France
> >
> >
> > "Potter" wrote:
> >
> > > Darren,
> > >
> > > I tried to reproduce this error and I cannot.
> > >
> > > How are you passing parameters to the second report? Are you using the
> > > Jump To navigation?
> > >
> > > Andy Potter
> > >
> > >
Datetime in Where clause
I have a simple stored procedure that updates the loggedout field, which is
a
datetime field, in a table based on the user id and login date (which is als
o
a datetime field). Since a user can login multiple times throughout the day,
I need the Where clause to include the time, which is part of the datetime
being passed in.
When I run the following sp and pass in a datetime field, for example
5/15/2005 1:12:22 PM, the table never updates, even though there is a field
with that date/time in it.
PROCEDURE dbo.UpdateLoginActivity
(
@.fldLogoutDate datetime,
@.fldUserId int,
@.fldLoginDate datetime
)
AS
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
How can I use a datetime field effectively in a Where clause, so that it
finds the exact row based on the date & time?datetime stores milliseconds:
select getdate()
---
2006-05-15 16:31:33.750
(1 row(s) affected)
YOu must either provide milliseconds in your parameter or modify your
update
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )|||It worked fine for me.
Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
PM ?
Richard wrote:
> I have a simple stored procedure that updates the loggedout field, which i
s a
> datetime field, in a table based on the user id and login date (which is a
lso
> a datetime field). Since a user can login multiple times throughout the da
y,
> I need the Where clause to include the time, which is part of the datetime
> being passed in.
> When I run the following sp and pass in a datetime field, for example
> 5/15/2005 1:12:22 PM, the table never updates, even though there is a fiel
d
> with that date/time in it.
> PROCEDURE dbo.UpdateLoginActivity
> (
> @.fldLogoutDate datetime,
> @.fldUserId int,
> @.fldLoginDate datetime
> )
> AS
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
> How can I use a datetime field effectively in a Where clause, so that it
> finds the exact row based on the date & time?|||That worked! Thanks Alexander.
"Alexander Kuznetsov" wrote:
> datetime stores milliseconds:
> select getdate()
> ---
> 2006-05-15 16:31:33.750
> (1 row(s) affected)
> YOu must either provide milliseconds in your parameter or modify your
> update
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
> AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )
>|||Yes, that is the correct date. See Alexander's post for the syntax, which
worked well for me. Thanks.
"tjolliffe@.hotmail.com" wrote:
> It worked fine for me.
> Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
> PM ?
>
> Richard wrote:
>
a
datetime field, in a table based on the user id and login date (which is als
o
a datetime field). Since a user can login multiple times throughout the day,
I need the Where clause to include the time, which is part of the datetime
being passed in.
When I run the following sp and pass in a datetime field, for example
5/15/2005 1:12:22 PM, the table never updates, even though there is a field
with that date/time in it.
PROCEDURE dbo.UpdateLoginActivity
(
@.fldLogoutDate datetime,
@.fldUserId int,
@.fldLoginDate datetime
)
AS
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
How can I use a datetime field effectively in a Where clause, so that it
finds the exact row based on the date & time?datetime stores milliseconds:
select getdate()
---
2006-05-15 16:31:33.750
(1 row(s) affected)
YOu must either provide milliseconds in your parameter or modify your
update
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )|||It worked fine for me.
Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
PM ?
Richard wrote:
> I have a simple stored procedure that updates the loggedout field, which i
s a
> datetime field, in a table based on the user id and login date (which is a
lso
> a datetime field). Since a user can login multiple times throughout the da
y,
> I need the Where clause to include the time, which is part of the datetime
> being passed in.
> When I run the following sp and pass in a datetime field, for example
> 5/15/2005 1:12:22 PM, the table never updates, even though there is a fiel
d
> with that date/time in it.
> PROCEDURE dbo.UpdateLoginActivity
> (
> @.fldLogoutDate datetime,
> @.fldUserId int,
> @.fldLoginDate datetime
> )
> AS
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
> How can I use a datetime field effectively in a Where clause, so that it
> finds the exact row based on the date & time?|||That worked! Thanks Alexander.
"Alexander Kuznetsov" wrote:
> datetime stores milliseconds:
> select getdate()
> ---
> 2006-05-15 16:31:33.750
> (1 row(s) affected)
> YOu must either provide milliseconds in your parameter or modify your
> update
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
> AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )
>|||Yes, that is the correct date. See Alexander's post for the syntax, which
worked well for me. Thanks.
"tjolliffe@.hotmail.com" wrote:
> It worked fine for me.
> Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
> PM ?
>
> Richard wrote:
>
Thursday, March 8, 2012
DateTime Error
I have a function that is based aounr the input of parameters. The last remaing issue is that I am required to enter the data into the parameter field as mm/dd/yyyy. I want to be able to enter the data as dd/mm/yyyy. I have tried to use
WHERE (CONVERT(datetime,src_terrier.datadate,103) = @.dt_src_date) AND..........
But this just throws an error "Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0
Error converting data type nvarchar to datetime."
The execution line I am using is
USE [DashboardSQL-2K5]
GO
DECLARE @.return_value int
EXEC @.return_value = [dbo].[spWTRalldatareportsummary]
@.dt_src_date = N'28/04/2006',
@.chr_div = NULL,
@.vch_portfolio_no = NULL,
@.vch_prop_cat = NULL
SELECT 'Return Value' = @.return_value
GO
Anybody got any ideas as to what I have done wrong? I have also tried it without the N just before the date and get a varchar version of the same error.
Thanks in advance
You should convert src_terrier.datadate not to datetime (it IS datedime, after all :) ), but to varchar. And no, you don't need N in this case.
HTH :)
|||Hi,first of all, this is right, you don′t need to N the datestring. If you want to pass the "datestring as a string, I would rather prefer passing it as ISO date e.g. 20040101 rather than in a formatted way. Then you need to convert it to VARCHAR if you use a format / convert function. The isu code for formatting to 20040101 is 112, so this should be something like
WHERE (CONVERT(VARCHAR(10),src_terrier.datadate,112) = @.dt_src_date) AND.......... The other option you could use, which would normally better keep track of an index rather than convert all the datetimes stored in your table. Use datediff, it should be 0 if you compare by days and the record is of the same day as the imput paramter. HTH, Jens Suessmeyer. http://www.sqlserver2005.de
Is src_terrier.datadate column smalldatetime/datetime? If not you should first change it to one of the datetime data types so you can manipulate the data efficiently and perform searches in meaningful manner. As for the input, you can use the ISO unseparated format as mentioned so the code works under any language settings. If you are going to pass the value in a specific format then perform a conversion in the code like:
convert(datetime, @.dt_src_date, 101 )
convert(datetime, @.dt_src_date, 103)
If you try there and it doesn't resolve the problem then please post a sample script with DDL & data so it is easy to see what the schema looks like and the actual code.
Wednesday, March 7, 2012
Datetime calculation help
Hi Everyone,
I have got a problem with date calculation. I have a procedure that all
me to insert date into a Table based on user input. The input is a Event
Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w
prior to the
event date as the reminder
My question is how do I calculate prior w of a certain Date.. e.g Event
Date = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Below is my procedure:
CREATE PROCEDURE EventReminder
@.DocketID int,
@.EventName varchar(50),
@.Reminder int,
@.EventNumber int,
@.EventDate varchar(50)
AS
--Declare variables
Declare @.EventStartNum int,
@.EventReminderNum int,
@.EventDate1 datetime,
@.EventNum int
--Initialize the Variables
set @.EventStartNum = 0
set @.EventReminderNum = 0
set @.EventNum = -1
--Delete the Reminder if the DocketID already exist
delete from reminder where DocketID = @.DocketID
--Start the loop
while @.EventStartNum < @.EventNumber
Begin --Start Begin
set @.EventStartNum = @.EventStartNum + 1
--Wly Reminder
if @.EventNumber = 1
begin
while @.Reminder >
@.EventReminderNum
begin
--Increment of the w
set @.EventReminderNum =
@.EventReminderNum + 1
set @.EventDate1 = DATEADD(w,
@.EventReminderNum, @.EventDate)
insert into Reminder
(DocketID, EventDate, EventName, Reminder)
Values
(@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
set @.EventNum = @.EventNum - 1
end
end
--print 'The counter is ' +
convert(varchar(50),@.EventDate1,101)
end --End Begin
GOUse function DATEADD.
Example:
select dateadd(ww, -1, '20050114')
go
AMB
"Roplab" wrote:
> Hi Everyone,
> I have got a problem with date calculation. I have a procedure that al
l
> me to insert date into a Table based on user input. The input is a Event
> Date and Reminder
> Example: if the user Enter an Event Date and choose to a reminder for a
> certain event... I need to calculate a date that will be a w prior to t
he
> event date as the reminder
> My question is how do I calculate prior w of a certain Date.. e.g Even
t
> Date = 01/14/2005 I want the reminder to be calculate has
> Reminder=01/07/2005
> Below is my procedure:
> CREATE PROCEDURE EventReminder
> @.DocketID int,
> @.EventName varchar(50),
> @.Reminder int,
> @.EventNumber int,
> @.EventDate varchar(50)
> AS
> --Declare variables
> Declare @.EventStartNum int,
> @.EventReminderNum int,
> @.EventDate1 datetime,
> @.EventNum int
> --Initialize the Variables
> set @.EventStartNum = 0
> set @.EventReminderNum = 0
> set @.EventNum = -1
>
> --Delete the Reminder if the DocketID already exist
> delete from reminder where DocketID = @.DocketID
> --Start the loop
> while @.EventStartNum < @.EventNumber
> Begin --Start Begin
> set @.EventStartNum = @.EventStartNum + 1
> --Wly Reminder
> if @.EventNumber = 1
> begin
> while @.Reminder >
> @.EventReminderNum
> begin
> --Increment of the w
> set @.EventReminderNum =
> @.EventReminderNum + 1
> set @.EventDate1 = DATEADD(wee
k,
> @.EventReminderNum, @.EventDate)
> insert into Reminder
> (DocketID, EventDate, EventName, Reminder)
> Values
> (@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
> set @.EventNum = @.EventNum -
1
> end
> end
> --print 'The counter is ' +
> convert(varchar(50),@.EventDate1,101)
> end --End Begin
> GO
>
>
I have got a problem with date calculation. I have a procedure that all
me to insert date into a Table based on user input. The input is a Event
Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w
prior to theevent date as the reminder
My question is how do I calculate prior w
of a certain Date.. e.g EventDate = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Below is my procedure:
CREATE PROCEDURE EventReminder
@.DocketID int,
@.EventName varchar(50),
@.Reminder int,
@.EventNumber int,
@.EventDate varchar(50)
AS
--Declare variables
Declare @.EventStartNum int,
@.EventReminderNum int,
@.EventDate1 datetime,
@.EventNum int
--Initialize the Variables
set @.EventStartNum = 0
set @.EventReminderNum = 0
set @.EventNum = -1
--Delete the Reminder if the DocketID already exist
delete from reminder where DocketID = @.DocketID
--Start the loop
while @.EventStartNum < @.EventNumber
Begin --Start Begin
set @.EventStartNum = @.EventStartNum + 1
--W
ly Reminderif @.EventNumber = 1
begin
while @.Reminder >
@.EventReminderNum
begin
--Increment of the w
set @.EventReminderNum =
@.EventReminderNum + 1
set @.EventDate1 = DATEADD(w
,@.EventReminderNum, @.EventDate)
insert into Reminder
(DocketID, EventDate, EventName, Reminder)
Values
(@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
set @.EventNum = @.EventNum - 1
end
end
--print 'The counter is ' +
convert(varchar(50),@.EventDate1,101)
end --End Begin
GOUse function DATEADD.
Example:
select dateadd(ww, -1, '20050114')
go
AMB
"Roplab" wrote:
> Hi Everyone,
> I have got a problem with date calculation. I have a procedure that al
l
> me to insert date into a Table based on user input. The input is a Event
> Date and Reminder
> Example: if the user Enter an Event Date and choose to a reminder for a
> certain event... I need to calculate a date that will be a w
prior to the
> event date as the reminder
> My question is how do I calculate prior w
of a certain Date.. e.g Event
> Date = 01/14/2005 I want the reminder to be calculate has
> Reminder=01/07/2005
> Below is my procedure:
> CREATE PROCEDURE EventReminder
> @.DocketID int,
> @.EventName varchar(50),
> @.Reminder int,
> @.EventNumber int,
> @.EventDate varchar(50)
> AS
> --Declare variables
> Declare @.EventStartNum int,
> @.EventReminderNum int,
> @.EventDate1 datetime,
> @.EventNum int
> --Initialize the Variables
> set @.EventStartNum = 0
> set @.EventReminderNum = 0
> set @.EventNum = -1
>
> --Delete the Reminder if the DocketID already exist
> delete from reminder where DocketID = @.DocketID
> --Start the loop
> while @.EventStartNum < @.EventNumber
> Begin --Start Begin
> set @.EventStartNum = @.EventStartNum + 1
> --W
ly Reminder> if @.EventNumber = 1
> begin
> while @.Reminder >
> @.EventReminderNum
> begin
> --Increment of the w
> set @.EventReminderNum =
> @.EventReminderNum + 1
> set @.EventDate1 = DATEADD(wee
k,
> @.EventReminderNum, @.EventDate)
> insert into Reminder
> (DocketID, EventDate, EventName, Reminder)
> Values
> (@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
> set @.EventNum = @.EventNum -
1
> end
> end
> --print 'The counter is ' +
> convert(varchar(50),@.EventDate1,101)
> end --End Begin
> GO
>
>
Friday, February 17, 2012
Datediff giving output based on year...
Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar Ganguly
Hi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>
|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft .com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
showing[vbcol=seagreen]
give[vbcol=seagreen]
|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar Ganguly
Hi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>
|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft .com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
showing[vbcol=seagreen]
give[vbcol=seagreen]
|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
Datediff giving output based on year...
Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1
as the result. But is it possible to get the difference in year purely base
d on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me
1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar GangulyHi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only wit
h Year parameter. And it won't be possible for me to know the year differenc
e as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft.com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
>
showing[vbcol=seagreen]
give[vbcol=seagreen]|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1
as the result. But is it possible to get the difference in year purely base
d on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me
1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar GangulyHi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only wit
h Year parameter. And it won't be possible for me to know the year differenc
e as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft.com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
>
showing[vbcol=seagreen]
give[vbcol=seagreen]|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
Datediff giving output based on year...
Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar GangulyHi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> > Hi Everyone,
> > Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> > For Example Difference between 26 June 2002 and 21 June 2004 should give
> me 1 instead of 2.
> > Thanx in advance for the help.
> >
> > Regards,
> > Dipankar Ganguly
>
>|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft.com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
> > Hi
> >
> > Maybe something on the lines of:
> >
> > DECLARE @.StartDate Datetime
> > DECLARE @.EndDate Datetime
> >
> > SET @.StartDate = '20020626'
> > SET @.EndDate = '20040621'
> > SELECT MAX([NoYears])
> > FROM ( SELECT 1 as [NoYears] UNION ALL
> > SELECT 2 UNION ALL
> > SELECT 3 UNION ALL
> > SELECT 4 UNION ALL
> > SELECT 5 ) A
> > WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> >
> > John
> >
> > "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> > message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> > > Hi Everyone,
> > > Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is
showing
> > 1 as the result. But is it possible to get the difference in year purely
> > based on date and not only on the Year part of the date?
> > > For Example Difference between 26 June 2002 and 21 June 2004 should
give
> > me 1 instead of 2.
> > > Thanx in advance for the help.
> > >
> > > Regards,
> > > Dipankar Ganguly
> >
> >
> >|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar GangulyHi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> > Hi Everyone,
> > Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> > For Example Difference between 26 June 2002 and 21 June 2004 should give
> me 1 instead of 2.
> > Thanx in advance for the help.
> >
> > Regards,
> > Dipankar Ganguly
>
>|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft.com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
> > Hi
> >
> > Maybe something on the lines of:
> >
> > DECLARE @.StartDate Datetime
> > DECLARE @.EndDate Datetime
> >
> > SET @.StartDate = '20020626'
> > SET @.EndDate = '20040621'
> > SELECT MAX([NoYears])
> > FROM ( SELECT 1 as [NoYears] UNION ALL
> > SELECT 2 UNION ALL
> > SELECT 3 UNION ALL
> > SELECT 4 UNION ALL
> > SELECT 5 ) A
> > WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> >
> > John
> >
> > "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> > message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> > > Hi Everyone,
> > > Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is
showing
> > 1 as the result. But is it possible to get the difference in year purely
> > based on date and not only on the Year part of the date?
> > > For Example Difference between 26 June 2002 and 21 June 2004 should
give
> > me 1 instead of 2.
> > > Thanx in advance for the help.
> > >
> > > Regards,
> > > Dipankar Ganguly
> >
> >
> >|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
DateDiff calculating ages incorrectly.
Hi,
I am trying to use datediff to calculate a persons age, based on their date
of brith. I am using the following function:
(datediff(year,[DOB],getdate()))
The formula calculates the ages correctly for people whose birthday falls on
a day and month before today (getdate()), but for those with a birthday afte
r
today it adds an extra year on.
Anyone got any suggestions about how to correctly calculate ages using a
date of birth?Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the same
as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
bothe sets of dates...
To calculate Age, use the following:
Year(@.D2) - Year(@.D1)
- Case When Month(@.D2) > Month(@.D1) Or
(Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
Else 0 End
You could put this in UDF...
Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
RETURNS TinyInt
As
Begin
Declare @.Age SmallInt
Set @.Age = Year(@.CurDT) - Year(@.DOB) -
Case When Month(@.CurDT) < Month(@.DOB) Then 1
When Month(@.CurDT) > Month(@.DOB) Then 0
When Day(@.CurDT) < Day(@.DOB) Then 1
Else 0 End
Return @.Age
End
"Enterprise Andy" wrote:
> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||http://groups.google.ca/group/micro...49c2e
c8
AMB
"Enterprise Andy" wrote:
> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||Hi Andy,
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:10C0C6CD-965D-4608-A47E-E720ACE40DDE@.microsoft.com...
> Hi,
> I am trying to use datediff to calculate a persons age, based on their
> date
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
> on
> a day and month before today (getdate()), but for those with a birthday
> after
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?
Try this:
CREATE FUNCTION uf_YearsDifference (@.initialDate DATETIME,
@.finalDateDATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN
DATEADD(YEAR, DATEDIFF(YEAR, @.initialDate, @.finalDate), @.initialDate) >
@.finalDate
THEN DATEDIFF(YEAR, @.initialDate, @.finalDate) - 1
ELSE DATEDIFF(YEAR, @.initialDate, @.finalDate)
END
)
END
Andrea - www.absistemi.it|||Many thanks. Saved me a lot of trouble!!!
"CBretana" wrote:
> Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
> exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the sa
me
> as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
> bothe sets of dates...
> To calculate Age, use the following:
> Year(@.D2) - Year(@.D1)
> - Case When Month(@.D2) > Month(@.D1) Or
> (Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
> Else 0 End
> You could put this in UDF...
> Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
> RETURNS TinyInt
> As
> Begin
> Declare @.Age SmallInt
> Set @.Age = Year(@.CurDT) - Year(@.DOB) -
> Case When Month(@.CurDT) < Month(@.DOB) Then 1
> When Month(@.CurDT) > Month(@.DOB) Then 0
> When Day(@.CurDT) < Day(@.DOB) Then 1
> Else 0 End
> Return @.Age
> End
>
> "Enterprise Andy" wrote:
>
I am trying to use datediff to calculate a persons age, based on their date
of brith. I am using the following function:
(datediff(year,[DOB],getdate()))
The formula calculates the ages correctly for people whose birthday falls on
a day and month before today (getdate()), but for those with a birthday afte
r
today it adds an extra year on.
Anyone got any suggestions about how to correctly calculate ages using a
date of birth?Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the same
as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
bothe sets of dates...
To calculate Age, use the following:
Year(@.D2) - Year(@.D1)
- Case When Month(@.D2) > Month(@.D1) Or
(Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
Else 0 End
You could put this in UDF...
Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
RETURNS TinyInt
As
Begin
Declare @.Age SmallInt
Set @.Age = Year(@.CurDT) - Year(@.DOB) -
Case When Month(@.CurDT) < Month(@.DOB) Then 1
When Month(@.CurDT) > Month(@.DOB) Then 0
When Day(@.CurDT) < Day(@.DOB) Then 1
Else 0 End
Return @.Age
End
"Enterprise Andy" wrote:
> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||http://groups.google.ca/group/micro...49c2e
c8
AMB
"Enterprise Andy" wrote:
> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||Hi Andy,
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:10C0C6CD-965D-4608-A47E-E720ACE40DDE@.microsoft.com...
> Hi,
> I am trying to use datediff to calculate a persons age, based on their
> date
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
> on
> a day and month before today (getdate()), but for those with a birthday
> after
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?
Try this:
CREATE FUNCTION uf_YearsDifference (@.initialDate DATETIME,
@.finalDateDATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN
DATEADD(YEAR, DATEDIFF(YEAR, @.initialDate, @.finalDate), @.initialDate) >
@.finalDate
THEN DATEDIFF(YEAR, @.initialDate, @.finalDate) - 1
ELSE DATEDIFF(YEAR, @.initialDate, @.finalDate)
END
)
END
Andrea - www.absistemi.it|||Many thanks. Saved me a lot of trouble!!!
"CBretana" wrote:
> Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
> exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the sa
me
> as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
> bothe sets of dates...
> To calculate Age, use the following:
> Year(@.D2) - Year(@.D1)
> - Case When Month(@.D2) > Month(@.D1) Or
> (Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
> Else 0 End
> You could put this in UDF...
> Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
> RETURNS TinyInt
> As
> Begin
> Declare @.Age SmallInt
> Set @.Age = Year(@.CurDT) - Year(@.DOB) -
> Case When Month(@.CurDT) < Month(@.DOB) Then 1
> When Month(@.CurDT) > Month(@.DOB) Then 0
> When Day(@.CurDT) < Day(@.DOB) Then 1
> Else 0 End
> Return @.Age
> End
>
> "Enterprise Andy" wrote:
>
Subscribe to:
Posts (Atom)