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()))
>

Date query

I have the following table with datetime and varchar(10) columns.
CREATE TABLE tblA (
fillDated datetime NULL ,
fillDate varchar (10)
)
Sample data:
fillDated fillDate
1/13/2006 1/13/2006
12/19/2005 12/19/2005
I would like to query those records where the date is >= today's date.
Say today's date is 1/13/2006.
From the above data, I would like the result to be
1/13/2006 1/13/2006
When I do the following
select tblA.filldated, tblA.filldate,getdate()
from tblA
where tblA.filldated >= getdate()
--> the result is NO record
when I do the following:
select tblA.filldated, tblA.filldate,getdate()
from tblA
where filldate >= convert(varchar(10),getdate(),101)
--> the result is
1/13/2006 1/13/2006
12/19/2005 12/19/2005 --> wrong, because 12/19/2005 is < 1/13/2006
How can I query the table so that I will get the following as the result ?
1/13/2006 1/13/2006
Thank you.filldated >= getdate() won't work because getdate() will return date
and time so getdate > filldated
filldate >= convert(varchar(10),getdate(),101) is comparing two
strings to see if they are >= then each other, not really what you want
to do is it?|||> filldate >= convert(varchar(10),getdate(),101) is comparing two
> strings to see if they are >= then each other, not really what you want
> to do is it?
No, I want the result to be
1/13/2006 1/13/2006
How can I do that ?
Thanks.
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137168331.130329.120930@.f14g2000cwb.googlegroups.com...
> filldated >= getdate() won't work because getdate() will return date
> and time so getdate > filldated
> filldate >= convert(varchar(10),getdate(),101) is comparing two
> strings to see if they are >= then each other, not really what you want
> to do is it?
>|||http://groups.google.com/group/micr...ring+&start=10&|||Thanks.
I solved the problem by using the following query:
select tblA.filldated, tblA.filldate,getdate()
from tblA
where datediff(day,getdate(),filldated) >= 0
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137170228.372663.107310@.g49g2000cwa.googlegroups.com...
> http://groups.google.com/group/micr...ring+&start=10&
>sql

Date problem

I have a table that has two datetime columns and the following stored
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks

Date problem

Hi,
Can anybody help me to find an easy solution to this problem?
I have a table

CREATE TABLE T1 (
Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(20)
, Col4 DATETIME
, Col5 INT )

INSERT INTO T1 VALUES ('A01','B01','C01',23-03-2006,4)

I want to pass a parameter to a stored proc such as Col1 ('A01'),and it will
check value of Col5, which is 4 here in our data.And then it will generate a resultset by adding 1+ to the month of Col4.

And I want to get a resultset like

A01 B01 C01 23-03-2006
A01 B01 C01 23-04-2006
A01 B01 C01 23-05-2006
A01 B01 C01 23-06-2006

It will also check if the date is 25-12-2006 the next date would be 25-01-2007 and also if 29-01-2006 the next date would be 28-02-2006.
I am trying to avoid Cursor.
Any solution would be really appreciated.
Thanks!!create an integers table like this --create table integers (i integer not null primary key)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9) then in the stored proc, run this query --select Col1
, Col2
, Col3
, dateadd(mm,i,Col4) as Col4
from integers
cross
join T1
where i < Col5|||You are one of the smartest guy I ever seen.
Rudy ,thanks a ton.;)|||thanks for the kind words

but there are a half dozen guys in this very forum smarter than me ;)|||create an integers table like this --create table integers (i integer not null primary key)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9) Just an FYI - if you want a BIG integers table (and one day you will :) ) this is a nice function to create one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx|||Just an FYI - if you want a BIG integers table (and one day you will :) ) this is a nice function to create one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

Thanks for the link Pootie;)

DATE PARAMETER-- Simple Question

Hi,
I am using the following simple stored procedure to dispaly data between 2
date ranges.
CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
(
@.manucode varchar(50),
@.brand varchar(50),
@.StartDate datetime,
@.EndDate datetime
)
AS
SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
FROM ITEMHIST INNER JOIN
ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND( ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
GO
Now the prblem is that when I enterd start date as 1 Sept 2004 and end date
as 1Aug 2005, then there is no data displayed. I know the problem is with th
e
last 2 "AND" clauses of my stored procedure. Please help. I want to display
data between the 2 date ranges.
Thanks
--
pmudAND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
Did you mean EndDate here, and not StartDate, on the last line? Anyway, I'm
not sure that breaking down a datetime into month and year to calculate a
range is very wise. If you're interested in blocking by months only, have
you considered adding a column to ITEMHIST that represents the month and
year combined, e.g. 20050101, 20050201, etc. This makes querying by date
ranges a true date range query, instead of separating the components of the
date and assuming that the range will always have a starting month and year
and then the ending date is starting month - 1 and ending year + 1.
A
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO,
> ITEMS.DESCRIPT,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end
> date
> as 1Aug 2005, then there is no data displayed. I know the problem is with
> the
> last 2 "AND" clauses of my stored procedure. Please help. I want to
> display
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Try,
...
WHERE
(ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND (ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
between (Year(@.StartDate ) * 100) + Month(@.StartDate)
AND (Year(@.EndDate) * 100) + Month(@.EndDate)
If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
do not expect sql server to perform an index s in these indexes. Using
those columns in an expression, limit then to be considered search arguments
.
AMB
"pmud" wrote:

> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIP
T,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end dat
e
> as 1Aug 2005, then there is no data displayed. I know the problem is with
the
> last 2 "AND" clauses of my stored procedure. Please help. I want to displa
y
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Adding a computed column as Aaron mentioned would seem to be a much better
long term solution that what you are doing now. That said, if you are
unable to alter the schema, give this a try. Replace the last two AND
conditions with the following:
cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
varchar) as datetime) between @.StartDate and @.EndDate
--Brian
(Please reply to the newsgroups only.)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> Did you mean EndDate here, and not StartDate, on the last line? Anyway,
> I'm not sure that breaking down a datetime into month and year to
> calculate a range is very wise. If you're interested in blocking by
> months only, have you considered adding a column to ITEMHIST that
> represents the month and year combined, e.g. 20050101, 20050201, etc.
> This makes querying by date ranges a true date range query, instead of
> separating the components of the date and assuming that the range will
> always have a starting month and year and then the ending date is starting
> month - 1 and ending year + 1.
> A
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
>|||Hi ,
Yes, I meant @.EndDate in the last line. Is there any other way of doing
this. It is not necesaary to do month and year separately. i did it that way
bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
month) and FISCAL_YEAR fields.
Is there any way I can do it together rather than breaking it down...If not,
then can you please explain your menthod in detail..
Thansk for all your help..
--
pmud
"Alejandro Mesa" wrote:
> Try,
>
> ...
> WHERE
> (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND (ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
> between (Year(@.StartDate ) * 100) + Month(@.StartDate)
> AND (Year(@.EndDate) * 100) + Month(@.EndDate)
> If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
> do not expect sql server to perform an index s in these indexes. Using
> those columns in an expression, limit then to be considered search argumen
ts.
>
> AMB
> "pmud" wrote:
>|||Hi,
Actually I just now checked, Aaron was right, I by chance wrote @.StartDate
instaed of @.EndDate in the sp and after changing ti , it works. I apologize.
Though I would really like to underastand the enw methods you all told me.I
didnt quite understand them...Can you please explain your solutions...
I really appreciate all your help
--
pmud
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||It is hard for me to give you a good explanation because my englis is far
from good, but I am posting an example so you can get the idea.
I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
(FISCAL_YEAR * 100) + PERIOD
and the same with the start and end dates. Then I am selecting just where
this number is between the ones from start and end date.
create table t1 (
c1 int not null identity primary key,
c2 int not null check (c2 between 1900 and 3000),
c3 int not null check (c3 between 1 and 12)
)
go
insert into t1(c2, c3) values(2000, 1)
insert into t1(c2, c3) values(2000, 5)
insert into t1(c2, c3) values(2001, 6)
insert into t1(c2, c3) values(2002, 8)
insert into t1(c2, c3) values(2005, 7)
insert into t1(c2, c3) values(2005, 8)
go
declare @.sd datetime
declare @.ed datetime
set @.sd = '20000201'
set @.ed = '20050701'
select
c1,
c2,
c3,
(c2 * 100) + c3 as c4,
(year(@.sd) * 100) + month(@.sd) as c5,
(year(@.ed) * 100) + month(@.ed) as c6
from
t1
where
(c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
100) + month(@.ed)
order by
c2, c3
go
drop table t1
go
AMB
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||Hi Aljendro,
Thanks for talking the time and explaining it. I appreciate it. Your
explanation was very helpful and whatever little remaining doubt I have I
think when I will implement it myself, that will make it still clearer.
Thanks for ur help.
--
pmud
"Alejandro Mesa" wrote:
> It is hard for me to give you a good explanation because my englis is far
> from good, but I am posting an example so you can get the idea.
> I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
> (FISCAL_YEAR * 100) + PERIOD
> and the same with the start and end dates. Then I am selecting just where
> this number is between the ones from start and end date.
> create table t1 (
> c1 int not null identity primary key,
> c2 int not null check (c2 between 1900 and 3000),
> c3 int not null check (c3 between 1 and 12)
> )
> go
> insert into t1(c2, c3) values(2000, 1)
> insert into t1(c2, c3) values(2000, 5)
> insert into t1(c2, c3) values(2001, 6)
> insert into t1(c2, c3) values(2002, 8)
> insert into t1(c2, c3) values(2005, 7)
> insert into t1(c2, c3) values(2005, 8)
> go
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20000201'
> set @.ed = '20050701'
> select
> c1,
> c2,
> c3,
> (c2 * 100) + c3 as c4,
> (year(@.sd) * 100) + month(@.sd) as c5,
> (year(@.ed) * 100) + month(@.ed) as c6
> from
> t1
> where
> (c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
> 100) + month(@.ed)
> order by
> c2, c3
> go
> drop table t1
> go
>
> AMB
>
> "pmud" wrote:
>|||Hi Brian,
I used the cast statement as it is.. and it works...I am trying to
undersatnd how it exactly works though...
pmud
"Brian Lawton" wrote:

> Adding a computed column as Aaron mentioned would seem to be a much better
> long term solution that what you are doing now. That said, if you are
> unable to alter the schema, give this a try. Replace the last two AND
> conditions with the following:
> cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
> varchar) as datetime) between @.StartDate and @.EndDate
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
>
>|||Basically it just converts your Period and FiscalYear combination into a
date with the format mm/01/yyyy. As a datetime datatype, it then does the
comparison to the @.StartDate and @.EndDate via the BETWEEN.
--Brian
(Please reply to the newsgroups only.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:0AD179F0-3096-48B9-88D3-AD7C9F9BE88E@.microsoft.com...
> Hi Brian,
> I used the cast statement as it is.. and it works...I am trying to
> undersatnd how it exactly works though...
> --
> pmud
>
> "Brian Lawton" wrote:
>

Date Parameter Procedure

I am trying to create a Parameter for entering a Date Range for a report,
here is what I think the syntax should look like, but it is wrong;
"Create Procedure procAccrual Aging
as
Select Distinct Receiptdate
from pop30310
where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
As you seen, I am trying to create a beginning and ending range parameter.
Thank you,
RyanOn Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am trying to create a Parameter for entering a Date Range for a report,
> here is what I think the syntax should look like, but it is wrong;
> "Create Procedure procAccrual Aging
> as
> Select Distinct Receiptdate
> from pop30310
> where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> As you seen, I am trying to create a beginning and ending range parameter.
> Thank you,
> Ryan
If I'm understanding you correctly, you will want to create your
stored procedure for your report like this:
Create Procedure procAccrual Aging
@.BeginDate DATETIME,
@.EndDate DATETIME
as
Select Distinct Receiptdate
from pop30310
where (pop30310.receiptdate between @.BeginDate and @.EndDate)
Then have the @.BeginDate and @.EndDate report parameters based on a
standard calendar datetime control (default for datetime parameters).
Then in the Data view, where you define the dataset for the stored
procedure procAccrual Aging then in the Parameters tab of the Edit
Dataset [...] section, select the report parameters. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ed,
The procedure works now, but when I preview the report and enter a range,
the data still spits out the same. Is there some linking that needs to be
done?
Thanks,
Ryan
"EMartinez" wrote:
> On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> wrote:
> > I am trying to create a Parameter for entering a Date Range for a report,
> > here is what I think the syntax should look like, but it is wrong;
> >
> > "Create Procedure procAccrual Aging
> > as
> > Select Distinct Receiptdate
> > from pop30310
> > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> >
> > As you seen, I am trying to create a beginning and ending range parameter.
> >
> > Thank you,
> >
> > Ryan
>
> If I'm understanding you correctly, you will want to create your
> stored procedure for your report like this:
> Create Procedure procAccrual Aging
> @.BeginDate DATETIME,
> @.EndDate DATETIME
> as
> Select Distinct Receiptdate
> from pop30310
> where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> Then have the @.BeginDate and @.EndDate report parameters based on a
> standard calendar datetime control (default for datetime parameters).
> Then in the Data view, where you define the dataset for the stored
> procedure procAccrual Aging then in the Parameters tab of the Edit
> Dataset [...] section, select the report parameters. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> Ed,
> The procedure works now, but when I preview the report and enter a range,
> the data still spits out the same. Is there some linking that needs to be
> done?
> Thanks,
> Ryan
> "EMartinez" wrote:
> > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > wrote:
> > > I am trying to create a Parameter for entering a Date Range for a report,
> > > here is what I think the syntax should look like, but it is wrong;
> > > "Create Procedure procAccrual Aging
> > > as
> > > Select Distinct Receiptdate
> > > from pop30310
> > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> > > As you seen, I am trying to create a beginning and ending range parameter.
> > > Thank you,
> > > Ryan
> > If I'm understanding you correctly, you will want to create your
> > stored procedure for your report like this:
> > Create Procedure procAccrual Aging
> > @.BeginDate DATETIME,
> > @.EndDate DATETIME
> > as
> > Select Distinct Receiptdate
> > from pop30310
> > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> > Then have the @.BeginDate and @.EndDate report parameters based on a
> > standard calendar datetime control (default for datetime parameters).
> > Then in the Data view, where you define the dataset for the stored
> > procedure procAccrual Aging then in the Parameters tab of the Edit
> > Dataset [...] section, select the report parameters. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
I'm assuming you are addressing me. Have you linked the parameters
that the stored procedure is expecting in the Data tab view with the
report parameters? You would check this via selecting the dataset that
references the stored procedure in the Data view, select the [...]
button to the right of the dataset, for Edit dataset, then select the
Parameters tab and set/verify that @.BeginDate, @.EndDate have the
correct Report parameters associated with them. The values should be
whatever your report parameters are named (i.e., Parameters!
BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
things for you.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for all of your help? What is your address? I am going to have to
send you a bottle of Scotch my friend!
Ryan
"EMartinez" wrote:
> On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> wrote:
> > Ed,
> > The procedure works now, but when I preview the report and enter a range,
> > the data still spits out the same. Is there some linking that needs to be
> > done?
> >
> > Thanks,
> > Ryan
> >
> > "EMartinez" wrote:
> > > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > > wrote:
> > > > I am trying to create a Parameter for entering a Date Range for a report,
> > > > here is what I think the syntax should look like, but it is wrong;
> >
> > > > "Create Procedure procAccrual Aging
> > > > as
> > > > Select Distinct Receiptdate
> > > > from pop30310
> > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> >
> > > > As you seen, I am trying to create a beginning and ending range parameter.
> >
> > > > Thank you,
> >
> > > > Ryan
> >
> > > If I'm understanding you correctly, you will want to create your
> > > stored procedure for your report like this:
> > > Create Procedure procAccrual Aging
> > > @.BeginDate DATETIME,
> > > @.EndDate DATETIME
> > > as
> >
> > > Select Distinct Receiptdate
> > > from pop30310
> > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> >
> > > Then have the @.BeginDate and @.EndDate report parameters based on a
> > > standard calendar datetime control (default for datetime parameters).
> > > Then in the Data view, where you define the dataset for the stored
> > > procedure procAccrual Aging then in the Parameters tab of the Edit
> > > Dataset [...] section, select the report parameters. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> I'm assuming you are addressing me. Have you linked the parameters
> that the stored procedure is expecting in the Data tab view with the
> report parameters? You would check this via selecting the dataset that
> references the stored procedure in the Data view, select the [...]
> button to the right of the dataset, for Edit dataset, then select the
> Parameters tab and set/verify that @.BeginDate, @.EndDate have the
> correct Report parameters associated with them. The values should be
> whatever your report parameters are named (i.e., Parameters!
> BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
> things for you.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 18, 1:34 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> Enrique,
> Thanks for all of your help? What is your address? I am going to have to
> send you a bottle of Scotch my friend!
> Ryan
> "EMartinez" wrote:
> > On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > wrote:
> > > Ed,
> > > The procedure works now, but when I preview the report and enter a range,
> > > the data still spits out the same. Is there some linking that needs to be
> > > done?
> > > Thanks,
> > > Ryan
> > > "EMartinez" wrote:
> > > > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > > > wrote:
> > > > > I am trying to create a Parameter for entering a Date Range for a report,
> > > > > here is what I think the syntax should look like, but it is wrong;
> > > > > "Create Procedure procAccrual Aging
> > > > > as
> > > > > Select Distinct Receiptdate
> > > > > from pop30310
> > > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> > > > > As you seen, I am trying to create a beginning and ending range parameter.
> > > > > Thank you,
> > > > > Ryan
> > > > If I'm understanding you correctly, you will want to create your
> > > > stored procedure for your report like this:
> > > > Create Procedure procAccrual Aging
> > > > @.BeginDate DATETIME,
> > > > @.EndDate DATETIME
> > > > as
> > > > Select Distinct Receiptdate
> > > > from pop30310
> > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> > > > Then have the @.BeginDate and @.EndDate report parameters based on a
> > > > standard calendar datetime control (default for datetime parameters).
> > > > Then in the Data view, where you define the dataset for the stored
> > > > procedure procAccrual Aging then in the Parameters tab of the Edit
> > > > Dataset [...] section, select the report parameters. Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > I'm assuming you are addressing me. Have you linked the parameters
> > that the stored procedure is expecting in the Data tab view with the
> > report parameters? You would check this via selecting the dataset that
> > references the stored procedure in the Data view, select the [...]
> > button to the right of the dataset, for Edit dataset, then select the
> > Parameters tab and set/verify that @.BeginDate, @.EndDate have the
> > correct Report parameters associated with them. The values should be
> > whatever your report parameters are named (i.e., Parameters!
> > BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
> > things for you.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Glad I could be of assistance. Thanks for the offer, however, I'm not
much into drinking.
Best Regards,
Enrique

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?
I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?|||Thanks! A lot of good information on your hyperlink.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manage
r
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on wha
t
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

Date information

I am trying to create an sp that will be put into a report that allows me to
pull information based on the last day to day year. E.g. today is 4/20/2005
and I would like to be able to pull info between 4/20/2004 and today, but I
want this to be dynamic so that it will pull the data tomorrow for 4/21/2004
to 4/21/2005where your_datetime_col between @.your_datetime_para and
dateadd(y,1,@.your_datetime_para)
-oj
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:50A09C7A-410D-4768-BC74-23D8D22516A0@.microsoft.com...
>I am trying to create an sp that will be put into a report that allows me
>to
> pull information based on the last day to day year. E.g. today is
> 4/20/2005
> and I would like to be able to pull info between 4/20/2004 and today, but
> I
> want this to be dynamic so that it will pull the data tomorrow for
> 4/21/2004
> to 4/21/2005|||SELECT
*
FROM <YourTable> WHERE <DateColumn> BETWEEN
DATEADD(YEAR, -1, GETDATE())
AND
GETDATE()
Hope it helps

Date headache

Guys
I have a table 1 row, a start and end date of a period

create table xperiod(startdate datetime , enddate datetime)
insert xperiod (startdate , enddate)
values ('2004-04-01 00:00:00.000' , 2012-03-31 00:00:00.000)

I'm trying to retrieve a batch of 'smaller' periods from this where the relevant period is a number (of months) passed as a parameter (only ever 1, 3 or 6)

for example, if the parameter is 1 I will obtain the following rows each being a 1 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-04-30 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'

and so on to
'2012-03-01 00:00:00.000' '2012-03-31 00:00:00.000'

if the parameter is 3 I will obtain the following rows each being a 3 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value

startperiod endperiod
'2004-04-01 00:00:00.000' '2004-06-30 00:00:00.000'
'2004-07-01 00:00:00.000' '2004-09-30 00:00:00.000'
'2004-10-01 00:00:00.000' '2004-12-31 00:00:00.000'

and so on to
'2012-01-01 00:00:00.000' '2012-03-31 00:00:00.000'

Hope this makes sense !

I think I'll be ok on the logic for the while loop but my main problem is getting the endperiod value based on the startperiodvalue
Thx in advance--eg:for one month period
select dateadd(dd,-1,dateadd(mm,1,getdate())) as endperiod
--eg:for 3 month period
select dateadd(dd,-1,dateadd(mm,3,getdate())) as endperiod
--eg:for 6 month period
select dateadd(dd,-1,dateadd(mm,6,getdate())) as endperiod|||That's perfect - thanks

Date Formatting in a Cross Tab

I am trying to create a cross tab that will show data from the current month (from before and after the current date) and grouped by month. I want it to automatically calculate the current month so that I do not have to change the date each month. I know how to group by month and I know how to get it to show everything greater than or equal to current date but I don't know how to do everything greater than or equal to current month. Is this possible?

Thanks,
Babs827Does anyone have any ideas?

Date format isn't working

Hello, all. We've got a table that holds the begin and end date for allowing
people into a voting app. Here's the DDL,
CREATE TABLE [tblVotingPeriod] (
[pk] [tinyint] IDENTITY (1, 1) NOT NULL ,
[beginVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_beginVote]
DEFAULT (getdate()),
[endVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_endVote]
DEFAULT (getdate()),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_dateAdded]
DEFAULT (getdate()),
CONSTRAINT [PK_tblVotingPeriod] PRIMARY KEY CLUSTERED
(
[pk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
We need it to store "3/15/2005 12:00:00 AM" as the beginVote and "3/18/2005
12:00:00 AM" as the endVote. However, each time we try entering the
"12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
won't display the time in the ASP page -- just the date. We've tried using
the CONVERT(smalldatetime, beginVote,109) to no avail. Any ideas? Thanks
much."dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 12:00:00 AM" as the endVote. However, each time we try entering the
> "12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
Sounds like a problem with your client code, not SQL Server. SQL Server
DATETIME and SMALLDATETIME datatypes must have a time component -- they
can't be ignored. Or are you getting an error of some sort?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
and tab out, it ignores it. Is there something special about 12:00:00 AM? Is
that the "default" for the date, so it doesn't even show it? What if you do
want it shown?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O7BswobGFHA.544@.TK2MSFTNGP12.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with your client code, not SQL Server. SQL
> Server
> DATETIME and SMALLDATETIME datatypes must have a time component -- they
> can't be ignored. Or are you getting an error of some sort?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
> date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
> and tab out, it ignores it. Is there something special about 12:00:00 AM?
Is
> that the "default" for the date, so it doesn't even show it? What if you
do
> want it shown?
EM does appear to truncate the date if the time is 12:00:00 AM. And
yes, that is the "default" time -- if you insert a row with '20050315' and
no time component, the time will automatically be set to 12:00:00 AM.
(Actually, 00:00:00). You should be able to format the date to see the time
component in ASP using VBScript's date formatting functions. It is being
returned by SQL Server, you're just not displaying it. Note also, Query
Analyzer does not trucate the date.
One side note, by the way: You should re-consider that date format, as
it's ambiguous (it can change with locale); for example, what does the
following represent: '01/02/2005' ? Depends on what country you're in.
The preferred format in SQL Server is YYYYMMDD.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
just not being shown. Do you know of a less ambigious date format? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
> just not being shown. Do you know of a less ambigious date format? Thanks.
YYYYMMDD HH:MM:SS - '20050315 00:00:00'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Which format do I use if I want the date to come out "Mar 21 2005"? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||Thanks, Adam. I got it working, thanks to your help! I had a mistake in my
code where I was doing the convert -- instead of converting it to a char or
varchar, I was changing it to a smalldatetime, which it already was! I'm
using the 100 format to convert it to this: Mar 15 2005 12:00AM
Thanks for your help :) Much appreciated.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e6D5c9bGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> YYYYMMDD HH:MM:SS - '20050315 00:00:00'
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||I suggest you check out my article on the subject:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message news:eTbYl9bGFHA.2356@.TK2MSFTNGP12.phx.g
bl...
> Which format do I use if I want the date to come out "Mar 21 2005"? Thanks
.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
>

Date expression in Access

I'm pretty new to this whole "programming" game. The date field I'm using is [date]. I need to create two queries. One will pull all records year-to-date this year. The other should pull all records year-to-date last year. I'm sure there is an expression that accomplishes this, without having to type in parameters each time I execute the query. Examples, please.datediff(yy, [YourDateValue], getdate())

...will give the difference in the years between your data value and the current date.

Value 0 means YourDateValue is current year-to-date.
Value 1 means YourDateValue is prior year-to-date.
.
.
.
etc

blindman

Date Dimensions

Hi,
I am new to this OLAP stuff.
I am trying to create a cube which has a count of sales per month per year.
How do you make a datetime field a dimension of a cube?
I seem to specify the dimension OK and get the Year Month heirarchy I want
(simple star dimension and a MOLAP) but when I process the cube it gives a
datasource provider error Initialising the dimension.
'Data Source provider error: ; Time:16/03/2005 22:31:32 '
I have tried making a view with the date cast to 101 and creating the cube
on that view but no difference.
thanks
Bob
Hi,
Problem is more general than a date dimension. It is a general failure. I
was misinterpreting the error message.
I shall do some more investigation.
"Bob" <bob@.nowhere.com> wrote in message
news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am new to this OLAP stuff.
> I am trying to create a cube which has a count of sales per month per
year.
> How do you make a datetime field a dimension of a cube?
> I seem to specify the dimension OK and get the Year Month heirarchy I
want
> (simple star dimension and a MOLAP) but when I process the cube it gives a
> datasource provider error Initialising the dimension.
> 'Data Source provider error: ; Time:16/03/2005 22:31:32 '
> I have tried making a view with the date cast to 101 and creating the cube
> on that view but no difference.
> thanks
> Bob
>
|||How is your date dimension designed? The standard is -
PK Year Month Week Date
Then when you set the dimension up in AS, set it as a star, and set the levels,
year down to date. That way you can drill down to the day.
-- Brian
[vbcol=seagreen]
> Hi,
> Problem is more general than a date dimension. It is a general
> failure. I
> was misinterpreting the error message.
> I shall do some more investigation.
> "Bob" <bob@.nowhere.com> wrote in message
> news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> year.
> want

Date Dimensions

Hi,
I am new to this OLAP stuff.
I am trying to create a cube which has a count of sales per month per year.
How do you make a datetime field a dimension of a cube?
I seem to specify the dimension OK and get the Year Month heirarchy I want
(simple star dimension and a MOLAP) but when I process the cube it gives a
datasource provider error Initialising the dimension.
'Data Source provider error: ; Time:16/03/2005 22:31:32 '
I have tried making a view with the date cast to 101 and creating the cube
on that view but no difference.
thanks
BobHi,
Problem is more general than a date dimension. It is a general failure. I
was misinterpreting the error message.
I shall do some more investigation.
"Bob" <bob@.nowhere.com> wrote in message
news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am new to this OLAP stuff.
> I am trying to create a cube which has a count of sales per month per
year.
> How do you make a datetime field a dimension of a cube?
> I seem to specify the dimension OK and get the Year Month heirarchy I
want
> (simple star dimension and a MOLAP) but when I process the cube it gives a
> datasource provider error Initialising the dimension.
> 'Data Source provider error: ; Time:16/03/2005 22:31:32 '
> I have tried making a view with the date cast to 101 and creating the cube
> on that view but no difference.
> thanks
> Bob
>|||How is your date dimension designed? The standard is -
PK Year Month Week Date
Then when you set the dimension up in AS, set it as a star, and set the leve
ls,
year down to date. That way you can drill down to the day.
-- Brian
[vbcol=seagreen]
> Hi,
> Problem is more general than a date dimension. It is a general
> failure. I
> was misinterpreting the error message.
> I shall do some more investigation.
> "Bob" <bob@.nowhere.com> wrote in message
> news:%23Ja74vgKFHA.3392@.TK2MSFTNGP10.phx.gbl...
> year.
>
> want
>

Date Comparison Issue

Hi all,

I am trying to create a stored procedure that will check a date field.
I want to check for records that are equal to or greater then 90 days
from the current date. I am trying to check this against a field
called LastUpdate. Is there an easy way in SQL to do this?

TIA"Icarus" <christopher@.NOSPAMreardenweb.com> wrote in message
news:dfekrvgejb2bg5gt4bqfdjj2p33j1gsj6v@.4ax.com...
> Hi all,
> I am trying to create a stored procedure that will check a date field.
> I want to check for records that are equal to or greater then 90 days
> from the current date. I am trying to check this against a field
> called LastUpdate. Is there an easy way in SQL to do this?
> TIA

SELECT *
FROM T
WHERE LastUpdate <= CURRENT_TIMESTAMP - 90

Regards,
jag|||...
WHERE lastupdate >=
DATEADD(DAY,-90,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))

--
David Portas
----
Please reply only to the newsgroup
--

"Icarus" <christopher@.NOSPAMreardenweb.com> wrote in message
news:dfekrvgejb2bg5gt4bqfdjj2p33j1gsj6v@.4ax.com...
> Hi all,
> I am trying to create a stored procedure that will check a date field.
> I want to check for records that are equal to or greater then 90 days
> from the current date. I am trying to check this against a field
> called LastUpdate. Is there an easy way in SQL to do this?
> TIA|||Thanks! Worked perfectly.

On Tue, 18 Nov 2003 15:48:07 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Icarus" <christopher@.NOSPAMreardenweb.com> wrote in message
>news:dfekrvgejb2bg5gt4bqfdjj2p33j1gsj6v@.4ax.com...
>> Hi all,
>>
>> I am trying to create a stored procedure that will check a date field.
>> I want to check for records that are equal to or greater then 90 days
>> from the current date. I am trying to check this against a field
>> called LastUpdate. Is there an easy way in SQL to do this?
>>
>> TIA
>SELECT *
>FROM T
>WHERE LastUpdate <= CURRENT_TIMESTAMP - 90
>Regards,
>jag

Date calculation

I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.So do you have a specific question? Are you having an issue with some part of it?|||I am not having a specific issue. I wrote the following and it works:

CREATE FUNCTION [simexdb].GetRoundTimeLeft
(
@.datetoday datetime,
@.RoundExpDate datetime
)
RETURNS varchar(50) AS
BEGIN
DECLARE @.Days int
DECLARE @.Hours int
DECLARE @.Min int
DECLARE @.TimeString as varchar(50)
SET @.Min = DATEDIFF ( mi , @.datetoday, @.RoundExpDate)
SET @.Days= @.Min/(24*60)
SET @.Min = @.Min - (@.Days*(24*60))
SET @.Hours= @.Min/60
SET @.Min = @.Min - (@.Hours*(60))
SET @.TimeString = CONVERT(varchar, @.Days ) + 'd / ' + CONVERT(varchar, @.Hours ) + 'h / ' + CONVERT(varchar, @.Min )+ 'm'
Return @.TimeString
END

I would like some expert feedback as to whether this is the best and most efficient approach.

Thank you for your response.|||That looks good|||Thank you for your confirmation.

Date between 12 and 12

Hi

I am trying to create a daily report that will only select the values between 12 o' clock last night and 12 o'clock the previous night. I want to see all the data for the previous day. If someone will please be able to help me with this i would be very greatful.

Here is my query, i even tried to use the NOW() function:

I'm pasting a few queries that i tried, maybe just a slight adjustment will do the trick

Code Snippet

Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, SUBSTRING(CONVERT(VARCHAR(25),m.SentDateTime),0,15) as 'NewDate'

from Members m, client c

WHERE c.clientID = m.clientID

AND m.SentDateTime < {fn NOW()}

AND m.Active = 1

Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime

Order By c.ClientName

Another one i tried

Code Snippet

Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, DateDiff(hh,({fn NOW()}-1),MAX(m.SentDateTime)) as 'NewDate'

from Members m, client c

WHERE c.clientID = m.clientID

AND m.SentDateTime > (GETDATE() - DAY(0.5))

AND m.Active = 1

Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime

Order By c.ClientName

And this one too. (Although this is from a seperate report.

Code Snippet

SELECT v.ContentId, v.ContentType, COUNT(h.Description) AS Hits, h.Description, DATEPART(yy, v.RenderDate) AS y, DATEPART(dy, v.RenderDate) AS d,

CONVERT(CHAR(12), v.RenderDate, 106) AS date

FROM ViewerPaneRenderHistory AS v INNER JOIN

Members AS M ON v.MemberId = M.MemberID INNER JOIN

HealthBytes AS h ON v.ContentId = h.HealthbyteID

WHERE (v.ContentType = 1)

GROUP BY h.Description, v.ContentType, v.ContentId, DATEPART(yy, v.RenderDate), DATEPART(dy, v.RenderDate), CONVERT(CHAR(12), v.RenderDate, 106)

HAVING (CONVERT(CHAR(12), v.RenderDate, 106) > { fn NOW() } - 1)

ORDER BY Date, COUNT(v.MemberId) DESC

I'v tries a few different ways, but just can't seem to get it right.

Any help would be greatly appreciated.

Kind Regards

Carel Greaves

use the following contion on the where clause...

Code Snippet

ColumnName < Cast(Convert(varchar,@.EndDate,101) as datetime)

and ColumnName >= Cast(Convert(varchar,@.EndDate,101) as datetime) - 1

or

ColumnName < Cast(Convert(varchar,GetDate(),101) as datetime)

and ColumnName >= Cast(Convert(varchar,GetDate(),101) as datetime) - 1

|||

Thanks

|||

Hi,carel:

I am not sure if i known your really means from the upstair threes samples,Following is my reply;

About figure 1:

i think the correct sql is :

Code Snippet

select c.clientname,m.memberid,m.name,m.surname,m.email,m.sentdatetime,SUBSTRING(CONVERT(VARCHAR(25),m.SentDatetime),0,15) as 'NewDate'

from Members as m

inner join client as c

on m.clientID=m.clientID

where m.Active=1

and m.SentDateTime>=Convert(varchar(10),dateadd(dd,-1,getdate()),120)
and m.SentDateTime<Convert(varchar(10),getdate(),120)

i don't know why you use "group by" in the first example.

if you only want previous day's data, i think you could use the condition (Marked with green) only.

NOTE:

the dateformat i used in this example Convert(varchar(10),getdate(),120) returns Date fromat 'YYYY-MM-DD'. and it length is 10

please correct this as your local format, meanwhile you should notice the length.

Date and time

Hello all!
I want to create a time stamp based on the system date and system
time. The format of my timestamp should be YYYYMMDDhhmmss.
YYYY year
MM month
DD day
hh hours
mm minutes
ss seconds
Does anyone have a clue on how can I do this?
Thanks in advance,
Hugo MadureiraHere's an example:
DECLARE @.i datetime
SET @.i = CURRENT_TIMESTAMP
SELECT CONVERT(varchar(8), @.i, 112) + REPLACE(CONVERT(varchar(8), @.i, 108),
':', SPACE(0))
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hugo Madureira" <hugomadureira@.hotmail.com> wrote in message
news:uhptUrejFHA.2644@.TK2MSFTNGP09.phx.gbl...
Hello all!
I want to create a time stamp based on the system date and system
time. The format of my timestamp should be YYYYMMDDhhmmss.
YYYY year
MM month
DD day
hh hours
mm minutes
ss seconds
Does anyone have a clue on how can I do this?
Thanks in advance,
Hugo Madureira|||Thanks a lot, it worked. That would have take me days to find that out.
Narayana Vyas Kondreddi wrote:
> Here's an example:
> DECLARE @.i datetime
> SET @.i = CURRENT_TIMESTAMP
> SELECT CONVERT(varchar(8), @.i, 112) + REPLACE(CONVERT(varchar(8), @.i, 108)
,
> ':', SPACE(0))
>|||Hi
If you use the normal datetime datatype and getdate() to populate a default.
This can then be displayed in whatever format is require on the client. If
really necessary you can use
REPLACE(REPLACE(REPLACE(CONVERT(char(19)
,mydate,120),'-'.''),SPACE(1),''),':
','') to get YYYYMMDDHHMISS format.
John
"Hugo Madureira" wrote:

> Hello all!
> I want to create a time stamp based on the system date and system
> time. The format of my timestamp should be YYYYMMDDhhmmss.
> YYYY year
> MM month
> DD day
> hh hours
> mm minutes
> ss seconds
> Does anyone have a clue on how can I do this?
> Thanks in advance,
> Hugo Madureira
>