date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
Paul G
Software engineer.
found solution!
Paul G
Software engineer.
"Paul" wrote:

> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:

> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

Date Parameter Labels?

I have the usual report with date range parameters (from, to) that I have set
up as datetime format so that users can get the fancy calendar applet. thats
all fine and good. My problem is that I have creative users that like to hand
enter all kinds of date values other than the expected <mm/dd/yyyy> values
and are surprised that they dont return results.
I know I can create code in my sproc to handle all of the various
permutations on the backend, and that is probably the more "user friendly?
way to go, but what I want to know is if there is a way to put a label in the
parameter boxes that will show the expected format. I tried messing with
default parameters, but that didnt work when using a string as a default for
a datetime parameter. All I want is for the lable to show the expected
format, not act as a default value in any way.
If anyone has any ideas, please let me know. Perhaps this will be
functionality in the next version of RS?
Thanks!You can already put whatever label you want for the parameter. In layout
mode, Report Menu->Report Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>I have the usual report with date range parameters (from, to) that I have
>set
> up as datetime format so that users can get the fancy calendar applet.
> thats
> all fine and good. My problem is that I have creative users that like to
> hand
> enter all kinds of date values other than the expected <mm/dd/yyyy> values
> and are surprised that they dont return results.
> I know I can create code in my sproc to handle all of the various
> permutations on the backend, and that is probably the more "user friendly?
> way to go, but what I want to know is if there is a way to put a label in
> the
> parameter boxes that will show the expected format. I tried messing with
> default parameters, but that didnt work when using a string as a default
> for
> a datetime parameter. All I want is for the lable to show the expected
> format, not act as a default value in any way.
> If anyone has any ideas, please let me know. Perhaps this will be
> functionality in the next version of RS?
> Thanks!|||Different kind of label.
I know I can put the format in the NAME of the parameter, but this looks
kind of ugly and is only a last resort. I want to put the format INSIDE the
text box, much like a default value, but have it disappears when typed over
or the calendar applet is used. I dont want it seen by the reporting server
as an actual default value.
"Bruce L-C [MVP]" wrote:
> You can already put whatever label you want for the parameter. In layout
> mode, Report Menu->Report Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
> >I have the usual report with date range parameters (from, to) that I have
> >set
> > up as datetime format so that users can get the fancy calendar applet.
> > thats
> > all fine and good. My problem is that I have creative users that like to
> > hand
> > enter all kinds of date values other than the expected <mm/dd/yyyy> values
> > and are surprised that they dont return results.
> > I know I can create code in my sproc to handle all of the various
> > permutations on the backend, and that is probably the more "user friendly?
> > way to go, but what I want to know is if there is a way to put a label in
> > the
> > parameter boxes that will show the expected format. I tried messing with
> > default parameters, but that didnt work when using a string as a default
> > for
> > a datetime parameter. All I want is for the lable to show the expected
> > format, not act as a default value in any way.
> >
> > If anyone has any ideas, please let me know. Perhaps this will be
> > functionality in the next version of RS?
> > Thanks!
>
>|||Ahhh, I see. Nope, can't do it. However, if they put in a non-date format
and you have the parameter value as date then RS will tell the user. Not the
best but not the worst error message. For instance: The value provided for
the report parameter 'FromDate' is not valid for its type
I have found most people use a date picker if it is there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0F768D55-C02B-4A3E-BBCA-2EA618B0490F@.microsoft.com...
> Different kind of label.
> I know I can put the format in the NAME of the parameter, but this looks
> kind of ugly and is only a last resort. I want to put the format INSIDE
> the
> text box, much like a default value, but have it disappears when typed
> over
> or the calendar applet is used. I dont want it seen by the reporting
> server
> as an actual default value.
> "Bruce L-C [MVP]" wrote:
>> You can already put whatever label you want for the parameter. In layout
>> mode, Report Menu->Report Parameters
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>> >I have the usual report with date range parameters (from, to) that I
>> >have
>> >set
>> > up as datetime format so that users can get the fancy calendar applet.
>> > thats
>> > all fine and good. My problem is that I have creative users that like
>> > to
>> > hand
>> > enter all kinds of date values other than the expected <mm/dd/yyyy>
>> > values
>> > and are surprised that they dont return results.
>> > I know I can create code in my sproc to handle all of the various
>> > permutations on the backend, and that is probably the more "user
>> > friendly?
>> > way to go, but what I want to know is if there is a way to put a label
>> > in
>> > the
>> > parameter boxes that will show the expected format. I tried messing
>> > with
>> > default parameters, but that didnt work when using a string as a
>> > default
>> > for
>> > a datetime parameter. All I want is for the lable to show the expected
>> > format, not act as a default value in any way.
>> >
>> > If anyone has any ideas, please let me know. Perhaps this will be
>> > functionality in the next version of RS?
>> > Thanks!
>>

date in Calander is in incorrect format

Hi this is driving me completely mad.
UK based so we want the calendar parameter to display the date format as
dd/MM/yyyy rather than MM/dd/YYYY.
I'm using SQL RS 2005 on a Windows 2003 sp1 box.
I've used regedit to look at all the user account international settings,
all of which are set to UK.
If i log on to the box and run the report the server the date format in the
calendar parameter display correctly.
Same account on annother machine displays it in the US format.
I've also checked my regional settings on the other machine and it shows UK.
Where else is it going to pick up the US format from?What is the report's default format value for the date that is displayed on
the report?
=-Chris
"Namshub" <Ricchard.Pullen@.Southend.nhs.uk.RemoveMyspam> wrote in message
news:%23J6J5MUDHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Hi this is driving me completely mad.
> UK based so we want the calendar parameter to display the date format as
> dd/MM/yyyy rather than MM/dd/YYYY.
> I'm using SQL RS 2005 on a Windows 2003 sp1 box.
> I've used regedit to look at all the user account international settings,
> all of which are set to UK.
> If i log on to the box and run the report the server the date format in
> the calendar parameter display correctly.
> Same account on annother machine displays it in the US format.
> I've also checked my regional settings on the other machine and it shows
> UK.
>
> Where else is it going to pick up the US format from?
>|||The report format is fine as I set the documents local to UK, and all dates
display correctly without specific formating. It is just the Parameterised
Calendar combo (which I use for a start and end date filtering)
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:e%23xrIkaDHHA.4024@.TK2MSFTNGP04.phx.gbl...
> What is the report's default format value for the date that is displayed
> on the report?
> =-Chris
> "Namshub" <Ricchard.Pullen@.Southend.nhs.uk.RemoveMyspam> wrote in message
> news:%23J6J5MUDHHA.4832@.TK2MSFTNGP06.phx.gbl...
>> Hi this is driving me completely mad.
>> UK based so we want the calendar parameter to display the date format as
>> dd/MM/yyyy rather than MM/dd/YYYY.
>> I'm using SQL RS 2005 on a Windows 2003 sp1 box.
>> I've used regedit to look at all the user account international
>> settings, all of which are set to UK.
>> If i log on to the box and run the report the server the date format in
>> the calendar parameter display correctly.
>> Same account on annother machine displays it in the US format.
>> I've also checked my regional settings on the other machine and it shows
>> UK.
>>
>> Where else is it going to pick up the US format from?
>

date in a database

dear experst:

i have a web site and a calendar where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

this problem is realy annoying me and i can not continue my work without solving it. any help will be more than appreciated.
thanksvarname1 = startday
varname2 = endday|||thanks for your reply,
what i want is how to know the start date and the end date when i user select a week or a month and how to calculate the value so when give it to the stored procedure it will know that is a week?
thanks|||to get the Frist/Last day-of-the-week, Frist/Last day-of-the-month u can use the following queries. this assumes that Sunday is the first day of the week and Saturday is that last one. also the dates r calculated with respect to date variable.

declare @.DtVar smalldatetime
set @.DtVar = '2007-03-22' -- an example date
select @.DtVar+1-datepart(dw,@.DtVar) as StartingDayOfTheWeek
select @.DtVar+7-datepart(dw,@.DtVar) as LastDayOfTheWeek
select @.DtVar+1 - datepart(dd,@.DtVar) as FristDateOfTheMonth
select dateadd(mm,1,@.DtVar) - datepart(dd,@.DtVar) as LastDateOfTheMonth|||thanks a lot for your answer, but what if i want monday be the first day of the week and sunday the last day of the week? what should i changed.
thanks|||dude, please play around with the number. when you got a solution for a problem, you shall learn to understand it and explore further. By asking, "what if i want Monday", you're asking the same question...|||Set @.@.datefirst 1|||thanks a lot for your answer, but what if i want monday be the first day of the week and sunday the last day of the week? what should i changed.
thanks

You can assign the days into an int array and pass to your stored procedure.

date function in C#

hi experts,

i'm working in a web page for some statistics and i have a calendar where the customer can choose a day a week or a month and according to the date he select i need to query the database according to the date selected.

what i want to know is how can i store the date for a day in a variable so i can call it from a stored procedure, the day actually is easy what i want is how can i store the whole week in a variable so i can give it to the stored procedure and query the data in the database according to the whole week may be with startday and endday

also the same problem for the whole month, any idea how can i implement that in C#?!!!

thanks

Are you asking how would you query by a date range? such as a week?

-c

|||

thanks for your reply, what i wnat is how can i store the value of the whole week and month in a variable. i know how to query the database. the situation is as follow:

i have a web site where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

i hope i made my self clear.

any help will be appreciated.

thanks

|||

mrjoka

Check out the article below and look for the sample function called RunStoredProcParams here you will see how to return a datareader from a stored procedure that accepts a parameter

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

HTH

|||

thanks for the link, actually what i want is how to store the date value of the selected week or month from a callendar in a variable so i can assing them to a stored procedure. i know how to assign parameters to SP but how can i store the startdate and enddate selected from the user from the callendar? for example if the user select a week or a month for a day i can use :date1 = Calendar1.SelectedDate.ToShortDateString(); but for a week or a month!!!!

this is what makes me crazy these days

thanks in advance

|||

Since the SelectedDate is a DateTime variable you can do this for month;

int SelectedMonth = Calendar1.SelectedDate.Month;

The week can be done as follows

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture;int SelectedWeek = cal.GetWeekOfYear;

|||

thanks for the reply it realy helps but when i tried the week i had these fouts:

Error 1 Cannot implicitly convert type 'System.Globalization.CultureInfo' to 'System.Globalization.Calendar'

Error 2 Cannot convert method group 'GetWeekOfYear' to non-delegate type 'int'. Did you intend to invoke the method?

any idea why?

thanks again

|||

yes, I forgot the .Calendar on the CultureInfo - so its = System.Globalization.CultureInfo.CurrentCulture.Calendar;

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture.Calendar;

That will fix both issues.

-c

|||

thanks for the quick reply,

i did wat you told me and i get rid of the first fout but the second one is still there

any idea

thanks

|||

my bad - you need to add () to the call;

int SelectedWeek = cal.GetWeekOfYear();

|||

sorry to bother you again,

here is the fout this time:

Error 1 No overload for method 'GetWeekOfYear' takes '0' arguments

is it because i'm using it inside a case statement?!!!

thanks

|||

here you go;

int SelectedWeek = System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(Calendar1.SelectedDate, System.Globalization.CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);

-c|||

do i need to declareDayOfWeek because it gave me this error:

Error 1 The name 'DayOfWeek' does not exist in the current context

thanks

|||

thanks man it works like a charm

|||

DayOfWeek is under System and is an enumeration so it should be fine. Maybe try System.DayOfWeek.

-c

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

Date contol issue.

Hello,

In the date control parameter while user open the calendar he can change the year at the top of the control by click in the year and he get up and down arrow.

When user go to old year ther is a problem with the calendar.

Any idea ?

Idanle,

Can you specify exactly what the problem is that the user encounters when selecting older dates?

Ham

|||

The user not select a date.

While you open the date control, at the top click on the year (e.g 2006) when you click on the year you can down or up to get 2005 or 2007.

If you start to down to 2005,2004... you see that the calander miss dates

|||

Humm,

When I click the on the Year control in the datatime picker, I can move through the years and then if I need a different month I click on the month and a picklist appears that allows me to pick another month of that year.

Does this problem appear on all of your reports that uses the datetime control or just on single report?

Ham

|||I can't pick but if I go to an olde year somthing happen to the calendar