Hi all
I have a problem in using date range
If i select a date range,need to get values for proveious years june 1st to may 31st of current year
ex: 02/01/2006 - 02/28/2006(Current)
need to get values for 06/01/2005 - 05/31/2006 date range
could any one help me in writing formula for this
Thanks in advancedoesn't look like real question ,, , pls be specific|||Thanks for your reply
i would like to give you more specific
In my requiremnet if i selected a date range ex:01/06/2006 - 31/06/2006
need to get the values of preveious years of June 1st - current years May 31st
01/06/2005 - 31/05/2006 values should display
actually we have two fields in that in first field need to display the first date range values and in second one need to do like that
We have two fields named
current and year to date
current means date range the user selected
Year to date means data from June 1 of previous year to May 31 st of Current year|||Hi
use this formula
datetime('01-jan-2006')-365
include if condition to handle leap year
Hope it helps|||It's very simple just use this formula in formula field
formula =DateAdd ("yyyy",-1 , {YOUR DATE })
and use this formula wherever you want to use same date of previous year
Best Luck...|||Thanks for your reply
its useful for me
could you pls tell how to take fixed date 1st june to end date 31st may
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Thursday, March 29, 2012
Date Range
I have a query that I have to place a beginning and ending date to select the
information needed. The date is always 30 days for the current date. Is
there a way to set up a query to select current date minus 30 days that I
will not have to change the date each time?
Thanks in advance for the help.
On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>I have a query that I have to place a beginning and ending date to select the
>information needed. The date is always 30 days for the current date. Is
>there a way to set up a query to select current date minus 30 days that I
>will not have to change the date each time?
>Thanks in advance for the help.
Hi Daniell,
SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
Hugo Kornelis, SQL Server MVP
|||Thanks Hugo, I will give that a try.
"Hugo Kornelis" wrote:
> On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>
> Hi Daniell,
> SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
> --
> Hugo Kornelis, SQL Server MVP
>
sql
information needed. The date is always 30 days for the current date. Is
there a way to set up a query to select current date minus 30 days that I
will not have to change the date each time?
Thanks in advance for the help.
On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>I have a query that I have to place a beginning and ending date to select the
>information needed. The date is always 30 days for the current date. Is
>there a way to set up a query to select current date minus 30 days that I
>will not have to change the date each time?
>Thanks in advance for the help.
Hi Daniell,
SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
Hugo Kornelis, SQL Server MVP
|||Thanks Hugo, I will give that a try.
"Hugo Kornelis" wrote:
> On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>
> Hi Daniell,
> SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
> --
> Hugo Kornelis, SQL Server MVP
>
sql
Tuesday, March 27, 2012
date query problem
why aint this return the records for current day if al FROM and TO variabels is set to current date?
SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
FROM tb_AVVirusLog
WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
CLF_LogGenerationTime, 120) BETWEEN @.from_year + @.from_month + @.from_day AND @.to_year + @.to_month + @.to_day)
GROUP BY VLF_VirusName
ORDER BY COUNT(VLF_VirusName) DESC
This works (it gives me current days records), but i want to be able to specify end date instead of current date.
SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
FROM tb_AVVirusLog
WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
CLF_LogGenerationTime, 120) BETWEEN @.from_year + @.from_month + @.from_day AND CONVERT(varchar, GETDATE(), 120))
GROUP BY VLF_VirusName
ORDER BY COUNT(VLF_VirusName) DESCYou should "Cast" the @.from_year, @.from_Month, @.from_day, etc variables as char or carchar, if not the "@.from_year + @.from_month + @.from_day" contruct will simply add the _integers_ contained in those variables.
So, I would replace the "@.from_year + @.from_month + @.from_day"
with
"cast(@.from_year as char(4)) + '-' + cast(@.from_month as char(2)) + '-' + cast(@.from_day as char(2)"
you should do similar for the @.to_ variables...|||why aint this return the records for current day if al FROM and TO variabels is set to current date?
Oh my.
Post the DDL of your table, and why are your input date values broken up by year/month/day?|||thx for help.
its not broken up in table its only broken up in query so that users can put in these values in a form. maybe not the correct way to do it but it works for me :)
and what u mean by DDL?|||Perhaps this forum would be more helpful:
http://rinkworks.com/dialect/dialectp.cgi?dialect=redneck&url=http%3A%2F%2Fwww.dbforums.com|||ye that was helpful and creative...
SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
FROM tb_AVVirusLog
WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
CLF_LogGenerationTime, 120) BETWEEN @.from_year + @.from_month + @.from_day AND @.to_year + @.to_month + @.to_day)
GROUP BY VLF_VirusName
ORDER BY COUNT(VLF_VirusName) DESC
This works (it gives me current days records), but i want to be able to specify end date instead of current date.
SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
FROM tb_AVVirusLog
WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
CLF_LogGenerationTime, 120) BETWEEN @.from_year + @.from_month + @.from_day AND CONVERT(varchar, GETDATE(), 120))
GROUP BY VLF_VirusName
ORDER BY COUNT(VLF_VirusName) DESCYou should "Cast" the @.from_year, @.from_Month, @.from_day, etc variables as char or carchar, if not the "@.from_year + @.from_month + @.from_day" contruct will simply add the _integers_ contained in those variables.
So, I would replace the "@.from_year + @.from_month + @.from_day"
with
"cast(@.from_year as char(4)) + '-' + cast(@.from_month as char(2)) + '-' + cast(@.from_day as char(2)"
you should do similar for the @.to_ variables...|||why aint this return the records for current day if al FROM and TO variabels is set to current date?
Oh my.
Post the DDL of your table, and why are your input date values broken up by year/month/day?|||thx for help.
its not broken up in table its only broken up in query so that users can put in these values in a form. maybe not the correct way to do it but it works for me :)
and what u mean by DDL?|||Perhaps this forum would be more helpful:
http://rinkworks.com/dialect/dialectp.cgi?dialect=redneck&url=http%3A%2F%2Fwww.dbforums.com|||ye that was helpful and creative...
Date Query
How to write a query which will return list of all the dates from say 3 days from current date and also next 3 days from current date..
eg say
Output should be something like this:
Date
----
02/09/2002
02/10/2002
02/11/2002
02/12/2002 <-- Today's date
02/13/2002
02/14/2002
02/15/2002
Any comments or suggestions are welcome.
Thanks! in advance.Does SQL Server have a DUAL table, or something similar? In Oracle, I'd do:
SELECT SYSDATE (getDate() in SQL Server)
FROM DUAL
WHERE getDate() BETWEEN DateAdd ( 'd', -3, getDate() ) AND DateAdd ( 'd', 3, getDate() )
Obviously, that's a mix of Oracle and SQL Server syntax, but it should be fairly easy to tell which is which. The key is the DUAL table equivalent. I don't know if SQL Server has something like that. You might have to tweak the syntax a little, too. I rarely use the DateAdd function, myself, so I could be off a little.|||select convert(varchar(8),dateadd(dd,a.i, getdate()),112)
from
(
select i = -3
union select -2
union select -1
union select 0
union select 1
union select 2
union select 3
) as a
or using a temp table
create table #a (d datetime)
declare @.d
select @.d = convert(varchar(8),dateadd(dd,-3, getdate()),112)
while @.d <= convert(varchar(8),dateadd(dd,3, getdate()),112)
begin
insert #a select @.d
select @.d = dateadd(dd,1,@.d)
end
select * from #a
drop table #asql
eg say
Output should be something like this:
Date
----
02/09/2002
02/10/2002
02/11/2002
02/12/2002 <-- Today's date
02/13/2002
02/14/2002
02/15/2002
Any comments or suggestions are welcome.
Thanks! in advance.Does SQL Server have a DUAL table, or something similar? In Oracle, I'd do:
SELECT SYSDATE (getDate() in SQL Server)
FROM DUAL
WHERE getDate() BETWEEN DateAdd ( 'd', -3, getDate() ) AND DateAdd ( 'd', 3, getDate() )
Obviously, that's a mix of Oracle and SQL Server syntax, but it should be fairly easy to tell which is which. The key is the DUAL table equivalent. I don't know if SQL Server has something like that. You might have to tweak the syntax a little, too. I rarely use the DateAdd function, myself, so I could be off a little.|||select convert(varchar(8),dateadd(dd,a.i, getdate()),112)
from
(
select i = -3
union select -2
union select -1
union select 0
union select 1
union select 2
union select 3
) as a
or using a temp table
create table #a (d datetime)
declare @.d
select @.d = convert(varchar(8),dateadd(dd,-3, getdate()),112)
while @.d <= convert(varchar(8),dateadd(dd,3, getdate()),112)
begin
insert #a select @.d
select @.d = dateadd(dd,1,@.d)
end
select * from #a
drop table #asql
Sunday, March 25, 2012
Date problem
Hi there !
Can anyone tell me how to convert 1900-01-01 00:00:00 into current date and current time. ?
I am just one the beginer of MSSQL and ASP. I have a table name annouce which work fine before i altered it. I added one new field named tdate whcih has datetype smalldatetime & have default value getdate().
When not provide the any value when inserting a new row, I got the error "Column name or number of supplied values does not match table definition. " which is true because the number of supplied value is 1 less then number of column. But then what does the default value meant for ?
When i provide NULL means( " ") then it automatically add the above mention date and time. ?
Can anyone put some light why it happend ? Shall, i have to put all the fields name except the new added field ? or i have to do something else.
With Thankswhen you got that error message, it was most likely because you did not specify a column list for the INSERT statement
let's say your table has five fields, and you add a sixth one
in order for the default getdate() value to work, you have to sayinsert
into annouce
( fld1, fld2, fld3, fld4, fld5 )
values
( 1, 2, 3, 4, 5 )this way the number of supplied values equals the number of columns you are inserting into
the column you did not mention in the insert statement is then given its default value
rudy
http://r937.com/sql
Can anyone tell me how to convert 1900-01-01 00:00:00 into current date and current time. ?
I am just one the beginer of MSSQL and ASP. I have a table name annouce which work fine before i altered it. I added one new field named tdate whcih has datetype smalldatetime & have default value getdate().
When not provide the any value when inserting a new row, I got the error "Column name or number of supplied values does not match table definition. " which is true because the number of supplied value is 1 less then number of column. But then what does the default value meant for ?
When i provide NULL means( " ") then it automatically add the above mention date and time. ?
Can anyone put some light why it happend ? Shall, i have to put all the fields name except the new added field ? or i have to do something else.
With Thankswhen you got that error message, it was most likely because you did not specify a column list for the INSERT statement
let's say your table has five fields, and you add a sixth one
in order for the default getdate() value to work, you have to sayinsert
into annouce
( fld1, fld2, fld3, fld4, fld5 )
values
( 1, 2, 3, 4, 5 )this way the number of supplied values equals the number of columns you are inserting into
the column you did not mention in the insert statement is then given its default value
rudy
http://r937.com/sql
date part of getdate
i want to use the date part of the function getdate in my sql query.
the getdate function return the current date and time. i want only the date part.
how can i do it.You can use the CONVERT functions and specify a date format. However, it's a lot harder if you want to return the result as an ISO XML format...but if you're not worried about that then convert will work fine|||CONVERT(VARCHAR(10),GETDATE(),101)
the getdate function return the current date and time. i want only the date part.
how can i do it.You can use the CONVERT functions and specify a date format. However, it's a lot harder if you want to return the result as an ISO XML format...but if you're not worried about that then convert will work fine|||CONVERT(VARCHAR(10),GETDATE(),101)
Thursday, March 22, 2012
date parameters
I need a parameter with the following options:
current week
last week
current month
current quarter
I have a dataset with today, monday of current week, current quarter number
and current month number, but i'm at a loss as to how to tie this in with my
report and how to put it into a parameter...any suggestions or referrals
would be most appreciated.Hi,
It is possible to ty values to a labels for a parameter through a query. All
you have to do is to create the dataset with two columns. One with you're
desired options and the other column with the corresponding number. In this
way it is possible to show the user the list of the four option, but use in
your report your own variables.
Jan Pieter Posthuma
"jmann" wrote:
> I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter number
> and current month number, but i'm at a loss as to how to tie this in with my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.|||This logic probably isn't right for your data but the basic idea should be
the same. For simplicity, I'm figuring that current week/month/quarter means
one week/month/quarter ago through today. You'll likely need to figure out
when each period begins using your data and make adjustments.
SELECT * FROM YourTables
WHERE OrderDate BETWEEN
CASE
WHEN @.Period = 'Current Week' THEN DATEADD(Week, -1, GETDATE())
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -2, GETDATE())
WHEN @.Period = 'Current Month' THEN CAST(CAST(MONTH(GETDATE()) AS
VarChar(2)) + '-1-' + CAST(YEAR(GETDATE()) AS VarChar(4)) AS DateTime)
WHEN @.Period = 'Current Quarter' THEN DATEADD(Quarter, -1, GETDATE())
END
AND
CASE
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -1, GETDATE())
ELSE GETDATE()
END
Paul Turley
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:E474B451-652E-405F-B698-D0D1642D907D@.microsoft.com...
>I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter
> number
> and current month number, but i'm at a loss as to how to tie this in with
> my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.sql
current week
last week
current month
current quarter
I have a dataset with today, monday of current week, current quarter number
and current month number, but i'm at a loss as to how to tie this in with my
report and how to put it into a parameter...any suggestions or referrals
would be most appreciated.Hi,
It is possible to ty values to a labels for a parameter through a query. All
you have to do is to create the dataset with two columns. One with you're
desired options and the other column with the corresponding number. In this
way it is possible to show the user the list of the four option, but use in
your report your own variables.
Jan Pieter Posthuma
"jmann" wrote:
> I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter number
> and current month number, but i'm at a loss as to how to tie this in with my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.|||This logic probably isn't right for your data but the basic idea should be
the same. For simplicity, I'm figuring that current week/month/quarter means
one week/month/quarter ago through today. You'll likely need to figure out
when each period begins using your data and make adjustments.
SELECT * FROM YourTables
WHERE OrderDate BETWEEN
CASE
WHEN @.Period = 'Current Week' THEN DATEADD(Week, -1, GETDATE())
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -2, GETDATE())
WHEN @.Period = 'Current Month' THEN CAST(CAST(MONTH(GETDATE()) AS
VarChar(2)) + '-1-' + CAST(YEAR(GETDATE()) AS VarChar(4)) AS DateTime)
WHEN @.Period = 'Current Quarter' THEN DATEADD(Quarter, -1, GETDATE())
END
AND
CASE
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -1, GETDATE())
ELSE GETDATE()
END
Paul Turley
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:E474B451-652E-405F-B698-D0D1642D907D@.microsoft.com...
>I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter
> number
> and current month number, but i'm at a loss as to how to tie this in with
> my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.sql
Date Parameters
Hello,
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>
Date Parameter with ReportViewer
Hi All,
I have the following code which works using the ReportViewer component. However how do I pass in the current date i.e Now?
When I use DateTime.Today.ToString I get the following error: The value provided for the report parameter 'SnapshotDate' is not valid for its type. SnapshoteDate is defined as DateTime.
If Not IsPostBack Then
ReportViewer1.ServerUrl = "http://devnet2/ReportServer"
ReportViewer1.ReportPath = "%2fCorVuReports/LAA/Pie&SnapshotDate=06/01/2004"
ReportViewer1.Toolbar = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False
ReportViewer1.Zoom = "75"
End If
I managed to solve this...
cheers
|||Could you please post your resolution, so others (like us) who encounter the problem could benefit from your wisdom.
Much appreciated.
date parameter in reporting services
Hi
I need to have the following result format into a date time parameter with
the current date
='1/07/2005 5:00:00 AM'
so when i schudule this report to run it will look at the current date at 5a
m.
Thankyou
toddTango
Can you eloborate what you expect
Regards
R.D
"Tango" wrote:
> Hi
> I need to have the following result format into a date time parameter with
> the current date
> ='1/07/2005 5:00:00 AM'
> so when i schudule this report to run it will look at the current date at
5am.
> Thankyou
> todd
>|||my expectation is that when i schedule this report to run it looks at the da
y
at 5am & reports back. All records in my database have a date of at 5am.
so the parameter output should be in datetimeformat showing d/MM/yy h:mm:ss
tt
Thanks
Todd
"R.D" wrote:
> Tango
> Can you eloborate what you expect
> Regards
> R.D
> "Tango" wrote:
>sql
I need to have the following result format into a date time parameter with
the current date
='1/07/2005 5:00:00 AM'
so when i schudule this report to run it will look at the current date at 5a
m.
Thankyou
toddTango
Can you eloborate what you expect
Regards
R.D
"Tango" wrote:
> Hi
> I need to have the following result format into a date time parameter with
> the current date
> ='1/07/2005 5:00:00 AM'
> so when i schudule this report to run it will look at the current date at
5am.
> Thankyou
> todd
>|||my expectation is that when i schedule this report to run it looks at the da
y
at 5am & reports back. All records in my database have a date of at 5am.
so the parameter output should be in datetimeformat showing d/MM/yy h:mm:ss
tt
Thanks
Todd
"R.D" wrote:
> Tango
> Can you eloborate what you expect
> Regards
> R.D
> "Tango" wrote:
>sql
Date Parameter
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
Hi ecn i,
You could write an expression\custom code block to set the parameter default value to be the last day of the current month.
|||Thanks for your reply.
Please give me the example (expression) for finding the last date of the current month
and set it as default.
|||=DateSerial(Year(Now()),Month(Now()) + 2,0)Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||
Thank You Very Much For Your Reply .
It Really help me a lot.
|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||To get the last day of this month, get the first date of next month, then subtract one day from it.
This expression gets the first day of next month:
Code Snippet
=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:
Code Snippet
=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")If you want to avoid a Format statement then you can use the Month and DatePart functions:
Code Snippet
=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.
|||Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")Wednesday, March 21, 2012
Date Parameter
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
Hi ecn i,
You could write an expression\custom code block to set the parameter default value to be the last day of the current month.
|||Thanks for your reply.
Please give me the example (expression) for finding the last date of the current month
and set it as default.
|||=DateSerial(Year(Now()),Month(Now()) + 2,0)Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||
Thank You Very Much For Your Reply .
It Really help me a lot.
|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||To get the last day of this month, get the first date of next month, then subtract one day from it.
This expression gets the first day of next month:
Code Snippet
=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:
Code Snippet
=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")If you want to avoid a Format statement then you can use the Month and DatePart functions:
Code Snippet
=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.
|||Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")Date Parameter
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
Hi ecn i,
You could write an expression\custom code block to set the parameter default value to be the last day of the current month.
|||Thanks for your reply.
Please give me the example (expression) for finding the last date of the current month
and set it as default.
|||=DateSerial(Year(Now()),Month(Now()) + 2,0)Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||
Thank You Very Much For Your Reply .
It Really help me a lot.
|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||To get the last day of this month, get the first date of next month, then subtract one day from it.
This expression gets the first day of next month:
Code Snippet
=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:
Code Snippet
=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")If you want to avoid a Format statement then you can use the Month and DatePart functions:
Code Snippet
=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.
|||Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")Date of first day of week for current week
seriously nowWhy so serious? Is the first day the first workday, or Sunday?
Monday, March 19, 2012
Date in the file name for a subscription
I'm in desperate need for insight into how I can use the current date, or
part of the current date, in the filename of a report delivered through a
data-driven subscription and snapshot? Any info is appreciated, apparently
this is possible but there's no info anywhere on how this can be completed
and my company is depending on a solution. I'm at the point where I don't
know what to say to them and I don't know where to look for a resolution for
this request. Thank you.
ChrisJust have the query used for the Data Driven subscription return the file
name. You can then have the sql statement get the current date.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <idroppeddabomb@.hotmail.com> wrote in message
news:e3zS1BiqEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I'm in desperate need for insight into how I can use the current date, or
> part of the current date, in the filename of a report delivered through a
> data-driven subscription and snapshot? Any info is appreciated, apparently
> this is possible but there's no info anywhere on how this can be completed
> and my company is depending on a solution. I'm at the point where I don't
> know what to say to them and I don't know where to look for a resolution
for
> this request. Thank you.
> Chris
>
part of the current date, in the filename of a report delivered through a
data-driven subscription and snapshot? Any info is appreciated, apparently
this is possible but there's no info anywhere on how this can be completed
and my company is depending on a solution. I'm at the point where I don't
know what to say to them and I don't know where to look for a resolution for
this request. Thank you.
ChrisJust have the query used for the Data Driven subscription return the file
name. You can then have the sql statement get the current date.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <idroppeddabomb@.hotmail.com> wrote in message
news:e3zS1BiqEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I'm in desperate need for insight into how I can use the current date, or
> part of the current date, in the filename of a report delivered through a
> data-driven subscription and snapshot? Any info is appreciated, apparently
> this is possible but there's no info anywhere on how this can be completed
> and my company is depending on a solution. I'm at the point where I don't
> know what to say to them and I don't know where to look for a resolution
for
> this request. Thank you.
> Chris
>
Date in current Month?
I want to know if @.MyDate (DateTime) is in the current month. This works,
but it's ugly. Any ideas to make it look nicer?
if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
datepart(month,current_timestamp) = datepart(month,@.MyDate))
/* Yes*/
else
/* No */Is this any prettier?
If CONVERT(char(6),@.MyDate,112) = CONVERT(char(6),CURRENT_TIMESTAMP,112)
/* yes */
Else
/* no */
Gert-Jan
JM wrote:
> I want to know if @.MyDate (DateTime) is in the current month. This works,
> but it's ugly. Any ideas to make it look nicer?
> if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
> else
> /* No */|||Yes, your's is much better since it doesn't have two parts. It's always
helpful whenever I try something, then get a push from an expert. I learn a
lot that way.
Thanks!
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:421A38B5.98921D2E@.toomuchspamalready.nl...
> Is this any prettier?
> If CONVERT(char(6),@.MyDate,112) = CONVERT(char(6),CURRENT_TIMESTAMP,112)
> /* yes */
> Else
> /* no */
> Gert-Jan
>
> JM wrote:
works,|||Try this:
If @.MyDate >= dateadd(month,datediff(month, '20000101', getdate()), '
20000101' )
and @.MyDate < dateadd(month,1+datediff(month, '20000101', getdate()), '
20000101' )
...
Steve Kass
Drew University
JM wrote:
>I want to know if @.MyDate (DateTime) is in the current month. This works,
>but it's ugly. Any ideas to make it look nicer?
>if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
>else
> /* No */
>
>
>|||Try,
if datediff(month, @.MyDate, GETDATE()) = 0
print 'yes'
else
print 'no'
AMB
"JM" wrote:
> I want to know if @.MyDate (DateTime) is in the current month. This works,
> but it's ugly. Any ideas to make it look nicer?
> if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
> else
> /* No */
>
>
>
but it's ugly. Any ideas to make it look nicer?
if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
datepart(month,current_timestamp) = datepart(month,@.MyDate))
/* Yes*/
else
/* No */Is this any prettier?
If CONVERT(char(6),@.MyDate,112) = CONVERT(char(6),CURRENT_TIMESTAMP,112)
/* yes */
Else
/* no */
Gert-Jan
JM wrote:
> I want to know if @.MyDate (DateTime) is in the current month. This works,
> but it's ugly. Any ideas to make it look nicer?
> if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
> else
> /* No */|||Yes, your's is much better since it doesn't have two parts. It's always
helpful whenever I try something, then get a push from an expert. I learn a
lot that way.
Thanks!
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:421A38B5.98921D2E@.toomuchspamalready.nl...
> Is this any prettier?
> If CONVERT(char(6),@.MyDate,112) = CONVERT(char(6),CURRENT_TIMESTAMP,112)
> /* yes */
> Else
> /* no */
> Gert-Jan
>
> JM wrote:
works,|||Try this:
If @.MyDate >= dateadd(month,datediff(month, '20000101', getdate()), '
20000101' )
and @.MyDate < dateadd(month,1+datediff(month, '20000101', getdate()), '
20000101' )
...
Steve Kass
Drew University
JM wrote:
>I want to know if @.MyDate (DateTime) is in the current month. This works,
>but it's ugly. Any ideas to make it look nicer?
>if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
>else
> /* No */
>
>
>|||Try,
if datediff(month, @.MyDate, GETDATE()) = 0
print 'yes'
else
print 'no'
AMB
"JM" wrote:
> I want to know if @.MyDate (DateTime) is in the current month. This works,
> but it's ugly. Any ideas to make it look nicer?
> if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
> else
> /* No */
>
>
>
Date handling in SQL Server
I need to make a decision regarding date handling to continue as is or reope
n
and modify completed development, if it is felt that current approach was no
t
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All program
s
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren’t we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach bei
ng
> used/ Any input you can provide will help us decide our forward direction
.
> Thanks.
>
>|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...[vbcol=seagreen]
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
>
n
and modify completed development, if it is felt that current approach was no
t
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All program
s
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren’t we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach bei
ng
> used/ Any input you can provide will help us decide our forward direction
.
> Thanks.
>
>|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...[vbcol=seagreen]
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
>
Date handling in SQL Server
I need to make a decision regarding date handling to continue as is or reopen
and modify completed development, if it is felt that current approach was not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren’t we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.
In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.
|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach being
> used/ Any input you can provide will help us decide our forward direction.
> Thanks.
>
>
|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...[vbcol=seagreen]
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
and modify completed development, if it is felt that current approach was not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren’t we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.
In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.
|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach being
> used/ Any input you can provide will help us decide our forward direction.
> Thanks.
>
>
|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...[vbcol=seagreen]
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
Date handling in SQL Server
I need to make a decision regarding date handling to continue as is or reopen
and modify completed development, if it is felt that current approach was not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why arenâ't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach being
> used/ Any input you can provide will help us decide our forward direction.
> Thanks.
>
>|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
>> In my opinion, when a data is unavailable/unknown, you should set it to
>> NULL, instead of hardcoding your applications to look for certain very
>> old
>> or very futuristic dates.
>> You could use new columns to indicate the status of rows, instead of
>> using
>> hardcoded date values.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
>> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
>> I need to make a decision regarding date handling to continue as is or
>> reopen
>> and modify completed development, if it is felt that current approach was
>> not
>> he best.
>> All development to date has been done with the use of two default dates.
>> There is one default date 01/01/1800 used for all date fields except
>> term_date. For term_date a forever date of 01/01/2900 is used. All
>> programs
>> identify terminated records as term_date less than 01/01/2900.
>> My questions are:
>> Why aren't we using Null values
>> Why two default dates instead of just one.
>> What standard does your company use? What is the most common approach
>> being
>> used/ Any input you can provide will help us decide our forward
>> direction.
>> Thanks.
>>
and modify completed development, if it is felt that current approach was not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why arenâ't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach being
> used/ Any input you can provide will help us decide our forward direction.
> Thanks.
>
>|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
>> In my opinion, when a data is unavailable/unknown, you should set it to
>> NULL, instead of hardcoding your applications to look for certain very
>> old
>> or very futuristic dates.
>> You could use new columns to indicate the status of rows, instead of
>> using
>> hardcoded date values.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
>> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
>> I need to make a decision regarding date handling to continue as is or
>> reopen
>> and modify completed development, if it is felt that current approach was
>> not
>> he best.
>> All development to date has been done with the use of two default dates.
>> There is one default date 01/01/1800 used for all date fields except
>> term_date. For term_date a forever date of 01/01/2900 is used. All
>> programs
>> identify terminated records as term_date less than 01/01/2900.
>> My questions are:
>> Why aren't we using Null values
>> Why two default dates instead of just one.
>> What standard does your company use? What is the most common approach
>> being
>> used/ Any input you can provide will help us decide our forward
>> direction.
>> Thanks.
>>
Sunday, March 11, 2012
Date function in DTS package
How can I make this work against an Access table through an ODBC dsn in my DTS package? I need to subtract 1 day from the current date.
Tried this, which is incomplete of course, (need to subtract the 1 day):
WHERE (TTDateTimeIn >= DATEDIFF(dd, 1, { fn CURDATE() }))Got this error:
[Microsoft][ODBC Microsoft Access Driver] Too few Parameters. Expected 1.I believe the function you should be using is DATEADD, not DATEDIFF. And you will need to enclose that dd in single quotes.
Terri|||That's correct, I finally figured it out. What added to my troubles was needing to pull yesterdays data after midnight, but I got it.
(TTDateTimeIn >= DATEADD('y', - 1, { fn CURDATE() }))It was painful building this DTS package using a DSN connection to get to locked MS Access tables.
Thanks.
Subscribe to:
Posts (Atom)