Date Picker control returning date is US format

SQL 2005 SP2 + hotfix build 3152
I'm a newbie to reporting services and I am just learning and testing at the
moment. I am based in the UK.
I have set up a report with two parameters that are both dates.
When running the report in the IDE or http://ServerName/Reports dates picked
from the calendar control return as DD/MM/YYYY
When I run the report through http://Servername/ReportServer the date is
returned to the web page as MM/DD/YYYY.
I have found that if I choose a date like 02 Feb so I can run the report and
then use the date picker again without exiting, it then returns DD/MM/YYYY.
My client PC is set to UK English and I have checked my IE settings (I have
tried firefox too). The server is all set to UK english. I have checked the
settings on IIS as well.
I have tried forcing the culture to "en-GB" in web.config.
It's interesting to note that the report services log files (eg
ReportServerWebApp__03_16_2007_14_29_30.log) contains en-US.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
9.00.3152.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles\ReportServerWebApp__03_16_2007_14_29_30.log</Path>
<SystemName>myservername</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
<OSVersion>5.2.3790.131072</OSVersion>
</Header>On 16 mar, 16:05, "Paul Cahill" <a...@.anon.com> wrote:
> SQL2005 SP2 + hotfix build 3152
> I'm a newbie to reporting services and I am just learning and testing at the
> moment. I am based in the UK.
> I have set up a report with two parameters that are both dates.
> When running the report in the IDE orhttp://ServerName/Reportsdates picked
> from the calendar control return as DD/MM/YYYY
> When I run the report throughhttp://Servername/ReportServerthe date is
> returned to the web page as MM/DD/YYYY.
> I have found that if I choose a date like 02 Feb so I can run the report and
> then use thedate pickeragain without exiting, it then returns DD/MM/YYYY.
> My client PC is set to UK English and I have checked my IE settings (I have
> tried firefox too). The server is all set to UK english. I have checked the
> settings on IIS as well.
> I have tried forcing the culture to "en-GB" in web.config.
> It's interesting to note that the report services log files (eg
> ReportServerWebApp__03_16_2007_14_29_30.log) contains en-US.
> <Header>
> <Product>MicrosoftSQLServer Reporting Services Version
> 9.00.3152.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>GMT Standard Time</TimeZone>
> <Path>C:\Program Files\MicrosoftSQLServer\MSSQL.3\Reporting
> Services\LogFiles\ReportServerWebApp__03_16_2007_14_29_30.log</Path>
> <SystemName>myservername</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
> <OSVersion>5.2.3790.131072</OSVersion>
> </Header>
Exactly the same problem here, it is caused by SP2 and his new date-
picker control. Anyone knows if MS is working to correct this?. Thanks.

Date out of range?!

Greetings!

I have a data source that gets generated based on a variable with the following SQL :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @.[User::Last_Run_Date] + "'"

But when I run my package I get an error message saying:

The conversion of CHAR to DATETIME resulted in a DATETIME value out of range

The Last_Run_Date variable is set to '2006-06-25 14:35:05.450'

When I run the code in a QA session it works, but in the package it complains! What am I doing wrong?

Thanks for your help in advance.

Does the error get thrown by SSIS eval;uating the expression or by SQL Server when it gets executed?

If the latter...use SQL Profiler to check what is getting issued.

-Jamie

|||

Does the error get thrown by SSIS eval;uating the expression or by SQL Server when it gets executed?

The expression evaluates correctly. The error occurs when I run the package.

|||

Yeah I know that. I mean when it actually fails, is it the expression evaluation that fails or the issuing of the SQL? Can you post the full error message?

Just cos the expression evaluates successfully at design-time it does not necassarily mean it will do at execution-time.

-Jamie

|||

Hi Jamie,

The error message I get is:

Error: 0xC0202009 at Load Deleted Data, Result on DevTill [1]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "The conversion of CHAR to DATETIME resulted in a DATETIME value out of range.".

Error: 0xC004706B at Load Deleted Data, DTS.Pipeline: "component "Result on DevTill" (1)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Load Deleted Data, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Load Deleted Data: There were errors during task validation.

Warning: 0x80019002 at Route A: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Result Transformation: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Result Transformation Faster Version.dtsx" finished: Failure.

|||

Hmmm...at what point does it fail? Is it when the Execute SQL Task executes or is it when the package is getting validated (which is the very first thing that happens - before any tasks have executed.

-Jamie

|||

Hmmm...at what point does it fail? Is it when the Execute SQL Task executes or is it when the package is getting validated (which is the very first thing that happens - before any tasks have executed.

While the package is being validated.

By the way, the variable User::Last_Run_Date is a datetime variable. In my SQL string (the one used to generate the data source) am casting my datatime variable as a character. But I can't see why that would cause a problem.

|||

Just a guess, but what is in your variable when you deploy the package?

I dare say that this will succeed if you set DelayValidation=TRUE on the Execute SQL Task.

-Jamie

|||

Hi Jamie,

The error does not occur while the SQL Task is being validated. The Task correctly picks the right date and assigns it to my variable. The variable then gets used in a Data source task. It's at the point of validating/running the datasource part of my Data Flow component that I get the error message.

|||

Then there is something wrong in the SQL. Use Profiler to see what is getting issued against SQL Server.

Or, use this technique to get the SQL statement in a watch window: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie

|||

Is it possible that your client - SSIS - is using a different locale from the server? I have seen run into this occasionally as my home PC uses a UK locale.

If so, then you may find that running the query in QA succeeds, but the client fails. But you'll need to work on it quickly - in a few days (1/7, or 7/1 depending on your locale) it may magically look like it is working again.

Donald

|||

Hi Donald,

Yes that is a possibility but how to change the date format in SSIS?!

|||

Donald is referring to the locale if your client workstation on which SSIS is installed.

However, this is a murky area. You should read this post: http://blogs.conchango.com/jamiethomson/archive/2006/04/26/3870.aspx and be aware of it.

Did you use Profiler to check what was getting issued? Profiler is always the first place to start in these situations - I can't stress just how useful a tool it is.

-Jamie

|||

Hi Jamie,

I am not quite sure how to use profiler for this problem.

I have a data source coming from SQL Server 6.5. The data source is generated from a variable with the following expression :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @.[User::Last_Run_Date] + "'"

The @.[User::Last_Run_Date] is a datetime variable

When I run it, I get the error about not being able to convert CHAR to DATETIME. The value of my Last_Run_Date is 2006-06-25 14:35:05.450

How am I suppose to use profiler for this?!

|||

You've already told me further up this thread that the error is getting thrown by SQL Server when you issue that query from SSIS. You have 2 ways of finding out what query SSIS is issuing after it has evaluated that expression:

1) Use profiler. However this is the first time you have mentioned that it is SQL Server 6.5. I don't actually know if Profiler works for SQL 6.5 though I assume it does.

2) Put the query into a variable and use a watch window

Both of which I've already mentioned in this thread and I gave you a link for option 2.

-Jamie

Date inputs (UK format) in SQL svr 2000 with OLE DB

Hi all,

I'm building an ASP app on a Windows 2000/IIS machine which interfaces
with our SQL Server 2000 database via OLE DB.

Since we're based in the UK I want the users to be able to type in
dates in UK date format to input into the database. In Enterprise
Manager on the SQL Server I can manually enter a record into a table
and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
it happily.

However, if I enter the exact same record on the form on the web page,
again using the UK date format, I get this back from the IIS box:

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of a char data type to a datetime data type resulted
in an out- of-range datetime value.
/ictest/eventadm.asp, line 78

I'm assuming that despite SQL Server accepting the date in UK format,
OLE DB will not. Can the OLE provider be configured to allow such
date formats, either (I imagine) in the registry or by altering the
connection string?

TIA,
NiallMake sure the SQL Server user is a UK English User otherwise SQL Server will
do this

(presume user language is set to English (American))

This guy is American so he is giving me a date of 25/12/2004. Americans use
MMDDYYYY so what he means is

the 12th day of the 25th month

this as we can guess is way out of range.

Here is another example

SET LANGUAGE us_english
GO

select cast('25/12/2004' as datetime)

Changed language setting to us_english.
Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

SET LANGUAGE British
GO

select cast('25/12/2004' as datetime)

2004-12-25 00:00:00.000

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"Niall Porter" <niallporter@.yahoo.co.uk> wrote in message
news:2db8d05e.0407280624.773f87ff@.posting.google.c om...
> Hi all,
> I'm building an ASP app on a Windows 2000/IIS machine which interfaces
> with our SQL Server 2000 database via OLE DB.
> Since we're based in the UK I want the users to be able to type in
> dates in UK date format to input into the database. In Enterprise
> Manager on the SQL Server I can manually enter a record into a table
> and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
> it happily.
> However, if I enter the exact same record on the form on the web page,
> again using the UK date format, I get this back from the IIS box:
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
> Technical Information (for support personnel)
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted
> in an out- of-range datetime value.
> /ictest/eventadm.asp, line 78
> I'm assuming that despite SQL Server accepting the date in UK format,
> OLE DB will not. Can the OLE provider be configured to allow such
> date formats, either (I imagine) in the registry or by altering the
> connection string?
> TIA,
> Niall|||"Niall Porter" <niallporter@.yahoo.co.uk> wrote in message
news:2db8d05e.0407280624.773f87ff@.posting.google.c om...
> Hi all,
> I'm building an ASP app on a Windows 2000/IIS machine which interfaces
> with our SQL Server 2000 database via OLE DB.
> Since we're based in the UK I want the users to be able to type in
> dates in UK date format to input into the database. In Enterprise
> Manager on the SQL Server I can manually enter a record into a table
> and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
> it happily.
> However, if I enter the exact same record on the form on the web page,
> again using the UK date format, I get this back from the IIS box:
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
> Technical Information (for support personnel)
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted
> in an out- of-range datetime value.
> /ictest/eventadm.asp, line 78
> I'm assuming that despite SQL Server accepting the date in UK format,
> OLE DB will not. Can the OLE provider be configured to allow such
> date formats, either (I imagine) in the registry or by altering the
> connection string?
> TIA,
> Niall

The best way to handle this would probably be to use a date format which
works regardless of any client and server settings - there are two:

'20040728' -- no time needed
'2004-07-28T20:20:00' -- ISO8601, includes time

Although I don't know much about ADO, I believe that if ADO is aware that
the target is a datetime parameter or column, it will automatically handle
the conversion for you. That might be one option for you - pass the value to
a stored procedure, and when you use the Command.CreateParameter method, ADO
will be aware that the parameter is datetime. But I admit that I haven't
tried it myself, so I may be wrong about this.

Simon|||Niall Porter (niallporter@.yahoo.co.uk) writes:
> Since we're based in the UK I want the users to be able to type in
> dates in UK date format to input into the database. In Enterprise
> Manager on the SQL Server I can manually enter a record into a table
> and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
> it happily.
> However, if I enter the exact same record on the form on the web page,
> again using the UK date format, I get this back from the IIS box:
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
> Technical Information (for support personnel)
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted
> in an out- of-range datetime value.
> /ictest/eventadm.asp, line 78
> I'm assuming that despite SQL Server accepting the date in UK format,
> OLE DB will not. Can the OLE provider be configured to allow such
> date formats, either (I imagine) in the registry or by altering the
> connection string?

So how does your code look like? Since you get the error from OLE DB,
I assume that you are passing the value as a parameter to a prepared
statement, and that you declare the parameter to be of type
adDBTimeStamp. In such case you must use ISO format, 2004-12-25.

If you want to support regional settings, you should use some ASP
routine to convert the date value. CDate() might be your guy, but
we're straying beyond the realms of this newsgroup. You may want to
try an ASP forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all the help from everyone so far, I'm not quite out of the
woods yet tho!

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<4107c9f7$0$7125$db0fefd9@.news.zen.co.uk>...
> Make sure the SQL Server user is a UK English User otherwise SQL Server will
> do this
> (presume user language is set to English (American))

The database user (a pure SQL Server user) was set to English. I
found another language called "British English", I've changed that
user to British English and now I can input dates in a UK format
(DD/MM/YYYY) which is great, thanks for that. I appreciate the 'best'
way to do it is to use an ISO format but my users wouldn't cope with
such technicalities as this...

However, dates *returned* are still in US format (MM/DD/YYYY)! Is
there some other esoteric setting I have to tweak somewhere?

> This guy is American so he is giving me a date of 25/12/2004. Americans use
> MMDDYYYY so what he means is
> the 12th day of the 25th month
> this as we can guess is way out of range.
>
> Here is another example
> SET LANGUAGE us_english
> GO

Won't that set it for the all databases on the whole server? We have
a number of applications that use the SQL server for their databases,
some of which may well have been developed in the states. If I change
the setting for the whole server, any other applications that don't
use the ISO or other non-regional date formatting will break surely?

> select cast('25/12/2004' as datetime)

Tried this also, didn't make any difference.

> Changed language setting to us_english.
> Server: Msg 242, Level 16, State 3, Line 2
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> SET LANGUAGE British
> GO
> select cast('25/12/2004' as datetime)
> 2004-12-25 00:00:00.000
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>|||Niall Porter (niallporter@.yahoo.co.uk) writes:
> However, dates *returned* are still in US format (MM/DD/YYYY)! Is
> there some other esoteric setting I have to tweak somewhere?

I'm still not sure why you post this in an SQL Server forum, I would
expect this to be an ASP problem. Then again, since you have not posted
any code, I have no idea of what you are doing.

>> SET LANGUAGE us_english
>> GO
> Won't that set it for the all databases on the whole server? We have
> a number of applications that use the SQL server for their databases,
> some of which may well have been developed in the states. If I change
> the setting for the whole server, any other applications that don't
> use the ISO or other non-regional date formatting will break surely?

SET LANGAUGE changes the language for the current session. However, this
option controls how date literals are interpreted on *SQL Server* and
you got the conversion error on client level.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland.

If the client connects as a US user on a UK server entering a data of
'25/12/2004' then wouldn't the error returned be this error. My expereince
of mixing regional settings says it would.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9536ECDAE92D1Yazorman@.127.0.0.1...
> Niall Porter (niallporter@.yahoo.co.uk) writes:
> > However, dates *returned* are still in US format (MM/DD/YYYY)! Is
> > there some other esoteric setting I have to tweak somewhere?
> I'm still not sure why you post this in an SQL Server forum, I would
> expect this to be an ASP problem. Then again, since you have not posted
> any code, I have no idea of what you are doing.
> >> SET LANGUAGE us_english
> >> GO
> > Won't that set it for the all databases on the whole server? We have
> > a number of applications that use the SQL server for their databases,
> > some of which may well have been developed in the states. If I change
> > the setting for the whole server, any other applications that don't
> > use the ISO or other non-regional date formatting will break surely?
> SET LANGAUGE changes the language for the current session. However, this
> option controls how date literals are interpreted on *SQL Server* and
> you got the conversion error on client level.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Allan Mitchell (allan@.no-spam.sqldts.com) writes:
> If the client connects as a US user on a UK server entering a data of
> '25/12/2004' then wouldn't the error returned be this error. My
> expereince of mixing regional settings says it would.

Depends. The error Neill got was:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

The only way to get that error from Query Analyzer is to connect a linked
server with a date-format conflict along the line.

The problem here is that things can to wrong on several levels. In Neill's
case it got wrong already on client level. I suspect because he was
using adDBTimeStamp to which he must feed an ISO format.

It is also confusing since the regional settings on the client neither the
server on what happens in SQL Server which uses its own language
definitions. You can set language when you connect, but then you have to
pass that in the connection string - and it has to be a language as SQL
Server knows it.

The main problem in Neill's case is that he has not posted any code, so
we are left to guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Date information

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

date 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 function to extract start date

Hello,
I was wondering if there is a date function to determine a starting
date based on a @.EndDate
parameter. In other word, if my user chose an end date of 3/31/2006, I
want to get a start date of 4/1/2005.
I appreciate any suggestion.
Edgar J.You can use another query to provide a default for the second parameter
using the 1st as input.
If its sqlserver you can use (I may have syntax slightly wrong as Im
not looking this up now).
SELECT [StartDate] = dateadd(day, dateadd(year, @.EndDate, -1), 1)
In other words return just 1 row , 1 column with a date.
Use sqlserver dateadd function to subtract 1 year and again to add 1
day.
Edgar wrote:
> Hello,
> I was wondering if there is a date function to determine a starting
> date based on a @.EndDate
> parameter. In other word, if my user chose an end date of 3/31/2006, I
> want to get a start date of 4/1/2005.
> I appreciate any suggestion.
> Edgar J.

Date Formats In Analysis Services 2005

Hello,

I've been having an issue trying to display anything other then a Long Date in Analysis Services. I've used the Server based Time Dimension and I've also tried creating a dimension for a User Created Date Table.

It seems no matter what I do the date attribute always displays as a long date.

Any Ideas?

If you need more flexibility (and most applications), what I typically recommend is to create a standalone time dimension table. For example, if you look at Project REAL, we use surrogate keys for all of our dimensions except time -- and for it we use YYYYMMDD as an integer. And use it as the surrogate key between the fact tables and a standalone time dimension.

See http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_

Date filter in MDX

Hi guys,

I have a cube where I want to make filter based on date range. I've created a cube with appropriate measures and I have Dimension with date field ( I want to filter on that field).

I've found two solutions:

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= "2/6/2007" --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= "2/7/2007" --'2/7/2007'

but this stopped working after I applied SP2 - i just get null values for all measures

and this one

[Reservation Search Log].[Simple Insert Date].&[February 1, 2007] :
[Reservation Search Log].[Simple Insert Date].&[February 10, 2007]

but in I would like to pass these dates as parameters. and if both dates are outside the range I get no results. In real case I should get all rows like this
data in SSAS February 1, 2007 to February 10, 2007
I pass January 1, 2007 to March 10, 2007
and no result are returned

If there is no appropriate records for both date parameters no rows are returned...

Any ideas are highly appreciated.Filter is not really an efficient way to do this, if possible I usually try to populate drop down lists of members in reports, but sometimes this is not always practical. Have you tried converting your comparison values to dates as well?

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= CDate("2/6/2007") --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= CDate("2/7/2007") --'2/7/2007'|||Hi Darren,

Thank very much for helping me with this....

I don't get what do you mean with converting comparison values to dates...

the example I posted is from real MDX query in which I got that doesn't work on SP2 and both sides are converted using CDate before comparison is made

Any other ideas? What is the best way to achieve such goal?|||

The sample you posted only had one side converted to a date, the other was a string value. The example you posted has

>= "2/6/2007"

as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like:

>= CDATE("2/6/2007")

I tested this in SP2 and it works for me.

|||

this is interesting...

I had this filter clause in where clause and I had the (null) problem...

I moved filter clause in select in subquery and everything is fine now...

Sorry for missing the changes in your post. I've made so many tests and I thought I posted same as you

Thanks a lot, Darren

date comparison and filtering

Im creating a stored proc where i need to filter an Orders table
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W, This Month.
This W:
Do I have to calculate the start of w and end of w and then use
a between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w, you have to decide what day of the w you
want to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w)
And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:

> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W, This Month.
> This W:
> Do I have to calculate the start of w and end of w and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w, you have to decide what day of the w yo
u
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w)
> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w
where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
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
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W, This Month.
> This W:
> Do I have to calculate the start of w and end of w and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>

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 calculation

Im relatively new with sql and im having a problem
creating a criteria based on todays date less 150 days.
I tried to use the following...
< GetDate () -150 however it will not let me use this.
I know that there must be away to do this, but I need
help.. Thank you in advance
On Tue, 3 Aug 2004 10:37:13 -0700, Bob wrote:

>Im relatively new with sql and im having a problem
>creating a criteria based on todays date less 150 days.
>I tried to use the following...
> < GetDate () -150 however it will not let me use this.
>I know that there must be away to do this, but I need
>help.. Thank you in advance
Hi Bob,
Try it with DATEADD(day, -150, getdate()).
If you remove the space between getdate and (), your code would work as
well, but I recommend using DATEADD anyway - just subtracting 150 relies
on the internal storage of datetime values; this is not guaranteed to
remain unchanged between SQL Server versions.
Also remember that getdate() gives current date *AND* time. Not a problem
for "smaller than" comparissons, but if you ever have to match on "equal"
date, you'll have to use some extra tricks to get rid of the time portion.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Date based filter in merge replication

Hopefully, someone can provide some advice regarding filtering data by dates.
We have data that we want to expire from the sqlce database because it has reached a certain age. Filters have been applied to only replicate data wihtin the date boundaries we require.
What we have found through testing is that when we increment server and device dates to a date where data should expire, it is not removed from the database on the device. During this testing we made sure our snapshot was updated before applying more chan
ges through our application, allowing us to see data travelling to and from the device. But, alas, the outdated data survived.
So, I suppose it makes sense that if a record is not updated, it will not be affected by replication - or is there some way around this?
Other than archiving data on the server side which has its own set of implications (ms_MergeTombstone growing excessively, effects on historical reports), is there some other technique we can use to limit the volume of data on the device?
Thanks
Steve
Steve,
As you mentioned already, records that are not updated are not affected by
replication. Creating a new snapshot won't help on this side either. So the
technique I term as 'Induced Updates' could be applied in your case. This
simply means making an update in the table for all rows you want replication
to re-assess without changing the data. This sample script will demo:
update YourTable
set column_name = column_name
where date_column = some_date_criteria
You can also create a job to do this periodically, where some_date_criteria
can be dependent on the getdate() function.
Hope the above helps.
Raj Moloye.
|||Steve,
As you mentioned already, records that are not updated are not affected by
replication. Creating a new snapshot won't help on this side either. So the
technique I term as 'Induced Updates' could be applied in your case. This
simply means making an update in the table for all rows you want replication
to re-assess without changing the data. This sample script will demo:
update YourTable
set column_name = column_name
where date_column = some_date_criteria
You can also create a job to do this periodically, where some_date_criteria
can be dependent on the getdate() function.
Hope the above helps.
Raj Moloye.
|||Thanks Raj,
I have to say I was starting to think along those lines as I wrote the original post.
When I update these rows that are out of date range, does replication cause these updated rows to be removed from the device?
I think some testing might be in order here just to confirm what you are saying.
Thanks again.
SteveM
"Raj Moloye" wrote:

> Steve,
> As you mentioned already, records that are not updated are not affected by
> replication. Creating a new snapshot won't help on this side either. So the
> technique I term as 'Induced Updates' could be applied in your case. This
> simply means making an update in the table for all rows you want replication
> to re-assess without changing the data. This sample script will demo:
> update YourTable
> set column_name = column_name
> where date_column = some_date_criteria
> You can also create a job to do this periodically, where some_date_criteria
> can be dependent on the getdate() function.
> Hope the above helps.
> Raj Moloye.
>
>
|||Thanks Raj,
I have to say I was starting to think along those lines as I wrote the original post.
When I update these rows that are out of date range, does replication cause these updated rows to be removed from the device?
I think some testing might be in order here just to confirm what you are saying.
Thanks again.
SteveM
"Raj Moloye" wrote:

> Steve,
> As you mentioned already, records that are not updated are not affected by
> replication. Creating a new snapshot won't help on this side either. So the
> technique I term as 'Induced Updates' could be applied in your case. This
> simply means making an update in the table for all rows you want replication
> to re-assess without changing the data. This sample script will demo:
> update YourTable
> set column_name = column_name
> where date_column = some_date_criteria
> You can also create a job to do this periodically, where some_date_criteria
> can be dependent on the getdate() function.
> Hope the above helps.
> Raj Moloye.
>
>

Date and time

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

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