Date Calculations...

I have a field that contains date information, and sometimes time
information as well. I would like to be able to take that date and do a
calculation on it. Here are some examples of what is in the field:

01/12/2003 5:04:00 PM
24/11/2003
19/05/2003 6:30:00 AM

How can I take that date, then do a calculation like minus 5 days from the
date. I understand that I am to use the GETDATE() function, but below is
the SQL I have implemented.

SELECT Field1, Field2, Field3
FROM Table1
WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))

For some reason this works, and it will return results that occur on this
day, but it disregards the year. Now someone will probably ask "Why
convert, char(10), etc". To be honest, I do not know and I ended up
implementing it from some other Usenet posts that are out there. I was
trying to figure this out and I ended up with that working until I later
realized it was only caring about the day and month. Any ideas what I am
doing wrong here? I just want to return results that have the day being 5
minus the current day. I am not interested in time information.

Thanks if anyone can help, I am by far not experienced in SQL.Take a look at the dateadd function of sql. It will do what you want, just
supply the date.

Oscar...

"mene" <mene@.mene.nope> wrote in message
news:cyMjd.5889$hp3.615058@.read2.cgocable.net...
> I have a field that contains date information, and sometimes time
> information as well. I would like to be able to take that date and do a
> calculation on it. Here are some examples of what is in the field:
> 01/12/2003 5:04:00 PM
> 24/11/2003
> 19/05/2003 6:30:00 AM
> How can I take that date, then do a calculation like minus 5 days from the
> date. I understand that I am to use the GETDATE() function, but below is
> the SQL I have implemented.
> SELECT Field1, Field2, Field3
> FROM Table1
> WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))
> For some reason this works, and it will return results that occur on this
> day, but it disregards the year. Now someone will probably ask "Why
> convert, char(10), etc". To be honest, I do not know and I ended up
> implementing it from some other Usenet posts that are out there. I was
> trying to figure this out and I ended up with that working until I later
> realized it was only caring about the day and month. Any ideas what I am
> doing wrong here? I just want to return results that have the day being 5
> minus the current day. I am not interested in time information.
> Thanks if anyone can help, I am by far not experienced in SQL.

date calculations using a time hierarchy

Hi There

I am a relative newbie at SSAS 2005, but I've created a working cube and populated it with data.

I have a fact table with three date fields:
[record date]
[start date] - date the product started
[end date] - date the product was withdrawn
+ some other data fields
I use the [record date] to analyse my data with a server-populated time dimension. So far so good, it works fine.
However I need to look at how many days the products were active during the current time period. For exampe, if I'm in Q1 of 2005 and my product ran from 1st december 2004 to 15th january 2005, the answer is 15 days. This needs to be totaled across various other dimensions too.
I have tried a lot of different ways of doing it without much success. Any ideas on how to implement something like this?

ThanksCould you explain the schema, and the granularity of the fact table - for example, is there only 1 record per product, or 1 record per sales transaction? In the latter case, shouldn't the product start and end dates be associated with a Product dimension, rather than with each transaction?|||It's a simplified proof-of-concept project, so I can understand how it works before trying it out on the full database which is more complex.
These are financial products which have a start and end date. There is one record per product and they are to be analysed by various dimensions, for example product type, which would be the maximum granularity (not the products themselves). I also need to look at them by other time dimensions, for example when the records appear in the database, which may be any time after they have actually incepted. So I could have product type on rows and time on columns, and the cells would be the sum of all the days of activity for a given product type for that time period. Or I could have 'appearence time' on rows, so each cell would be the sum of active days for products during the colum time, for all products appearing in a given time frame.
Hope this is clear enough? I realise this is maybe a bit general but I'm only after a general approach strategy rather than an in-depth analysis! Thanks
oh and Merry Christmas!

Date Calculations

Hi Everyone,
I have got a problem with date calculation. I have a
procedure that all me to insert date into a Table based on user input. The
input is a Event Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w prior to the
event date as the reminder
My question is how do I calculate prior w of a certain Date..
e.g Event Date = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Please help...
ROOTry this: dateadd(w,-1,event_date)|||sorry abou the previous. Should be dateadd(ww,-1,event_date)|||Roplab wrote:
> Hi Everyone,
> I have got a problem with date calculation. I
> have a procedure that all me to insert date into a Table based on
> user input. The input is a Event Date and Reminder
> Example: if the user Enter an Event Date and choose to a reminder
> for a certain event... I need to calculate a date that will be a w
> prior to the event date as the reminder
> My question is how do I calculate prior w of a certain Date..
> e.g Event Date = 01/14/2005 I want the reminder to be calculate
> has Reminder=01/07/2005
> Please help...
> ROO
It's a lot easier to help if you provide DDL and sample data in the form of
insert statements (www.aspfaq.com/5006). As it now stands, I have to guess
at the data type and name of the "Event Date" column. Here is my solution
based on the guess that it is a datetime column (you should look up Using
Date and Time Data in SQL Books Online). This is an example using variables.
You should be able to convert it to a select statement if it is relevant to
your situation:
declare @.eventdate datetime
set @.eventdate='20050114'
select DATEADD(ww,-1,@.eventdate)
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi Everyone,
I have got a problem with date calculation. I have a procedure that all
me to insert date into a Table based on user input. The input is a Event
Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w prior to the
event date as the reminder
My question is how do I calculate prior w of a certain Date.. e.g Event
Date = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Below is my procedure:
CREATE PROCEDURE EventReminder
@.DocketID int,
@.EventName varchar(50),
@.Reminder int,
@.EventNumber int,
@.EventDate varchar(50)
AS
--Declare variables
Declare @.EventStartNum int,
@.EventReminderNum int,
@.EventDate1 datetime,
@.EventNum int
--Initialize the Variables
set @.EventStartNum = 0
set @.EventReminderNum = 0
set @.EventNum = -1
--Delete the Reminder if the DocketID already exist
delete from reminder where DocketID = @.DocketID
--Start the loop
while @.EventStartNum < @.EventNumber
Begin --Start Begin
set @.EventStartNum = @.EventStartNum + 1
--Wly Reminder
if @.EventNumber = 1
begin
while @.Reminder >
@.EventReminderNum
begin
--Increment of the w
set @.EventReminderNum =
@.EventReminderNum + 1
set @.EventDate1 = DATEADD(w,
@.EventReminderNum, @.EventDate)
insert into Reminder
(DocketID, EventDate, EventName, Reminder)
Values
(@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
set @.EventNum = @.EventNum - 1
end
end
--print 'The counter is ' +
convert(varchar(50),@.EventDate1,101)
end --End Begin
GO
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OjAul7SDFHA.1264@.TK2MSFTNGP12.phx.gbl...
> Roplab wrote:
> It's a lot easier to help if you provide DDL and sample data in the form
of
> insert statements (www.aspfaq.com/5006). As it now stands, I have to guess
> at the data type and name of the "Event Date" column. Here is my solution
> based on the guess that it is a datetime column (you should look up Using
> Date and Time Data in SQL Books Online). This is an example using
variables.
> You should be able to convert it to a select statement if it is relevant
to
> your situation:
> declare @.eventdate datetime
> set @.eventdate='20050114'
> select DATEADD(ww,-1,@.eventdate)
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>

Date calculations

I'm trying to write a query to select all data for the last 3 months
from a table. I want to be able to put it into a stored procedure and
schedule it to run once a month. So since I want it to be automated I
don't want to pass it start and end date every time it runs. I'm so not
good with using the date functions. Can anyone help out'
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!SELECT columns FROM table WHERE datetimeColumn >= DATEADD(MONTH, -3,
GETDATE())
If you want to go on specific date / midnight boundaries, see
http://www.aspfaq.com/2444 for some shortcuts.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:ui22exSEEHA.580@.TK2MSFTNGP11.phx.gbl...
> I'm trying to write a query to select all data for the last 3 months
> from a table. I want to be able to put it into a stored procedure and
> schedule it to run once a month. So since I want it to be automated I
> don't want to pass it start and end date every time it runs. I'm so not
> good with using the date functions. Can anyone help out'
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||You can use the DATEADD() function adding minus three months from the
current datetime, for which you use the CURRENT_TIMESTAMP function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:ui22exSEEHA.580@.TK2MSFTNGP11.phx.gbl...
> I'm trying to write a query to select all data for the last 3 months
> from a table. I want to be able to put it into a stored procedure and
> schedule it to run once a month. So since I want it to be automated I
> don't want to pass it start and end date every time it runs. I'm so not
> good with using the date functions. Can anyone help out'
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Date And Time Calculations

Hi
I have to calculate the data and time differences between 2 different
fields. I'm using the Datediiff, function, for each denomination.
i,e
I have a DateDiff for the:
Days
Hours
Minutes
This works perfectly fine, except it brings back the difference for each
type:
i.e For a date and time difference of 1 Day, 1 Hour and 1 Minute it brings
me back :
DAYS : 1
HOURS : 24
MINUTES :1501
How do I work with this then, since 2 Day, 2 Hours and 2 Minutes, would be :
DAYS : 2
HOURS : 48
MINUTES : 2882
Whats the easiest way to convert this values in to one readable value?
Any help would be much appreciated.
--
Kind Regards
Rikesh
(W2K-SP3 / SQL2K)one way would be to start with the DateDiff in minutes:
then proceed as follows:
DECLARE @.min int, @.hr int, @.day int
SET @.min = 3002
SELECT @.day = @.min/(1440), @.min = @.min % 1440
SELECT @.hr = @.min/60, @.min = @.min % 60
SELECT @.day, @.hr, @.min
>--Original Message--
>Hi
>I have to calculate the data and time differences between
2 different
>fields. I'm using the Datediiff, function, for each
denomination.
>i,e
>I have a DateDiff for the:
>Days
>Hours
>Minutes
>This works perfectly fine, except it brings back the
difference for each
>type:
>i.e For a date and time difference of 1 Day, 1 Hour and 1
Minute it brings
>me back :
>DAYS : 1
>HOURS : 24
>MINUTES :1501
>How do I work with this then, since 2 Day, 2 Hours and 2
Minutes, would be :
>DAYS : 2
>HOURS : 48
>MINUTES : 2882
>Whats the easiest way to convert this values in to one
readable value?
>Any help would be much appreciated.
>
>--
>Kind Regards
>Rikesh
>(W2K-SP3 / SQL2K)
>
>.
>|||Thanks for the suggestion Joe, I got a solution in the form of a UDF, here's
the code:
CREATE FUNCTION dbo.minutesToWords
(
@.minutes INT
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.word VARCHAR(255)
IF @.minutes <= 0
SET @.word = 'Negative minutes?'
ELSE
BEGIN
SET @.word = ''
IF @.minutes >= (24*60)
SET @.word = @.word + RTRIM(@.minutes/(24*60))+' day(s), '
SET @.minutes = @.minutes % (24*60)
IF @.minutes >= 60
SET @.word = @.word + RTRIM(@.minutes/60)+' hour(s), '
SET @.minutes = @.minutes % 60
SET @.word = @.word + RTRIM(@.minutes)+' minute(s).'
END
RETURN @.word
END
GO
DECLARE @.diff INT
SET @.diff = DATEDIFF(MINUTE, '2003-11-30 5:43 PM', '2003-12-01 7:45 PM')
SELECT dbo.minutesToWords(@.diff)
SET @.diff = DATEDIFF(MINUTE, '2003-11-24 4:43 AM', '2003-12-01 7:45 PM')
SELECT dbo.minutesToWords(@.diff)
DROP FUNCTION dbo.minutesToWords
GO
But thanks for time in the forst place.
Cheers
Rikesh
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:a7e701c3b835$9e1b42b0$a601280a@.phx.gbl...
> one way would be to start with the DateDiff in minutes:
> then proceed as follows:
> DECLARE @.min int, @.hr int, @.day int
> SET @.min = 3002
> SELECT @.day = @.min/(1440), @.min = @.min % 1440
> SELECT @.hr = @.min/60, @.min = @.min % 60
> SELECT @.day, @.hr, @.min
>
> >--Original Message--
> >Hi
> >
> >I have to calculate the data and time differences between
> 2 different
> >fields. I'm using the Datediiff, function, for each
> denomination.
> >
> >i,e
> >
> >I have a DateDiff for the:
> >Days
> >Hours
> >Minutes
> >
> >This works perfectly fine, except it brings back the
> difference for each
> >type:
> >
> >i.e For a date and time difference of 1 Day, 1 Hour and 1
> Minute it brings
> >me back :
> >
> >DAYS : 1
> >HOURS : 24
> >MINUTES :1501
> >
> >How do I work with this then, since 2 Day, 2 Hours and 2
> Minutes, would be :
> >
> >DAYS : 2
> >HOURS : 48
> >MINUTES : 2882
> >
> >Whats the easiest way to convert this values in to one
> readable value?
> >
> >Any help would be much appreciated.
> >
> >
> >--
> >Kind Regards
> >
> >Rikesh
> >(W2K-SP3 / SQL2K)
> >
> >
> >
> >.
> >