Date/Time Stamp: error

Showing posts with label error. Show all posts

Thursday, March 29, 2012

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ONcolumns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

oncolumnsfrom [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.sql

Sunday, March 25, 2012

Date problem

Hi,

I enter a date in my parameter in the browser like '30/01/2001'. When I press on 'view report' then I get an error. 'An error occurred during local report processing. The value provided for the report parameter 'datum' is not valid for its type.

I think he sees the '30' as a month, so it gives an error. BUT in my SQL-statement i write this : CONVERT(DateTime, @.datum, 103). What's the problem?

Thx

We do validation on parameter values. If you specify the type of this parameter is datetime, it needs to be valid datetime value based on the current culture. You can try changing the parameter type to be string if you don't want the validation.|||

Does any one know how we can validate the input criteria in reporting server 2005? By saying this I mean to say; let say we have to dates “Start date” and “End date” as criteria to one report. How can I check that the Start date must be lower or equal to the End date?

Regards;
Rakesh

sql

Date Picker formatting

Im sure we have all seen this error before:
"The value for the report parameter XXX is not valid for its type"

I have a report (RS 2005) that has 2 date parameters, "start" and "end".

My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end

But when I go to view the report in VS2005, I get the above error.

I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.

I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)

How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....

Hi,

I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...

Regards, Jeroen

|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...

I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.

Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).

Thanks for the tips

Date Picker formatting

Im sure we have all seen this error before:
"The value for the report parameter XXX is not valid for its type"

I have a report (RS 2005) that has 2 date parameters, "start" and "end".

My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end

But when I go to view the report in VS2005, I get the above error.

I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.

How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....

Hi,

Regards, Jeroen

|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...

Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).

Thanks for the tipssql

Thursday, March 22, 2012

Date Parameter ERROR

My RS has two date parameters setup as a date data type. If the user was to
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.

Wednesday, March 21, 2012

date parameter

One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.

How is it possible to use the calender parameter without the report giving the error pls?
Thanks

i think u should go to report parameter ->properties -> change datatype to datetime|||

Hi,
Yes I am doing just that.
That is the problem because even though the parameter is set to datetime, it does not accept the format such as 21/05/2007 which is what you get if the date is selected from the calender.

Thanks

|||

Hi there,

Have ever you tried to fill a data in other format? This error semms like a invalid format date. Try to fill 05/21/2007 (English format - mm/dd/yyyy).

Let me know if this solved your problem.

Lant

|||you have to pass it in mm/dd/yyyy format

Monday, March 19, 2012

Date Insertion problem

I am using BLL for inserting my data. I have some date filed and I have declare that field in BLL as date only. But still it is giving me error string in wrong format. My database is in SQL Server2005

can u give me a code, how you are declared date in BLL and how you are assining the values to that date variable in BLL, it may be your date variable string is not in proper format.

|||

================================Codes in BLL =============================================================================

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert,True)> _

PublicFunction AddTempEmployee(ByVal DDKSFileNoAsString,ByVal firstNameAsString,ByVal middleNameAsString,ByVal lastNameAsString, _

ByVal dateOfBirthAsDate,ByVal maritualStatusAsInteger,ByVal nationalityAsString,ByVal passportNumberAsString, _

ByVal passportIssuePlaceAsString,ByVal passportIssueDateAsDate,ByVal passportExpiryDateAsDate,ByVal civilIDNumberAsString, _

ByVal civilIDExpiryDateAsDate,ByVal residenceNumberAsString,ByVal homePhoneNumberAsString, _

ByVal alternatePhoneNumberAsString,ByVal kuwaitHomeAddressAsString)AsBoolean

Dim tempEmployeesAsNew DDKS.TempEmployeeDataTableDim tempEmployeeAs DDKS.TempEmployeeRow = tempEmployees.NewTempEmployeeRow

tempEmployee.DDKSFileNo = DDKSFileNo

tempEmployee.FirstName = firstName

tempEmployee.MiddleName = middleName

tempEmployee.LastName = lastName

tempEmployee.DateOfBirth = dateOfBirth

tempEmployee.MaritualStatus = maritualStatus

tempEmployee.Nationality = nationality

tempEmployee.PassportNumber = passportNumber

tempEmployee.PassportIssueDate = passportIssueDate

tempEmployee.PassportExpiryDate = passportExpiryDate

tempEmployee.CivilIDNumber = civilIDNumber

tempEmployee.CivilIDExpiryDate = civilIDExpiryDate

tempEmployee.ResidenceNumber = residenceNumber

tempEmployee.HomePhoneNumber = homePhoneNumber

tempEmployee.AlternatePhoneNumber = alternatePhoneNumber

tempEmployee.KuwaitHomeAddress = kuwaitHomeAddress

tempEmployees.AddTempEmployeeRow(tempEmployee)

Dim rowAffectedAsInteger = Adapter.Update(tempEmployees)

Return 1

EndFunction

========================================= Ends here ===============================================================================

===============================Codes in aspx file==========================================================================================================

<tdstyle="width:150px; height: 27px;"><cc1:MaskedTextBoxID="txDateOfBirth"runat="server"Text='<%# Bind("DateOfBirth", "{0:d}") %>'Mask="99-99-9999"ReadOnly="True"></cc1:MaskedTextBox>

=====================================ends here=====================================================================================================

|||

chk whether you are passing correct values to correct varibale,,

this is not a solution, but sometime we do small mistakes,

chk whether ur passing string to string variables, date to date varibles and integer to integer, and also chk the ur datatypes of the columns for those fields.

DDKsFileNo String
Firstname String
MiddleName String
LastName String
DateofBirth Date
MaritualStatus Integer (I think this is Boolen or bit)
nationality String
PassportNumber String
PassportIssuePlace String
PassportIssueDate Date
PassportExpiryDate Date
CivilIDnumber string
CivilIDExpiryDate Date
residenceNumber String
HomePhoneNumber String
alternatePhoneNumber string
kuwaitHomeAddress string

|||

chk whether you are passing correct values to correct varibale,,

this is not a solution, but sometime we do small mistakes,

chk whether ur passing string to string variables, date to date varibles and integer to integer, and also chk the ur datatypes of the columns for those fields.

DDKsFileNo String
Firstname String
MiddleName String
LastName String
DateofBirth Date
MaritualStatus Integer
nationality String
PassportNumber String
PassportIssuePlace String
PassportIssueDate Date
PassportExpiryDate Date
CivilIDnumber string
CivilIDExpiryDate Date
residenceNumber String
HomePhoneNumber String
alternatePhoneNumber string
kuwaitHomeAddress string

|||

chk whether you are passing correct values to correct varibale,,

this is not a solution, but sometime we do small mistakes,

chk whether ur passing string to string variables, date to date varibles and integer to integer, and also chk the ur datatypes of the columns for those fields.

|||

My problem got caught it something like i was using "-" as seprator in application and my regional setting was using "/" so i change same in my application and start working.

Thanx for help

Thursday, March 8, 2012

Date format US/UK

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

any/all help is appreciated!

chris

Before your insert query add the following statement

SET DATEFORMAT dmy

Insert .....|||

Thanks for the post

I am thinking maybe I have posted in the wrong forum?

what I don't want to do is write more TSQL, rather I would like to know

what is causing the date to be inserted in the US format so that I can change the setting.

thanks

chris

|||Yes you are absolutly correct..

|||

Rather than doing an implicit conversion by inserting the text into a datetime field use an explicit conversion (onto which you can place a style identifier.

Rather than '16/04/2007' Use CONVERT(datetime, '16/04/2007', 103)|||

Thanks for the posts

I have posted this in the database engine forum so please dont post anymore to this thread.

regards

chris

Wednesday, March 7, 2012

date format in raiserror description

I need to return a date to the app if a stored procedure fails (when I raise
an error in it). I have just included the date in the error string, but
wanting to internationalise the app, need to be able to display the date is
the local format. What is the best way to return this information to the
app/user?Return it as an unambiguous date format (e.g. YYYYMMDD). Let the app
localize it (e.g. VBScript / ASP will apply locale to formatdatetime()).
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:R5CdnUa6P8Y7vcnfRVn-uA@.mycybernet.net...
> I need to return a date to the app if a stored procedure fails (when I
raise
> an error in it). I have just included the date in the error string, but
> wanting to internationalise the app, need to be able to display the date
is
> the local format. What is the best way to return this information to the
> app/user?
>|||I do not want to add any output parameters. Is there a way of returning an
error description and the date when raising the error?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O5ORqGtOFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Return it as an unambiguous date format (e.g. YYYYMMDD). Let the app
> localize it (e.g. VBScript / ASP will apply locale to formatdatetime()).
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
> news:R5CdnUa6P8Y7vcnfRVn-uA@.mycybernet.net...
> raise
> is
>|||On Wed, 6 Apr 2005 14:33:49 -0400, David J Rose wrote:

>I do not want to add any output parameters. Is there a way of returning an
>error description and the date when raising the error?
Hi David,
As far as I know: no.
BTW, I don't think Aaron intended to suggest you use an output
parameter. I think that he wanted to suggest that you include the date
in YYYYMMDD format in the error message, let your client intrercept the
error message, find the YYYYMMDD value and replace it with a formatted
date.
(And if Aaron didn't mean to suggest that, then I will <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> (And if Aaron didn't mean to suggest that, then I will <g> )
No, that's exactly what I meant. Currently I presume he is doing:
SET @.msg = 'this is an error on '+CONVERT(VARCHAR, GETDATE())+'...'
And I was suggesting an unambiguous format, such as:
SET @.msg = 'this is an error on '+CONVERT(CHAR(8), GETDATE(), 112)+'...'
Let the client interpret it however it wants to, or just leave it in the
unambiguous format. Whether you're in the UK or the US or anywhere else,
YYYYMMDD or YYYY-MM-DD is a heck of a lot more clear than 06/05/04 ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Date Format Data Driven Subscription

I'm getting the typical error that the format of my date parameters is not correct. Although i'm getting my dates in a select from a datetime field.

Is there really no universal format that can be delivered to RS that he can interprete the date right, whatever language the report has been set to.

Kind Regards .....

You can define your datetime parameters as YYYY-MM-DDTHH:MM:SS. This format is independent of the language of the report.

See http://msdn2.microsoft.com/en-us/library/ms159825.aspx for moer details.

Friday, February 24, 2012

Date Diff

I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1
Syntax error converting date time from character string". can someone please help.

SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime),
CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime))
AS StopMinutes,
BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA,
BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME,
OQTCOD, BAADES, PCS, WGT, Tractor, Driver
FROM dbo.JCI_Delivery_Report

Make sure you are constructing the datetime string correctly. You have to make sure the string you are constructing is a string SQL Server understands as a datetime; otherwise you're going to get a casting error.

German Afanador

|||Can you explain what you are trying to do?|||

SUBSTRING(SCHTIME, 3,4) should be SUBSTRING(SCHTIME, 3,2)

SUBSTRING(ACTTIME, 3,4) should be SUBSTRING(ACTTIME, 3,2)

and while I think it's bad that you've created text fields in your database for dates and times instead of storing them as they should be (in datetime format), this will get you closer to what you want.

Sunday, February 19, 2012

Date Conversion throws truncation error

Hi,

I am trying to process a flat file feed. My date is in format of YYYYMMDD. The database column is of datatype "datetime". I have tried using all date related data types on FLAT FILE Connection Manager. I have also tried using Data Conversion Component. No luck so far!!

Any suggestion?

Thanks in advance,
-AnandTry the Derived Column Transformation.

This post doesn't solve exactly the same problem but its very very similar: http://blogs.conchango.com/jamiethomson/archive/2005/07/26/1867.aspx

-Jamie|||

The key point is, yyyymmdd cannot be casted to date type. (This has been bugged I believe, but feel free to log again it as I know I want it!). Using the Derived Column to crack it into format that can then be casted is the only route. I did test what was suppoted and what wasn't, but seem to have lost the packages for now....

I would also check you cracked format works for US/UK locales, as the flip-flop between the two can really mess you up. I like dd mmm yyyy myself, as no languages muck that up, not even VBScript! This may help as a start if you follow that format-

(MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

|||Hi,

I have partial success with "Data Conversion". What should I do for the column which may contain '' or null?

One more thing: Is dt_dbdate in SSIS compatible with datetime in SQL Server 2005?

-Anand|||Hi,

Will this work in the "Derived Column"?

ISNULL([Maturity Date]) ? : (DT_DBDATE)(SUBSTRING(TRIM([Maturity Date]),5,2) + SUBSTRING(TRIM([Maturity Date]),6,2) + SUBSTRING(TRIM([Maturity Date]),1,4))|||I have resolved the issue using following "Derived Column" statement:

(TRIM([Maturity Date]) == "") ? NULL(DT_WSTR,8) : SUBSTRING(TRIM([Maturity Date]),5,2) + "/" + SUBSTRING(TRIM([Maturity Date]),7,2) + "/" + SUBSTRING(TRIM([Maturity Date]),1,4)

Cheers,
Anand

Big Smile

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
Query works fine after I run the following update:
update tablename set T_DATE = null where isdate(T_DATE) = 0
Is there any way we could make it work as-is, the way it was running in 2000
without any changes.
"Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Date conversion ?

Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"

Please help me !

Alter Procedure "Selectie_Date_Tabel" (@.datainceput datetime, @.datasfirsit datetime,@.Grupa AS nvarchar(20))

As

set nocount on

DECLARE @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

DECLARE @.keyssql AS varchar(1000)

SET @.keyssql = 'SELECT * FROM View2'
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 120) + ' AND ' + CONVERT(DATETIME, @.datasfirsit , 120) +')'

EXEC (@.keyssql)What parameters are you using to execute the stored procedure ?|||@.datainceput DATETIME
@.datasfirsit DATETIME

datainceput = 01.01.2004
datasfirsit = 15.01.2004

I want to make a SQL_String something like this :

SQL_String = 'SELECT * FROM TABLE WHERE ' .... date condition

EXECUTE (SQL_String)

All this inside a stored procedure

Sorry for my english|||I have two option

1. Sp_1

SELECT * FROM TABLE WHERE ............

Is ok, work

2. Sp_2

DECLARE @.keyssql AS varchar(8000)
SET @.keyssql ='SELECT * FROM TABLE WHERE' + 'Condition'

EXECUTE (@.keyssql) -- This line is inside at the same stored procedure.

This stored procedure Sp_2 don`t work|||Enjoy ...

Alter Procedure "Selectie_Date_Tabel" (@.datainceput datetime, @.datasfirsit datetime,@.Grupa AS nvarchar(20))

As

set nocount on

DECLARE @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

DECLARE @.keyssql AS varchar(1000)

SET @.keyssql = 'SELECT * FROM View2'
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 104) + ' AND ' + CONVERT(DATETIME, @.datasfirsit , 104) +')'

EXEC (@.keyssql)|||ALTER PROCEDURE SP_2
As
set nocount on

DECLARE @.keyssql AS varchar(8000)

SET @.keyssql = 'SELECT * FROM View2 WHERE (Data = CONVERT(DATETIME,' +CHAR(39)+ '2004-01-05 00:00:00'+CHAR(39)+', 102))'

EXEC (@.keyssql)
/*--------------*/

This SP work OK.

I want to use a parameter inside '2004-01-05 00:00:00'

Atention EXEC (@.keyssql) is inside a SP|||Enigma, Sorry don't work ........|||didnt get that !!! did the sp not work ?|||This is the original SP
But an solution for the precedent example it would usefull for me.

Alter Procedure sp_CrossTab
@.table AS sysname,
@.onrows AS nvarchar(128),
@.onrowsalias AS sysname = NULL,
@.oncols AS nvarchar(128),
@.sumcol AS sysname = NULL,
@.avgcol AS sysname = NULL,
@.Grupa AS nvarchar(20),
@.datainceput AS datetime,
@.datasfirsit AS datetime

AS
set nocount on

DECLARE @.sql AS varchar(8000), @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

SET @.table = '['+ @.table + ']'
SET @.oncols = '['+ @.oncols + ']'
SET @.onrows = '['+ @.onrows + ']'
SET @.sumcol = '['+ @.sumcol + ']'
SET @.avgcol = '['+ @.avgcol + ']'

SET @.sql ='SELECT' + @.NEWLINE
+ 'DATEPART(ww,' + @.onrows+ ') AS Saptamina,' + ' '
+ 'DATEPART(mm,' + @.onrows+ ') AS Luna,' + ' '
+ 'DATEPART(yyyy,' + @.onrows+ ') AS Anul,' + ' '
+ @.onrows +

CASE
WHEN @.onrowsalias IS NOT NULL THEN ' AS ' + @.onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @.keyssql AS varchar(1000)

/* THIS PART DON'T WORK */

SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 120) +' AND ' +CONVERT(DATETIME, @.datasfirsit , 120) +')'

/* THIS PART WORK OK*/
/*SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table*/

PRINT @.keyssql

EXEC (@.keyssql)

DECLARE @.key AS nvarchar(100)
SELECT @.key = MIN(keyvalue) FROM #keys

WHILE @.key IS NOT NULL
BEGIN
...........................|||Use This >>>>
SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 104) +' AND ' +CONVERT(DATETIME, @.datasfirsit , 104) +')'|||I try but the same error: "Error converting data type varchar to datetime"

Please help me,
Try an easy example:
One table with 3 columns (Day, Field1, Field2)
Create a SP and see if work.|||maybe i am not understanding the dateformat you are passing ...

check up convert in the holy book ("SQL Server Books Online") and insert the no corresponding to your input format|||I have a strong suspicion that the '15.01.2004' date is causing the problem. Whereever possible, you should feed 'yyyy-mm-dd' strings to the sql server. If you can't, then you need to declare your parameters as STRING instead of datetime since 15.01.2004 may not be a date if the date format on the Sql Server is set to 'mm-dd-yyyy' There is no 15th month (not on Earth, anyway ;)).

Friday, February 17, 2012

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Company -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004
is converted. This is based on the Lanaguage of the Login (unless overridden)
You can either override the date setting,
SET DATEFORMAT dm
Or use a neutral date formats (ISO and ISO8601
SET @.inputdate = '20040515' -- IS
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange results
With a DMY connection, it reads it as YDM, which no one uses
With a MDY connection, it reads it as YMD, with is the ODBC standard|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> > I need some help with Date Conversions. I am getting the following
error
> > when I run the query below.
> >
> > The conversion of a char data type to a datetime data type resulted in
an
> > out-of-range datetime value.
> >
> > declare @.inputdate char(10)
> > declare @.tempdate datetime
> > declare @.validdate char(10)
> >
> > SET @.inputdate = '15/05/2004'
> >
> > SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
> Company -
> > Dev$Base Calendar Change]
> > WHERE Date = @.InputDate and Nonworking = '1')
> > if @.tempdate is not null
> > SET @.validdate = '0' else SET @.validdate = '1'
> > select @.validdate
> >
> >
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>

Thursday, March 29, 2012

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

Sunday, March 25, 2012

Date problem

Date Picker formatting

Date Picker formatting

Thursday, March 22, 2012

Date Parameter ERROR

Wednesday, March 21, 2012

date parameter

Monday, March 19, 2012

Date Insertion problem

Thursday, March 8, 2012

Date format US/UK

Wednesday, March 7, 2012

date format in raiserror description

Date Format Data Driven Subscription

Friday, February 24, 2012

Date Diff

Sunday, February 19, 2012

Date Conversion throws truncation error

Date conversion error on 2005

Date conversion error on 2005

Date conversion error on 2005

Date conversion ?

Friday, February 17, 2012

Date Conversion

Date/Time Stamp

Blog Archive

About Me