I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Thursday, March 29, 2012
date range parameter
i have a need by date in my report. i would like to create a date parameter
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>
Tuesday, March 27, 2012
date query question
Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.
date query question
Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
Paul G
Software engineer.
found solution!
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
Paul G
Software engineer.
found solution!
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.
date query question
Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.
Date query
I have a query which finds records that are >= todays date. The user has
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
ThanksLook up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
--
Anith|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
ThanksLook up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
--
Anith|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com
Date query
I have a query which finds records that are >= todays date. The user has
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
Thanks
Look up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
Anith
|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
Thanks
Look up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
Anith
|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com
Date query
I have a query which finds records that are >= todays date. The user has
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
ThanksLook up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
Anith|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
ThanksLook up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
Anith|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com
Thursday, March 22, 2012
Date Parameter Question
Hi all,
I am still kind of new at this...
I am trying to have a date parameter that the user selects a month and it
brings back all the data for that month. Say the users picks August, the
report comes back for all the data for the month of August.
I hope I explained it well enough.
Any help would be great.
Thanks in advance,
KerrieConsider creating a parameter (called @.Month for example) and enter in the
"Available Values" (Report -> Report Parameters) the following:
Jan 1
Feb 2
Mar 3
etc, for each month. Then in your query use the following on your date
column:
SELECT
*
FROM
tblData
WHERE
DATEPART(mm, dateStart) = @.Month
to explain:
DATEPART(mm, <date>) returns the month of a date, e.g. for the 16th of July
it would return "7" for July.
Hope that helps,
-Geoff R G Williams
Primal Blaze Ltd.
"KS" <KS@.discussions.microsoft.com> wrote in message
news:303428AC-348D-45C1-BBCD-9EC5C032AEAE@.microsoft.com...
> Hi all,
> I am still kind of new at this...
> I am trying to have a date parameter that the user selects a month and it
> brings back all the data for that month. Say the users picks August, the
> report comes back for all the data for the month of August.
> I hope I explained it well enough.
> Any help would be great.
> Thanks in advance,
> Kerrie
>
>
I am still kind of new at this...
I am trying to have a date parameter that the user selects a month and it
brings back all the data for that month. Say the users picks August, the
report comes back for all the data for the month of August.
I hope I explained it well enough.
Any help would be great.
Thanks in advance,
KerrieConsider creating a parameter (called @.Month for example) and enter in the
"Available Values" (Report -> Report Parameters) the following:
Jan 1
Feb 2
Mar 3
etc, for each month. Then in your query use the following on your date
column:
SELECT
*
FROM
tblData
WHERE
DATEPART(mm, dateStart) = @.Month
to explain:
DATEPART(mm, <date>) returns the month of a date, e.g. for the 16th of July
it would return "7" for July.
Hope that helps,
-Geoff R G Williams
Primal Blaze Ltd.
"KS" <KS@.discussions.microsoft.com> wrote in message
news:303428AC-348D-45C1-BBCD-9EC5C032AEAE@.microsoft.com...
> Hi all,
> I am still kind of new at this...
> I am trying to have a date parameter that the user selects a month and it
> brings back all the data for that month. Say the users picks August, the
> report comes back for all the data for the month of August.
> I hope I explained it well enough.
> Any help would be great.
> Thanks in advance,
> Kerrie
>
>
Date Parameter Problem! Please help!
I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2... and so on.
There is not a Period column to reference the fields to and I am not sure how to
use a date range and apply it to one parameter. Is there a way to use
the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I did get some advice on using an if statement to reference the parameter but I receive the error message that I must declar the scalar value @.Period.
Here is the simple query I used to just see if the query would run based on the parameter(be nice...I am a newbie to SQL and RS)
In my Data tab:
IF @.Period = 1 SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN '03/06/2006' AND '04/02/2006'
For the report parameter:
Label Value
Period 1 1
I have used the IIF expression and such but this is just a different situation and I am pulling my hair out trying to find an answer.
Any help is greatly appreciated!
I
I'm sure there are a few different ways to approach this, but this could be one.
1) Create a parameter called @.PeriodStart like you did. Make this a datetime parameter. The label for this parameter could be a number (1, 2, 3.. n) and the value would be a date. You could hard-code the periods in through the Available Values section and select non-queried. For label you could put 1 and for value '1/1/2006' and 2 then 1/7/2006... or whatever you wanted to define for your period start dates. (If you wanted to make this more dynamic, you could create a dataset that somehow used sql functions to get these dates... You could create a DateDimension table or a Period table..)
2) Create a dataset called something like PeriodEndDataSet. The query could be something like this: SELECT DATEADD(MONTH, 1, @.PeriodStart) AS PeriodEndDate. (you can make the 1 month be anything you wanted).
3) Create a second parameter called @.PeriodEnd. You would want this to be a hidden parameter. In the Parameter editor Select Queried from available values section. Select your dataset and label & value fields. Also set the default value to come from the same query.
4) In the query for your report (the main data set), you can create your query to do this:
SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN @.PeriodStart AND @.PeriodEnd
That seems like a lot of work... I would almost recommend creating a lookup/dimension table to store this period information for you. You could then use this across many reports.
Regards,
Dan
date parameter input question
is there a way that once a user enters a date parameter in that i can attach
a time to the end of the date.
eg.
user enters 11/11/05 in the parameter field. i want the date submitted to
the query to be 11/11/05 12:00:00 AM without them having to enter the time.In RS 2000 it automatically puts those times in. I had a case where I needed
it to be from 7 am to 7 am. So I set the parameter to text. Then you map the
query parameter to an expression.
= Parameters!MyParam.Value & " 7:00"
In 2005 there is a calendar control. If no time is selected it doesn't show
a time. If you have it as a date/time you can also use expressions to strip
the time portion. Look at VB date/time formating.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.matt.com> wrote in message
news:uX7uf2t5FHA.3276@.TK2MSFTNGP10.phx.gbl...
> is there a way that once a user enters a date parameter in that i can
> attach
> a time to the end of the date.
> eg.
> user enters 11/11/05 in the parameter field. i want the date submitted to
> the query to be 11/11/05 12:00:00 AM without them having to enter the
> time.
>
a time to the end of the date.
eg.
user enters 11/11/05 in the parameter field. i want the date submitted to
the query to be 11/11/05 12:00:00 AM without them having to enter the time.In RS 2000 it automatically puts those times in. I had a case where I needed
it to be from 7 am to 7 am. So I set the parameter to text. Then you map the
query parameter to an expression.
= Parameters!MyParam.Value & " 7:00"
In 2005 there is a calendar control. If no time is selected it doesn't show
a time. If you have it as a date/time you can also use expressions to strip
the time portion. Look at VB date/time formating.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.matt.com> wrote in message
news:uX7uf2t5FHA.3276@.TK2MSFTNGP10.phx.gbl...
> is there a way that once a user enters a date parameter in that i can
> attach
> a time to the end of the date.
> eg.
> user enters 11/11/05 in the parameter field. i want the date submitted to
> the query to be 11/11/05 12:00:00 AM without them having to enter the
> time.
>
Date Parameter ERROR
My RS has two date parameters setup as a date data type. If the user was to
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.
Wednesday, March 21, 2012
Date Parameter
hi All
I am quiet new to sql reporting so would really appreciate some help. I am
using this with CRM 3.0
I want to do a report where user can select a to date and a from date to see
all completed phone calls. However both the to and the from are based on one
field called actualend.
Actual end is a date time field but in the layout tab i have used the function
=Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
into just a dd-mm-yy value. Now i want to create a paramater where user can
select a start date and an end date to report. Can someone please guide me
how to go about this.
I have tried using the @.>=actualend parameter under the data tab. It works
for that but does not work under the reports tab. I can see it as a parameter
under report - report parameters but when i run the report with a date say
14/05/07 i get the error as
An error occured during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the data set CRM_MSCRM
Arithmetic overflow error converting expression to data type datetime.
I would really appreciate some help.
Thanks and Regards
Ridhimai have managed to go one step beyond and set up a parameter which uses the
value as mm/dd/yy... how can i change it to so user can enter a value in
dd-mm-yy format. the parameter type is strng.
"Ridhima Sood" wrote:
> hi All
> I am quiet new to sql reporting so would really appreciate some help. I am
> using this with CRM 3.0
> I want to do a report where user can select a to date and a from date to see
> all completed phone calls. However both the to and the from are based on one
> field called actualend.
> Actual end is a date time field but in the layout tab i have used the function
> =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> into just a dd-mm-yy value. Now i want to create a paramater where user can
> select a start date and an end date to report. Can someone please guide me
> how to go about this.
> I have tried using the @.>=actualend parameter under the data tab. It works
> for that but does not work under the reports tab. I can see it as a parameter
> under report - report parameters but when i run the report with a date say
> 14/05/07 i get the error as
> An error occured during local report processing.
> An error has occurred during report processing.
> Cannot read the next data row for the data set CRM_MSCRM
> Arithmetic overflow error converting expression to data type datetime.
> I would really appreciate some help.
> Thanks and Regards
> Ridhima|||That option is not available instead you can use date picker or seperate all
three in 3 parameters like mm, dd, yyyy and then concatenate..
Amarnath
"Ridhima Sood" wrote:
> i have managed to go one step beyond and set up a parameter which uses the
> value as mm/dd/yy... how can i change it to so user can enter a value in
> dd-mm-yy format. the parameter type is strng.
> "Ridhima Sood" wrote:
> > hi All
> >
> > I am quiet new to sql reporting so would really appreciate some help. I am
> > using this with CRM 3.0
> >
> > I want to do a report where user can select a to date and a from date to see
> > all completed phone calls. However both the to and the from are based on one
> > field called actualend.
> >
> > Actual end is a date time field but in the layout tab i have used the function
> > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > select a start date and an end date to report. Can someone please guide me
> > how to go about this.
> >
> > I have tried using the @.>=actualend parameter under the data tab. It works
> > for that but does not work under the reports tab. I can see it as a parameter
> > under report - report parameters but when i run the report with a date say
> > 14/05/07 i get the error as
> > An error occured during local report processing.
> > An error has occurred during report processing.
> > Cannot read the next data row for the data set CRM_MSCRM
> > Arithmetic overflow error converting expression to data type datetime.
> >
> > I would really appreciate some help.
> >
> > Thanks and Regards
> > Ridhima|||hi Amarnath
When i use the date picker it doesnt work either.. displays and error:(
"Amarnath" wrote:
> That option is not available instead you can use date picker or seperate all
> three in 3 parameters like mm, dd, yyyy and then concatenate..
> Amarnath
> "Ridhima Sood" wrote:
> > i have managed to go one step beyond and set up a parameter which uses the
> > value as mm/dd/yy... how can i change it to so user can enter a value in
> > dd-mm-yy format. the parameter type is strng.
> >
> > "Ridhima Sood" wrote:
> >
> > > hi All
> > >
> > > I am quiet new to sql reporting so would really appreciate some help. I am
> > > using this with CRM 3.0
> > >
> > > I want to do a report where user can select a to date and a from date to see
> > > all completed phone calls. However both the to and the from are based on one
> > > field called actualend.
> > >
> > > Actual end is a date time field but in the layout tab i have used the function
> > > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > > select a start date and an end date to report. Can someone please guide me
> > > how to go about this.
> > >
> > > I have tried using the @.>=actualend parameter under the data tab. It works
> > > for that but does not work under the reports tab. I can see it as a parameter
> > > under report - report parameters but when i run the report with a date say
> > > 14/05/07 i get the error as
> > > An error occured during local report processing.
> > > An error has occurred during report processing.
> > > Cannot read the next data row for the data set CRM_MSCRM
> > > Arithmetic overflow error converting expression to data type datetime.
> > >
> > > I would really appreciate some help.
> > >
> > > Thanks and Regards
> > > Ridhima|||What I meant was you can use date picker for just picking dates in any ormat,
because this type of formatting upfront is not possible.
Amarnath
"Ridhima Sood" wrote:
> hi Amarnath
> When i use the date picker it doesnt work either.. displays and error:(
> "Amarnath" wrote:
> > That option is not available instead you can use date picker or seperate all
> > three in 3 parameters like mm, dd, yyyy and then concatenate..
> >
> > Amarnath
> >
> > "Ridhima Sood" wrote:
> >
> > > i have managed to go one step beyond and set up a parameter which uses the
> > > value as mm/dd/yy... how can i change it to so user can enter a value in
> > > dd-mm-yy format. the parameter type is strng.
> > >
> > > "Ridhima Sood" wrote:
> > >
> > > > hi All
> > > >
> > > > I am quiet new to sql reporting so would really appreciate some help. I am
> > > > using this with CRM 3.0
> > > >
> > > > I want to do a report where user can select a to date and a from date to see
> > > > all completed phone calls. However both the to and the from are based on one
> > > > field called actualend.
> > > >
> > > > Actual end is a date time field but in the layout tab i have used the function
> > > > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > > > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > > > select a start date and an end date to report. Can someone please guide me
> > > > how to go about this.
> > > >
> > > > I have tried using the @.>=actualend parameter under the data tab. It works
> > > > for that but does not work under the reports tab. I can see it as a parameter
> > > > under report - report parameters but when i run the report with a date say
> > > > 14/05/07 i get the error as
> > > > An error occured during local report processing.
> > > > An error has occurred during report processing.
> > > > Cannot read the next data row for the data set CRM_MSCRM
> > > > Arithmetic overflow error converting expression to data type datetime.
> > > >
> > > > I would really appreciate some help.
> > > >
> > > > Thanks and Regards
> > > > Ridhima
I am quiet new to sql reporting so would really appreciate some help. I am
using this with CRM 3.0
I want to do a report where user can select a to date and a from date to see
all completed phone calls. However both the to and the from are based on one
field called actualend.
Actual end is a date time field but in the layout tab i have used the function
=Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
into just a dd-mm-yy value. Now i want to create a paramater where user can
select a start date and an end date to report. Can someone please guide me
how to go about this.
I have tried using the @.>=actualend parameter under the data tab. It works
for that but does not work under the reports tab. I can see it as a parameter
under report - report parameters but when i run the report with a date say
14/05/07 i get the error as
An error occured during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the data set CRM_MSCRM
Arithmetic overflow error converting expression to data type datetime.
I would really appreciate some help.
Thanks and Regards
Ridhimai have managed to go one step beyond and set up a parameter which uses the
value as mm/dd/yy... how can i change it to so user can enter a value in
dd-mm-yy format. the parameter type is strng.
"Ridhima Sood" wrote:
> hi All
> I am quiet new to sql reporting so would really appreciate some help. I am
> using this with CRM 3.0
> I want to do a report where user can select a to date and a from date to see
> all completed phone calls. However both the to and the from are based on one
> field called actualend.
> Actual end is a date time field but in the layout tab i have used the function
> =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> into just a dd-mm-yy value. Now i want to create a paramater where user can
> select a start date and an end date to report. Can someone please guide me
> how to go about this.
> I have tried using the @.>=actualend parameter under the data tab. It works
> for that but does not work under the reports tab. I can see it as a parameter
> under report - report parameters but when i run the report with a date say
> 14/05/07 i get the error as
> An error occured during local report processing.
> An error has occurred during report processing.
> Cannot read the next data row for the data set CRM_MSCRM
> Arithmetic overflow error converting expression to data type datetime.
> I would really appreciate some help.
> Thanks and Regards
> Ridhima|||That option is not available instead you can use date picker or seperate all
three in 3 parameters like mm, dd, yyyy and then concatenate..
Amarnath
"Ridhima Sood" wrote:
> i have managed to go one step beyond and set up a parameter which uses the
> value as mm/dd/yy... how can i change it to so user can enter a value in
> dd-mm-yy format. the parameter type is strng.
> "Ridhima Sood" wrote:
> > hi All
> >
> > I am quiet new to sql reporting so would really appreciate some help. I am
> > using this with CRM 3.0
> >
> > I want to do a report where user can select a to date and a from date to see
> > all completed phone calls. However both the to and the from are based on one
> > field called actualend.
> >
> > Actual end is a date time field but in the layout tab i have used the function
> > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > select a start date and an end date to report. Can someone please guide me
> > how to go about this.
> >
> > I have tried using the @.>=actualend parameter under the data tab. It works
> > for that but does not work under the reports tab. I can see it as a parameter
> > under report - report parameters but when i run the report with a date say
> > 14/05/07 i get the error as
> > An error occured during local report processing.
> > An error has occurred during report processing.
> > Cannot read the next data row for the data set CRM_MSCRM
> > Arithmetic overflow error converting expression to data type datetime.
> >
> > I would really appreciate some help.
> >
> > Thanks and Regards
> > Ridhima|||hi Amarnath
When i use the date picker it doesnt work either.. displays and error:(
"Amarnath" wrote:
> That option is not available instead you can use date picker or seperate all
> three in 3 parameters like mm, dd, yyyy and then concatenate..
> Amarnath
> "Ridhima Sood" wrote:
> > i have managed to go one step beyond and set up a parameter which uses the
> > value as mm/dd/yy... how can i change it to so user can enter a value in
> > dd-mm-yy format. the parameter type is strng.
> >
> > "Ridhima Sood" wrote:
> >
> > > hi All
> > >
> > > I am quiet new to sql reporting so would really appreciate some help. I am
> > > using this with CRM 3.0
> > >
> > > I want to do a report where user can select a to date and a from date to see
> > > all completed phone calls. However both the to and the from are based on one
> > > field called actualend.
> > >
> > > Actual end is a date time field but in the layout tab i have used the function
> > > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > > select a start date and an end date to report. Can someone please guide me
> > > how to go about this.
> > >
> > > I have tried using the @.>=actualend parameter under the data tab. It works
> > > for that but does not work under the reports tab. I can see it as a parameter
> > > under report - report parameters but when i run the report with a date say
> > > 14/05/07 i get the error as
> > > An error occured during local report processing.
> > > An error has occurred during report processing.
> > > Cannot read the next data row for the data set CRM_MSCRM
> > > Arithmetic overflow error converting expression to data type datetime.
> > >
> > > I would really appreciate some help.
> > >
> > > Thanks and Regards
> > > Ridhima|||What I meant was you can use date picker for just picking dates in any ormat,
because this type of formatting upfront is not possible.
Amarnath
"Ridhima Sood" wrote:
> hi Amarnath
> When i use the date picker it doesnt work either.. displays and error:(
> "Amarnath" wrote:
> > That option is not available instead you can use date picker or seperate all
> > three in 3 parameters like mm, dd, yyyy and then concatenate..
> >
> > Amarnath
> >
> > "Ridhima Sood" wrote:
> >
> > > i have managed to go one step beyond and set up a parameter which uses the
> > > value as mm/dd/yy... how can i change it to so user can enter a value in
> > > dd-mm-yy format. the parameter type is strng.
> > >
> > > "Ridhima Sood" wrote:
> > >
> > > > hi All
> > > >
> > > > I am quiet new to sql reporting so would really appreciate some help. I am
> > > > using this with CRM 3.0
> > > >
> > > > I want to do a report where user can select a to date and a from date to see
> > > > all completed phone calls. However both the to and the from are based on one
> > > > field called actualend.
> > > >
> > > > Actual end is a date time field but in the layout tab i have used the function
> > > > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > > > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > > > select a start date and an end date to report. Can someone please guide me
> > > > how to go about this.
> > > >
> > > > I have tried using the @.>=actualend parameter under the data tab. It works
> > > > for that but does not work under the reports tab. I can see it as a parameter
> > > > under report - report parameters but when i run the report with a date say
> > > > 14/05/07 i get the error as
> > > > An error occured during local report processing.
> > > > An error has occurred during report processing.
> > > > Cannot read the next data row for the data set CRM_MSCRM
> > > > Arithmetic overflow error converting expression to data type datetime.
> > > >
> > > > I would really appreciate some help.
> > > >
> > > > Thanks and Regards
> > > > Ridhima
Monday, March 19, 2012
date input parameter
I have user who are entering startdate and enddate parameters. How can I make
sure that the date the enter is in the mm/dd/yy format? Usally you do this
with javascript. Or can I just do something where I can check the length of
the field.
--
kmatth007well the answer will be devide to 2:
1) on RS2000 where no calander/timepicker is availble you should define this
field as datetime and choose the wright format,then if the user will enter a
wrong date the RS won't load the report.no message avaible...
2)on RS2005 there's a timepicker so it's easy to know the user enter a
wright date.
But as recommanded a lot in this newsgroup the best way 2 controll the user
input is 2 use a .Net application that getts the input from the user and send
it to RS.
"kmatth007" wrote:
> I have user who are entering startdate and enddate parameters. How can I make
> sure that the date the enter is in the mm/dd/yy format? Usally you do this
> with javascript. Or can I just do something where I can check the length of
> the field.
> --
> kmatth007
sure that the date the enter is in the mm/dd/yy format? Usally you do this
with javascript. Or can I just do something where I can check the length of
the field.
--
kmatth007well the answer will be devide to 2:
1) on RS2000 where no calander/timepicker is availble you should define this
field as datetime and choose the wright format,then if the user will enter a
wrong date the RS won't load the report.no message avaible...
2)on RS2005 there's a timepicker so it's easy to know the user enter a
wright date.
But as recommanded a lot in this newsgroup the best way 2 controll the user
input is 2 use a .Net application that getts the input from the user and send
it to RS.
"kmatth007" wrote:
> I have user who are entering startdate and enddate parameters. How can I make
> sure that the date the enter is in the mm/dd/yy format? Usally you do this
> with javascript. Or can I just do something where I can check the length of
> the field.
> --
> kmatth007
Date function to extract start date
Hello,
I was wondering if there is a date function to determine a starting
date based on a @.EndDate
parameter. In other word, if my user chose an end date of 3/31/2006, I
want to get a start date of 4/1/2005.
I appreciate any suggestion.
Edgar J.You can use another query to provide a default for the second parameter
using the 1st as input.
If its sqlserver you can use (I may have syntax slightly wrong as Im
not looking this up now).
SELECT [StartDate] = dateadd(day, dateadd(year, @.EndDate, -1), 1)
In other words return just 1 row , 1 column with a date.
Use sqlserver dateadd function to subtract 1 year and again to add 1
day.
Edgar wrote:
> Hello,
> I was wondering if there is a date function to determine a starting
> date based on a @.EndDate
> parameter. In other word, if my user chose an end date of 3/31/2006, I
> want to get a start date of 4/1/2005.
> I appreciate any suggestion.
> Edgar J.
I was wondering if there is a date function to determine a starting
date based on a @.EndDate
parameter. In other word, if my user chose an end date of 3/31/2006, I
want to get a start date of 4/1/2005.
I appreciate any suggestion.
Edgar J.You can use another query to provide a default for the second parameter
using the 1st as input.
If its sqlserver you can use (I may have syntax slightly wrong as Im
not looking this up now).
SELECT [StartDate] = dateadd(day, dateadd(year, @.EndDate, -1), 1)
In other words return just 1 row , 1 column with a date.
Use sqlserver dateadd function to subtract 1 year and again to add 1
day.
Edgar wrote:
> Hello,
> I was wondering if there is a date function to determine a starting
> date based on a @.EndDate
> parameter. In other word, if my user chose an end date of 3/31/2006, I
> want to get a start date of 4/1/2005.
> I appreciate any suggestion.
> Edgar J.
Thursday, March 8, 2012
Date format problem with SQL server
Hi.
I'm localized in Greece and the date format used is dd/mm/yyyy.
The question mark is a place holder for your first param. Look it up, honestly its much easier than I'm making it. But you really need to spend 20 mins and have a good read.|||You were right.
I used parameters and date problem solved. I have put the ? as you suggested and after I have the command:
I'm localized in Greece and the date format used is dd/mm/yyyy.
So I have is that I made a page with a callendar. The user picks a date and this date is stored to an SQL server. But here comes the problem. When I try to write to the server the above format ( dd/mm/yyyy ) is not accepted because SQL wants date in format ( mm/dd/yyyy). So if day is bigger than 12 I get error or if is less than 13 wrong date is stored in the SQL.
Any ideas?
Thanks in advanceWhen you are in code, be it SQL or C#/VB, always use the date data type NOT a string. When you display the contents of a date data type always use the appropriate culture. When you pass a value to SQL use an ado paramater of datetime. There should be no problem then. If, and I can't think why, you really want to pass a string to SQL with a date in it, then use a culture invariant such as YYYY-MM-DD|||Thanks for your quick reply.
No I'm not passing string to the SQL. Passing date data type. I read it as date, I use it as date in the calendar, and I try to write it as date in the SQL. but fail. :(|||That doesn't make any sense. Can you post how you are using it in SQL server?|||I read it as follow:
Dim cmd As New OleDbCommand("SELECT * from NEWS where id = " & Convert.ToInt64(myGlobalId), con1)
And I get no problem there. It reads correctly and displays correctly. all the fields.
And I write to the db as follow:
Dim cmd As New OleDbCommand("INSERT INTO NEWS (id, title, body, news_date) VALUES (" & newid & ",'" & titlebox.Text & "','" & bodybox.Text & "','" & mySdate & "')", con1)
mySdate has been declared as date and as datetime but no use. Also it has been test with and without quotes ('). Without quotes I get a new record but date is always 1/1/1900. With quotes I get an error when day is bigger than the month.
Thanks for your time...|||You are using a string! You must try to use params, for many many reasons, one of them is sorting out formating problems like you've got. Switch to using params and the problem will go away.
PS You really should worry about SQL Injection hacks so using params will get you around 99% of those too.|||What do you mean about security? Do you have a link so I read more about SQL Injection hacks?
I guess I'm sending a string as long as I use ['mySdate'] but I do not as long I send [mySdate]. When I send just [mySdate] I get a 1/1/1900 date in the SQL db. So the error should be elsewere. Also I found is that when a ODBC/System DNS is created there is an option "Use regional settings when outputing currency, numbers, dates and times" that can be checked/unchecked. But can't find somthing similar when creating my connection in visual studio...
Once more thanks for helping a newbie :)|||There are plenty of resources on SQL Injection, just type it into google and choose the one at the level most suited. Basically imagine someone has entered "'Delete from Stock" as a text string, it could actually run the SQL typed in, not what you want at all!
I didn't really follow what your statement, but trust me use paramaters. Look up SQLParameter and Commands. Your SQL should look more like...
"select * from table where column=?"
The question mark is a place holder for your first param. Look it up, honestly its much easier than I'm making it. But you really need to spend 20 mins and have a good read.|||You were right.
I used parameters and date problem solved. I have put the ? as you suggested and after I have the command:
"cmd.Parameters.Add("@.news_date", OleDbType.Date).Value = mydate"
Also seems that there is a protection for SQL injections... :)
The same for the other fields of course. :) Seems to work great now!
Thanks for your help.
date format problem
hi..
can i change my date format of MS SQL 2000 to dd/mm/yyyypermanently as my user is more comfortable with this format.
i have made date textbox of datagrid readonly (enable=false) when i try to modify particular record from my datagrid, it shows me following 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.
what should i do to overcome this error?
Use parameters of the datetime type, and make sure your asp.net application is running with a culture that has dd/mm/yyyy as a valid date time format.
Sunday, February 19, 2012
Date Conversion!
Can anyone suggest how to convert a dd/mm/yyyy format date to a mm/dd/yyyy
formatted date using a user defined function.
This funtion is to be called inside a stored procedure.
I have already written a conversion function that handle mm/dd/yyyy to
dd/mm/yyyy. Not sure how to go the other way.
I used date part for the mm/dd/yyyy coversion, but i assume it only
recocognises mm/dd/yyyy dates at parse time, so feeding it a dd/mm/yyyy date
won't do.
Help Appreciated.
AJDates are not stored in any particular format, so your question is simply hp
w
to format a date for display as mm/dd/yyyy. The convert function will do
this when passed a format specifier of 101.
Select convert(varChar(10), getdate(), 101)
103, by the way, formats any date as dd/mm/yyyy..
Select convert(varChar(10), getdate(), 103)
"AJ" wrote:
> Can anyone suggest how to convert a dd/mm/yyyy format date to a mm/dd/yyyy
> formatted date using a user defined function.
> This funtion is to be called inside a stored procedure.
> I have already written a conversion function that handle mm/dd/yyyy to
> dd/mm/yyyy. Not sure how to go the other way.
> I used date part for the mm/dd/yyyy coversion, but i assume it only
> recocognises mm/dd/yyyy dates at parse time, so feeding it a dd/mm/yyyy da
te
> won't do.
> Help Appreciated.
> AJ
>|||Hi
For more on date conversion & formatting issues, please refer to
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"CBretana" wrote:
> Dates are not stored in any particular format, so your question is simply
hpw
> to format a date for display as mm/dd/yyyy. The convert function will do
> this when passed a format specifier of 101.
>
> Select convert(varChar(10), getdate(), 101)
> 103, by the way, formats any date as dd/mm/yyyy..
> Select convert(varChar(10), getdate(), 103)
> "AJ" wrote:
>
formatted date using a user defined function.
This funtion is to be called inside a stored procedure.
I have already written a conversion function that handle mm/dd/yyyy to
dd/mm/yyyy. Not sure how to go the other way.
I used date part for the mm/dd/yyyy coversion, but i assume it only
recocognises mm/dd/yyyy dates at parse time, so feeding it a dd/mm/yyyy date
won't do.
Help Appreciated.
AJDates are not stored in any particular format, so your question is simply hp
w
to format a date for display as mm/dd/yyyy. The convert function will do
this when passed a format specifier of 101.
Select convert(varChar(10), getdate(), 101)
103, by the way, formats any date as dd/mm/yyyy..
Select convert(varChar(10), getdate(), 103)
"AJ" wrote:
> Can anyone suggest how to convert a dd/mm/yyyy format date to a mm/dd/yyyy
> formatted date using a user defined function.
> This funtion is to be called inside a stored procedure.
> I have already written a conversion function that handle mm/dd/yyyy to
> dd/mm/yyyy. Not sure how to go the other way.
> I used date part for the mm/dd/yyyy coversion, but i assume it only
> recocognises mm/dd/yyyy dates at parse time, so feeding it a dd/mm/yyyy da
te
> won't do.
> Help Appreciated.
> AJ
>|||Hi
For more on date conversion & formatting issues, please refer to
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"CBretana" wrote:
> Dates are not stored in any particular format, so your question is simply
hpw
> to format a date for display as mm/dd/yyyy. The convert function will do
> this when passed a format specifier of 101.
>
> Select convert(varChar(10), getdate(), 101)
> 103, by the way, formats any date as dd/mm/yyyy..
> Select convert(varChar(10), getdate(), 103)
> "AJ" wrote:
>
Date Conversion - Flat File - YYYYMMDD
Hi,
What is the new way to transform flat file dates into SQL datetime datatype.Being average user in SQL 2000 DTS I would simply use "Date Time String Transformation Properties" and transform the date into the format I need, in SSIS I haven't found an elegant way of doing this.
My thoughts are to use “data conversion” utilizing substring expressions…
Thanks the help
Bill
Use a derived column transformation to substring the date field and then concatenate the parts together. Once that's done cast it to a datetime field.
Something like:
(DT_DBTIMESTAMP)(substring([yourDateField],5,2) + "/" + substring([yourDateField],7,2) + "/" + substring([yourDateField],1,4))
|||bmilstead,
In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column
ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]
Thanks
Subhash Subramanyam
|||
Subhash Subramanyam wrote:
bmilstead,
In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column
ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]
Thanks
Subhash Subramanyam
Right, but the format of the dates in the flat file are not DB_TIMESTAMP compatible. (YYYYMMDD)
Date Conversion
I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM. I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found. Of course I want I all records for a given day to be returned. This is how I'm doing it now. Thanks.
http://www.stanford.edu/~bsuter/sql-datecomputations.html|||I've changed my SQL Server field type from SmallDateTime to DateTime and it's still not working. If I do a response.write on the SQL statement, I see that it's working correctly (WHERE Date_Requested='09/22/2005')|||You need to keep in mind that there is no Date data type. All ofthe data types involving dates also includes times. You need towind up with a query that looks like this:
That is my best recommendation. That will return you all recordswhere Date_Requested falls on 9/22/2005 regardless of the time part ofthe date you are storing.
I must strongly recommend to you that you use Parameters instead of concatenating UI-supplied data to a string to be executed.
Here's the why:
And here's the how:
This is for a small intranet app so I'm not concerned about SQL injection attacks in this case but that is very good advice.
Thanks for the help.
|||
It's just what I've learned through trial and error and seeing otherpeople struggling with it. The method I suggested will takeadvantage of any index on your date field, and it takes the time partof the date out of the equation.
I have a few thoughts to offer on this viewpoint.
While one would like to think that all coworkers are trustworthy,a curious or disgruntled employee, or perhaps a temporary contractor,might try to access data to which they are not otherwise privileged, orperhaps even attempt to inflict damage to the database ornetwork. There still might be data which needs to be keptsafeguarded, such as payroll information, benefit information.
Dim dteDate_RequestedAsString = txtDate_Requested.Text
If dteDate_Requested <>""Then
strSqlText +=" Date_Requested='" & dteDate_Requested &"'"
EndIf
http://www.stanford.edu/~bsuter/sql-datecomputations.html|||I've changed my SQL Server field type from SmallDateTime to DateTime and it's still not working. If I do a response.write on the SQL statement, I see that it's working correctly (WHERE Date_Requested='09/22/2005')|||You need to keep in mind that there is no Date data type. All ofthe data types involving dates also includes times. You need towind up with a query that looks like this:
WHERE Date_Requested >= '20050922' AND Date_Requested < '20050923'
That is my best recommendation. That will return you all recordswhere Date_Requested falls on 9/22/2005 regardless of the time part ofthe date you are storing.
I must strongly recommend to you that you use Parameters instead of concatenating UI-supplied data to a string to be executed.
Here's the why:
Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET
And here's the how:
Using Parameterized Query in ASP.NET, Part 1|||I see. I've been a developer quite awhile and did not know this was the best way to search date fields.
Using Parameterized Query in ASP.NET, Part 2
This is for a small intranet app so I'm not concerned about SQL injection attacks in this case but that is very good advice.
Thanks for the help.
|||
evanburen wrote:
I see. I've been a developer quite awhile anddid not know this was the best way to search date fields.
It's just what I've learned through trial and error and seeing otherpeople struggling with it. The method I suggested will takeadvantage of any index on your date field, and it takes the time partof the date out of the equation.
This isfor a small intranet app so I'm not concerned about SQL injectionattacks in this case but that is very good advice.
I have a few thoughts to offer on this viewpoint.
While one would like to think that all coworkers are trustworthy,a curious or disgruntled employee, or perhaps a temporary contractor,might try to access data to which they are not otherwise privileged, orperhaps even attempt to inflict damage to the database ornetwork. There still might be data which needs to be keptsafeguarded, such as payroll information, benefit information.
Subscribe to:
Posts (Atom)