Date range + parallel time

Hi,

I'm quite new to MDX and found a problem I can not solve. I have a fact table which contains a start date and an end date, plus several measures. I also have a time dimension, with years and months. I need to dynamically build an mdx query that, given a year and a month, would show any measure in the fact table whose start date is lower than the given date and the end date is higher than the given date. To complicate things a bit, the same query must show the same measure in the previous year to the given date. Both results must be in the same axis.

We know how to show each result separately, using date ranges in the WHERE clause, but have no idea about how to combine both results.

Thanks

--eduardj

Finally solved it.

I created a date range dimension, with all the date ranges in the original fact table, and a measure-less fact table with a relationship with the new date range dimension and the old time dimension. After that, I created a Many-to-Many relationship between the original fact table and the time dimension through the newly created fact table. Besides, I created a hierarchy in the time dimension which related the times (parallel) that always had to be shown together.

Kind of messy, but it works beautifully.

thanks

--eduardj

date queries

I have written a report that contains multiple queries that displays the
number of applications we submit month by month to various entities. I wrote
the report last year and have been asked to create it again this year.
I know that this is going to be require every year, and I don't want to
write it again.
My problem is that in order to get the month by month data, I had to write
"WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
tDocHistory.ChangeDate)), CONVERT(CHAR(4),
DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
I would like to be able to allow my users to select the year they wish to
view.
I wanted to replace the 2008 with % in the statement:
(tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
and then filter the datepart for the year by writing:
HAVING (tDocHistory.NewStatus = 'wv' OR
tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
tDocHistory.ChangeDate))
LIKE @.Year)
Do you know any way I could make this work?
--
SamyraWhy dont you use report parameter to select the date.
On Jan 12, 8:28=A0pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> I have written a report that contains multiple queries that displays the
> number of applications we submit month by month to various entities. =A0I =wrote
> the report last year and have been asked to create it again this year.
> I know that this is going to be require every year, and I don't want to
> write it again.
> My problem is that in order to get the month by month data, I had to =A0wr=ite
> "WHERE =A0 =A0 (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.ChangeDate)), CONV=ERT(CHAR(4),
> DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> I would like to be able to allow my users to select the year they wish to
> view.
> I wanted to replace the 2008 with % in the statement:
> =A0 (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> and then filter the datepart for the year by writing:
> HAVING =A0 =A0 =A0(tDocHistory.NewStatus =3D 'wv' OR
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.NewStatus =3D 'dcs=') AND (tCompany.CoName
> LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> tDocHistory.ChangeDate))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LIKE @.Year)
> Do you know any way I could make this work?
> --
> Samyra|||I would love to, but the report is built with text boxes with a separate
query in each box.
I am looking at a table that contains historical data for each record in the
database, not current data, so I wasn't able to build a table or a matrix
report that would display the data they wanted to see.
--
Samyra
"Sridar K" wrote:
> Why dont you use report parameter to select the date.
> On Jan 12, 8:28 pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> > I have written a report that contains multiple queries that displays the
> > number of applications we submit month by month to various entities. I wrote
> > the report last year and have been asked to create it again this year.
> >
> > I know that this is going to be require every year, and I don't want to
> > write it again.
> >
> > My problem is that in order to get the month by month data, I had to write
> >
> > "WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> > GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> > tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> > tDocHistory.ChangeDate)), CONVERT(CHAR(4),
> > DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> >
> > I would like to be able to allow my users to select the year they wish to
> > view.
> >
> > I wanted to replace the 2008 with % in the statement:
> > (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> >
> > and then filter the datepart for the year by writing:
> >
> > HAVING (tDocHistory.NewStatus = 'wv' OR
> > tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
> > LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> > tDocHistory.ChangeDate))
> > LIKE @.Year)
> >
> > Do you know any way I could make this work?
> >
> > --
> > Samyra
>

date parsing

Hello,

I have a source with two smalldatetime fields, the first field contains 8/1/2006 12:00:00 AM, and the second field contains 8/10/2006 7:57:00 PM.

I would like to have the date from the first field and the time from the second field. No chance of changing the source system to do this for me.

What I have so far works, except the time portion is converted to 19:57:00 instead of 7:57:00 p.m. Any Ideas? My expression is below.

(DT_STR,2,1252)DATEPART("month",FIELD1) + "/" + (DT_STR,2,1252)DATEPART("Day",FIELD1) + "/" + (DT_STR,4,1252)DATEPART("Year",FIELD1) + " " + (DT_STR,2,1252)DATEPART("Hour",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("Minute",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("SS",FIELD2)

Thanks!

Try casting the values as DT_DBTIME or DT_DBDATE.

-Jamie

|||I added a data conversion transform to convert the output from the derived column transform to a database timestamp and it appears to be working. I probably could do all of this in one transformation, but this will work for now. Thanks!|||

You could wrap the cast to DT_DBTIMESTAMP around your whole expression in the derived column to avoid using the data convert downstream.

Mark

|||Thanks. I tried that and it worked great.

Date Parameter

My report contains a date-time parameter. When the report is run it displays
both the date and time portions in my date drop down box e.g. 01/01/2004
12:00:00 AM.
How would I change this to only display the 01/01/2004 ?
Note: I want to maintain system localized date format.Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> My report contains a date-time parameter. When the report is run it
displays
> both the date and time portions in my date drop down box e.g. 01/01/2004
> 12:00:00 AM.
> How would I change this to only display the 01/01/2004 ?
> Note: I want to maintain system localized date format.|||Hi Ravi:
Where should I place it?
"Ravi Mumulla (Microsoft)" wrote:
> Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > My report contains a date-time parameter. When the report is run it
> displays
> > both the date and time portions in my date drop down box e.g. 01/01/2004
> > 12:00:00 AM.
> >
> > How would I change this to only display the 01/01/2004 ?
> >
> > Note: I want to maintain system localized date format.
>
>|||Click on the control you want to format (the particular field in the table
control, or a texbox for example) and go to properties, format, select
expression and put this in when the expression box comes up.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> Hi Ravi:
> Where should I place it?
> "Ravi Mumulla (Microsoft)" wrote:
> > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > My report contains a date-time parameter. When the report is run it
> > displays
> > > both the date and time portions in my date drop down box e.g.
01/01/2004
> > > 12:00:00 AM.
> > >
> > > How would I change this to only display the 01/01/2004 ?
> > >
> > > Note: I want to maintain system localized date format.
> >
> >
> >|||I am attempting to chnage the date format in the parameter control bar... as
far as I can see there is no properties selection to choose from.
"Bruce L-C [MVP]" wrote:
> Click on the control you want to format (the particular field in the table
> control, or a texbox for example) and go to properties, format, select
> expression and put this in when the expression box comes up.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > Hi Ravi:
> >
> > Where should I place it?
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > My report contains a date-time parameter. When the report is run it
> > > displays
> > > > both the date and time portions in my date drop down box e.g.
> 01/01/2004
> > > > 12:00:00 AM.
> > > >
> > > > How would I change this to only display the 01/01/2004 ?
> > > >
> > > > Note: I want to maintain system localized date format.
> > >
> > >
> > >
>
>|||Ahh, sorry. Everybody answering you was answering with regards to the
report. The parameter tool bar can not be modified as far as I know. You
have the option of creating your own web page and then integrate into RS
using URL control or web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> I am attempting to chnage the date format in the parameter control bar...
as
> far as I can see there is no properties selection to choose from.
> "Bruce L-C [MVP]" wrote:
> > Click on the control you want to format (the particular field in the
table
> > control, or a texbox for example) and go to properties, format, select
> > expression and put this in when the expression box comes up.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > Hi Ravi:
> > >
> > > Where should I place it?
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > >
> > > > --
> > > > Ravi Mumulla (Microsoft)
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > My report contains a date-time parameter. When the report is run
it
> > > > displays
> > > > > both the date and time portions in my date drop down box e.g.
> > 01/01/2004
> > > > > 12:00:00 AM.
> > > > >
> > > > > How would I change this to only display the 01/01/2004 ?
> > > > >
> > > > > Note: I want to maintain system localized date format.
> > > >
> > > >
> > > >
> >
> >
> >|||Crystal Reports has the option to convert all date-time to dates.
This allows you to bring up all records which occured between a certain date
range irrespective of the time.
Having the time portion in RS causes problems. If I want to pull up all
records which occured from 1 jan 2004 to 1 jan 2004. RS views this as being 1
jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other 23:59
hours on Jan 1.
Are you saying this is not possible in RS without a custom webpage?
"Bruce L-C [MVP]" wrote:
> Ahh, sorry. Everybody answering you was answering with regards to the
> report. The parameter tool bar can not be modified as far as I know. You
> have the option of creating your own web page and then integrate into RS
> using URL control or web services.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > I am attempting to chnage the date format in the parameter control bar...
> as
> > far as I can see there is no properties selection to choose from.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Click on the control you want to format (the particular field in the
> table
> > > control, or a texbox for example) and go to properties, format, select
> > > expression and put this in when the expression box comes up.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > Hi Ravi:
> > > >
> > > > Where should I place it?
> > > >
> > > > "Ravi Mumulla (Microsoft)" wrote:
> > > >
> > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > >
> > > > > --
> > > > > Ravi Mumulla (Microsoft)
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > My report contains a date-time parameter. When the report is run
> it
> > > > > displays
> > > > > > both the date and time portions in my date drop down box e.g.
> > > 01/01/2004
> > > > > > 12:00:00 AM.
> > > > > >
> > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > >
> > > > > > Note: I want to maintain system localized date format.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||If you are going against SQL Server database it only has a datetime data
type. It does not have a separate date and time datatypes. I do all my
reports >= fromdate and < todate. So if you want a day's worth of data it is
between 1/1/04 00:00:00 and 1/2/04 00:00:00
One point, you can always ignore the time portion. The report parameter and
the query parameter are two different thing and you can map the query
parameter to an expression which takes the report parameter and strips the
time part of it. The downside of this is that the user will see the time in
the parameter bar.
One other point, you can have a text parameter instead of the date
parameter. The user puts in the date as text and then you do whatever you
want with it during the assignment to the query parameter.
The portal that RS provides in version 1 is limited with how you can
customize it. Hopefully we will see some improvements. For instance it sure
would be nice to format the parameters (as you mentioned) or add custom
error checking to it. Or a datepicker would be nice. No special knowledge of
what the improvements would be. Just that I do agree with you that more
control and options with it would be nice.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:53A33C93-0483-4305-BBF2-988B618FFF2D@.microsoft.com...
> Crystal Reports has the option to convert all date-time to dates.
> This allows you to bring up all records which occured between a certain
date
> range irrespective of the time.
> Having the time portion in RS causes problems. If I want to pull up all
> records which occured from 1 jan 2004 to 1 jan 2004. RS views this as
being 1
> jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other
23:59
> hours on Jan 1.
> Are you saying this is not possible in RS without a custom webpage?
> "Bruce L-C [MVP]" wrote:
> > Ahh, sorry. Everybody answering you was answering with regards to the
> > report. The parameter tool bar can not be modified as far as I know. You
> > have the option of creating your own web page and then integrate into RS
> > using URL control or web services.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > > I am attempting to chnage the date format in the parameter control
bar...
> > as
> > > far as I can see there is no properties selection to choose from.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Click on the control you want to format (the particular field in the
> > table
> > > > control, or a texbox for example) and go to properties, format,
select
> > > > expression and put this in when the expression box comes up.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > > Hi Ravi:
> > > > >
> > > > > Where should I place it?
> > > > >
> > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > >
> > > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > > >
> > > > > > --
> > > > > > Ravi Mumulla (Microsoft)
> > > > > > SQL Server Reporting Services
> > > > > >
> > > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > > My report contains a date-time parameter. When the report is
run
> > it
> > > > > > displays
> > > > > > > both the date and time portions in my date drop down box e.g.
> > > > 01/01/2004
> > > > > > > 12:00:00 AM.
> > > > > > >
> > > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > > >
> > > > > > > Note: I want to maintain system localized date format.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||I know this is an old thread but in my searches for an answer.....
Anyway I figured out a solution:
=Today.ToShortDateString()
or if you need to add days:
=Today.AddDays(-2).ToShortDateString()
Hope this helps
"Bruce L-C [MVP]" wrote:
> If you are going against SQL Server database it only has a datetime data
> type. It does not have a separate date and time datatypes. I do all my
> reports >= fromdate and < todate. So if you want a day's worth of data it is
> between 1/1/04 00:00:00 and 1/2/04 00:00:00
> One point, you can always ignore the time portion. The report parameter and
> the query parameter are two different thing and you can map the query
> parameter to an expression which takes the report parameter and strips the
> time part of it. The downside of this is that the user will see the time in
> the parameter bar.
> One other point, you can have a text parameter instead of the date
> parameter. The user puts in the date as text and then you do whatever you
> want with it during the assignment to the query parameter.
> The portal that RS provides in version 1 is limited with how you can
> customize it. Hopefully we will see some improvements. For instance it sure
> would be nice to format the parameters (as you mentioned) or add custom
> error checking to it. Or a datepicker would be nice. No special knowledge of
> what the improvements would be. Just that I do agree with you that more
> control and options with it would be nice.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:53A33C93-0483-4305-BBF2-988B618FFF2D@.microsoft.com...
> > Crystal Reports has the option to convert all date-time to dates.
> >
> > This allows you to bring up all records which occured between a certain
> date
> > range irrespective of the time.
> >
> > Having the time portion in RS causes problems. If I want to pull up all
> > records which occured from 1 jan 2004 to 1 jan 2004. RS views this as
> being 1
> > jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other
> 23:59
> > hours on Jan 1.
> >
> > Are you saying this is not possible in RS without a custom webpage?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Ahh, sorry. Everybody answering you was answering with regards to the
> > > report. The parameter tool bar can not be modified as far as I know. You
> > > have the option of creating your own web page and then integrate into RS
> > > using URL control or web services.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > > > I am attempting to chnage the date format in the parameter control
> bar...
> > > as
> > > > far as I can see there is no properties selection to choose from.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Click on the control you want to format (the particular field in the
> > > table
> > > > > control, or a texbox for example) and go to properties, format,
> select
> > > > > expression and put this in when the expression box comes up.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > > > Hi Ravi:
> > > > > >
> > > > > > Where should I place it?
> > > > > >
> > > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > > >
> > > > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > > > >
> > > > > > > --
> > > > > > > Ravi Mumulla (Microsoft)
> > > > > > > SQL Server Reporting Services
> > > > > > >
> > > > > > > This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > rights.
> > > > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > > > My report contains a date-time parameter. When the report is
> run
> > > it
> > > > > > > displays
> > > > > > > > both the date and time portions in my date drop down box e.g.
> > > > > 01/01/2004
> > > > > > > > 12:00:00 AM.
> > > > > > > >
> > > > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > > > >
> > > > > > > > Note: I want to maintain system localized date format.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

Date matching

I have done this twice now. I am sure I will do it again.

I have a date dimension. Among other things the dimension contains columns for a meaningless id and raw date. If I want to translate a date to a the meaningless id for storage in a fact table, I use a look up component. All fine and dandy until I run the data flow and all of the look up's fail.

Now what could be wrong with that? I look at the redirected rows and for a split second think, what is wrong with that data? Then it hits me. There is time information in the source and not in the date dimension.

Wouldn't it be nice if I could match on just the date! Off I go to put in another data conversion component for the lookup.

I know some of you have written custom components. How hard would that be for me to do?So you need to translate date in the source to raw date which you could then lookup in the lookup table? Can you do it with expression through Derived Column transform? If no, then writing custom component would be right. In C# it's not that difficult, 1 - 2 pages of code in one class. You can find some examples in Books Online.|||Yes, I would have used a derived column as well.

regards,
ash|||

Ash Sharma wrote:

Yes, I would have used a derived column as well.

The only successful way I have found is the following equation

(dt_wstr,2)DAY(dtPeriodStartDate) + "/" + (dt_wstr,2)MONTH(dtPeriodStartDate) + "/" + (dt_wstr,4)YEAR(dtPeriodStartDate)

And setting the result column type to date timestamp.

Lets just say this gets old fast. I am open to better ways.|||Ummm...I'd say this IS the best way. What is the problem that you have with it? Its only 1 extra component after all, and its a whizzy derived column transform as well - it shouldn't cause any perf problems!!

-Jamie|||

Jamie Thomson wrote:

What is the problem that you have with it?

My dislike is the copy paste nature of the work and potential for slight errors.

I actually have a fair number of such translations to do.|||So you'd like to be able to share (i.e. reuse - there's that magic word again )expressions across different places?

I think that's a great idea. Possibly a DCR for betaplace? Ashh/Kirk?

-Jamie|||In CTP 15, the FriendlyExpression property on columns in derived column is settable via dataflow property expressions. So conceivably, you could set this via configurations, or from one string variable that will evaluate to desired expression. Of course, you would need to now configure all the derived column expressions that would recieve this common meta-expression, but at least you would only have to edit the common expression in one place...

Date lookup in SSIS

How do I perform a date lookup in SSIS. I have a date with time component in it. This has to be looked-up with a table that contains only a date element.

You need convert the fields into varchar and do the comparison or you can convert both the fields to similar date formatted datetime type and do the comparison.

Thanks,

S Suresh

|||

I tried converting to varchar and it does not work well. There should be some other elegant way of doing this. To help understand the problem, I have created two tables table_1 and table_2. Table_1 is the source table with one column DateWithTime of type (datetime). Table_2 is the lookup table with columns DateSK of type (int) and another column DateAlone of type (smalldatetime).

I am taking the column DateWithTime from table_1 and looking it up with DateAlone from table_2 to get DateSK.

I do not know the right way to lookup date fields. Should I compare day, month and year separately to get DateSK.

Thanks,

Vijay

|||

I commonly use a slight cheat on this one, if you make the integer key of your lookup table the difference in days from 1 Jan 1900 then you can calculate the key instead of looking it up.

You can also use the same trick with the time portion of neccessary (do the diff in seconds).

Hope that helps you

Philip

|||

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

|||

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

|||

Philip Coupar wrote:

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

yyyy--mm-dd is unambiguous.

Date Help

I have a table that contains 5342 records where it is titled:
effbegdat. The table information is 12/30/1899 08:00:00
The date information is all the same, but times are different. I am
attempting to only alter the date. Or update the dates and leave the times
as they are set in the table. This is the formula I am using and it is not
working. I have skimmed through countless sql help sites and can't find a
fix. Any help would be appreciated. Here is my code:
update relent
set effbegdat = datepart(yy,1970) + datepart(m, 1) + datepart(d, 1) where
effbegdat like '%1899%'
Message posted via http://www.webservertalk.comNot sure why you are using the datepart on the SET part, just set the
value like below, you also might want to do the update WHERE the
datepart is 1899, hope this helps.
update relent
set effbeddat = CAST('1/1/1970 AS DATETIME)
where datepart(yyyy, effbegdat) = 1899|||I assume that this is a DATETIME column. Try this:
UPDATE effbegdat
SET dt = DATEADD(D,25569,dt)
WHERE dt >= '18991230'
AND dt < '18991231'
David Portas
SQL Server MVP
--|||I don't think you can use datepart directly on 'SET' . you'll have to use it
in the where clause.
"David Portas" wrote:

> I assume that this is a DATETIME column. Try this:
> UPDATE effbegdat
> SET dt = DATEADD(D,25569,dt)
> WHERE dt >= '18991230'
> AND dt < '18991231'
> --
> David Portas
> SQL Server MVP
> --
>
>|||That changed it, but it also changed the time portion and they want the
time in all records to remain as inputted by the users. I know how to
change a normal column, but just not parts in a column. This column header
is:
effbegdat
--
12/30/1899 08:00:00
12/30/1899 09:00:00
12/30/1899 07:00:10
And so on. They just want the date portion changed and the times to be left
alone. The 2 postings placed in response didn't work. They both changed the
time portions of the column.
Message posted via http://www.webservertalk.com|||I understand what you put in the system. What does this portion mean or
equate to:
(D,25569,dt)
I know the D stands for day and the dt is the variable but what is the
25569?
Message posted via http://www.webservertalk.com|||You can assign any valid expression to a column with UPDATE... SET. Try it
out:
CREATE TABLE effbegdat (dt DATETIME PRIMARY KEY)
INSERT INTO effbegdat
SELECT '1899-12-30T08:00:00'
UPDATE effbegdat
SET dt = DATEADD(D,25569,dt)
WHERE dt >= '18991230'
AND dt < '18991231'
SELECT dt FROM effbegdat
Result:
(1 row(s) affected)
(1 row(s) affected)
dt
--
1970-01-01 08:00:00.000
(1 row(s) affected)
David Portas
SQL Server MVP
--|||25569 is the difference in days between 1899-12-30 and 1970-01-01. DATEADD
just adds on that number of days. The time part is ignored and will not be
changed (assuming I was right and the column is in fact a DATETIME).
David Portas
SQL Server MVP
--|||The dates need an adjustment of datediff(day,effbegdat,'19700101'):
update relent set
effbegdat = effbegdat + datediff(day,effbegdat,'19700101')
where effbegdat >= '18991230'
and effbegdat < '18991231'
This is identical to David's suggestion, but you don't
have to wonder what 25569 is for.
You could also do this, if you want more things to wonder
about!
update relent set
effbegdat = effbegdat + 25569
where effbegdat >= -2
and effbegdat < -1
Steve Kass
Drew University
tina via webservertalk.com wrote:

>I have a table that contains 5342 records where it is titled:
>effbegdat. The table information is 12/30/1899 08:00:00
>The date information is all the same, but times are different. I am
>attempting to only alter the date. Or update the dates and leave the times
>as they are set in the table. This is the formula I am using and it is not
>working. I have skimmed through countless sql help sites and can't find a
>fix. Any help would be appreciated. Here is my code:
>update relent
>set effbegdat = datepart(yy,1970) + datepart(m, 1) + datepart(d, 1) where
>effbegdat like '%1899%'
>
>|||tina
CREATE TABLE #Test
(
col DATETIME NOT NULL
)
INSERT INTO #Test VALUES ('2005-02-03 08:04:10')
INSERT INTO #Test VALUES ('2005-02-03 09:10:20')
INSERT INTO #Test VALUES ('2005-02-03 15:25:30')
UPDATE #Test SET col=CAST('20050225'AS DATETIME)+CONVERT(CHAR(10),col,108)
DROP TABLE #Test
"tina via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:8d994f2203b1490eb08bc98663ff5b4e@.SQ
webservertalk.com...
> I have a table that contains 5342 records where it is titled:
> effbegdat. The table information is 12/30/1899 08:00:00
> The date information is all the same, but times are different. I am
> attempting to only alter the date. Or update the dates and leave the times
> as they are set in the table. This is the formula I am using and it is not
> working. I have skimmed through countless sql help sites and can't find a
> fix. Any help would be appreciated. Here is my code:
> update relent
> set effbegdat = datepart(yy,1970) + datepart(m, 1) + datepart(d, 1) where
> effbegdat like '%1899%'
> --
> Message posted via http://www.webservertalk.com

date from datetime field

Hi

What is the best practice to get the date from a smalldatetime field without the time.

The table contains 5 minute readings for energy consumption in the column period.

Now i need to get all the readings form some dates.

SELECT dbo.TBL_Data.*
FROM dbo.TBL_Data
WHERE (Period IN (CONVERT(DATETIME, '2003-12-31', 102), CONVERT(DATETIME, '2004-01-01', 102)))

this result contains only the readings for the timestamp 00:00

so how to select the whole day ?

kind regards

piet1. "IN" keyword only checks if the values are EQUAL to any of the values listed, not in between the two values (check for the BETWEEN operator or use a combination of greater-than and less-than), and

2. Also try explicitly setting the time value for each convert (check my syntax) so that you don't rely on the default time setting:

WHERE Period > CONVERT(DATETIME, '2003-12-31 00:00:00', 102) AND Period < CONVERT(DATETIME, '2003-12-31 12:59:59', 102)

Date formatting - Really newbie question

I have a unbelievably stupid problem that I can't figure out. My table is
imported from access and contains a column with data type datetime. I want
to be able to sum the data (which is a door counter for our store) to show
me all of the traffic for the day, then display the date as 1/1/2007 instead
of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
BOL help on this topic)...
convert(smalldatetime, [Date], 101)
No matter what I put in the style portion of the select statement it has no
impact on my output format. I suppose I could convert this to a varchar and
trim the results but that seems to be overkill (in addition to beiing a poor
solution).
Solved this. If anyone else is as inexperienced as I am and has this
problem, you can use the following method to change the output of the
datetime in this method.
Select CONVERT(varchar(20), [Date] as DT
will change this
1/30/2007 10:00:00
to this
1/30/2007
"Chuck G." wrote:

> I have a unbelievably stupid problem that I can't figure out. My table is
> imported from access and contains a column with data type datetime. I want
> to be able to sum the data (which is a door counter for our store) to show
> me all of the traffic for the day, then display the date as 1/1/2007 instead
> of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
> BOL help on this topic)...
> convert(smalldatetime, [Date], 101)
> No matter what I put in the style portion of the select statement it has no
> impact on my output format. I suppose I could convert this to a varchar and
> trim the results but that seems to be overkill (in addition to beiing a poor
> solution).
>
|||I don't quite see how that works (20 is too long, and the is only one
parenthesis). To
convert a datetime value to the same date at midnight, this is one solution:
dateadd(day,datediff(day,0,[Date]),0)
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Chuck G. wrote:
[vbcol=seagreen]
>Solved this. If anyone else is as inexperienced as I am and has this
>problem, you can use the following method to change the output of the
>datetime in this method.
>Select CONVERT(varchar(20), [Date] as DT
>will change this
>1/30/2007 10:00:00
>to this
>1/30/2007
>"Chuck G." wrote:
>

Date Format Conversions in SSIS

Hi

I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.

I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.

any suggestions will be gratefully recieved.

Best regards

It's just string manipulation. Add a derived column transformation, and manipulate the input string (using SUBSTRING, etc.) into a new column, casting it as the appropriate type.

Greg.

date field

I have a database table that contains a date field.

I don't know how to construct an sql query that returns the record that has the next closest date (in the future) to the actual date.

I know I need to brush up on sql but any help would be greatly appreciated.

Thanks

RamilaSelect Top 1 DateField From YourTable Where DateField > GetDate() Order By DateField

books online (sql server help files) and www.sqlcourse.com

edited: if you want the next day, I'd go with using DateAdd() function to assist in managing the amount of increate you wish to add to the DateField's criteria.

Date Difference

A table has 2 columns (Date and Trans). Date has the date of the
transaction, Trans contains whether the transacion was an invoice or a
payment.
I need to determine how long customers are taking to pay their bills.
How can I find the difference in the invoice date and the payment date
if both dates are part of the same field?Assuming there is some key value which shows that the 2 rows are connected
( and I am calling the field KEY)..
Select datediff(dd,i.[date],p.[date]) from thetable i inner join thetable p
on i.key = p.key
WHere whatever selection criteria you wish to use...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bunckles" <chris.bunch@.gmail.com> wrote in message
news:1117648478.631189.171300@.z14g2000cwz.googlegroups.com...
>A table has 2 columns (Date and Trans). Date has the date of the
> transaction, Trans contains whether the transacion was an invoice or a
> payment.
> I need to determine how long customers are taking to pay their bills.
> How can I find the difference in the invoice date and the payment date
> if both dates are part of the same field?
>

Date comparison problem

Hi

I have a table that contains information that has start dates and end
dates. They are stored in short date format.

I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used

dim todDate
todDate = now

then I did

shTodDate = FormatDateTime(todDate, 2)

which returned the date to a web page that was in the same format as
what was in the access table.

SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"

This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following

SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;

This still only returned events that started on the current day.

Does anyone know how I can use <= or >= when comparing dates?

Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?

Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:

DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP

SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;

... or, if the end date may be NULL, then:

SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);

Hope that helps,
Rich

"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+

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 and time diffs

I'm a newbie to CR so be gentle :) I'm using ver. 10 I'm going againt 2 tables in a SQL database.

The first table has contains problem reports and the second has the remediations they are linked by OutageID. My problem is that on the Problem table there can be several reords that have the same OutageID
but with different status' such as Unavailable, Update, and Available.
There would be only one Unavailable and one Available record but could be none to any number of Update records.
I want to calculate the number of hours and minute of lost time from the Unavailable timestamp to the Available timestamp. How do I code this formula?

ThanksTake a look at DateDiff function in help.