convert a date stored as a string into a datetime

Hello forum,

Is it possible to convert a date stored as a string into a datetime with integration services 2005? My attempts with the “data conversion” fail. The string type form of the date is ‘yyyy-mm-dd’ and the desired result for use in a Union All is ‘dd/mm/yyyy 12:00:00AM.’This outcome is needs so that match on the date can populate a fact table, as the results are coming from two different databases.

All advice/help welcomed.

Ian

Use the Derived Column transform, and add this expression:

Code Snippet

(DT_DATE)((SUBSTRING(StringDate,6,2) + "-" + SUBSTRING(StringDate,9,2) + "-" + SUBSTRING(StringDate,1,4)))

Tip: Because there is no domain integrity inherent in the string date format, be certain to include an error output on your Derived Column transform.

|||

Use a dervide column with substring to re-order the date format; at the end cast it as date:

Code Snippet

(DT_DATE)(SUBSTRING(StrDAte,9,2) + "/" + SUBSTRING(StrDAte,6,2) + "/" + SUBSTRING(StrDAte,1,4))

|||Since this is a common topic today, I blogged on it, with a little more detail than what is posted here: http://bi-polar23.blogspot.com/2007/05/having-trouble-getting-date.htmlsqlsql

convert a Boolean to either String or Text?

Hi,

Does any one know how to convert a Boolean to either String or Text?
I came across the ToText ( ) function but I can't seem to get it to work.
According to the Crystal Reports For Visual Studio .NET ( Wrox book )
the ToText ( ) the function should work to convert Booleans... but I have not IDEA HOW , since they don't provide any
example! Can any one please shed some light? or maybe provide a better solution?

thank you in advance.

C.What database are you talking about?|||Originally posted by Brett Kaiser
What database are you talking about?

SQL Server. Did I post this question on the wrong Forum?|||There is no ToText function in SQL server. That is a Crystal function. SQL Server does not even technically use "boolean" values. It uses the BIT value instead. It's possible that Crystal is misinterpreting the values it is receives from SQL server, because it is well-document that Crystal reports sucks big-time.

blindman|||Originally posted by blindman
There is no ToText function in SQL server. That is a Crystal function. SQL Server does not even technically use "boolean" values. It uses the BIT value instead. It's possible that Crystal is misinterpreting the values it is receives from SQL server, because it is well-document that Crystal reports sucks big-time.

blindman

I agree on the SUCK big time if we are talking about CR.NET. Version 8.5 seems to be pretty good to me. In any case, I guess I should have asked the question in terms of SQL Server since I want to create a VW and put it on the CR.NET as a DataSet. I have just realized that the way to convert a Boolean ( or Bit, thanks for the clarification) is as follows:

select tbl.fieldname = (case tbl_name when boolean_value then 'yourString' end) from tblName

Thank you,

P.|||The only technical equivalent to what I think you're asking is:

CASE
WHEN [MyColumn] = 0 THEN 'NO'
WHEN [MyColumn] = 1 THEN 'YES'
ELSE 'Null'
END CASE

That is assuming of course that the column is defined as:

MyColumn BIT NULL

The above CASE statement accepts that the field might be null. If that's not the case (the column allows no nulls), then you can just use two lines (omit the 'ELSE NULL').

Other than that, blindman is right. There isn't a data type called 'boolean' in SQL Server and ToText is definitely not a standard SQL function.

Good Luck,

hmscott|||Sorry for the duplicate post; you beat me by a couple of minutes. tHet's kuz i suk at tping.

hmscott|||Originally posted by hmscott
Sorry for the duplicate post; you beat me by a couple of minutes. tHet's kuz i suk at tping.

hmscott

Your query also worked. Thank you hmscott. I'll see you arround.

Convert $ to varchar decimal problem

I'm trying to convert a check amount to a fixed length string with
leading zeros and no decimal point. All is well, except for the pesky
decimal point. Here is what I have:
COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
(CONVERT(varchar(12),ckamt))
Thanks for any ideas on how to accomplish this.What exactly do you need? Could you give an example of the expected result?
E.g. I have 3.04, I want 003...
ML
http://milambda.blogspot.com/|||Multiply the number by 100 (or which ever multiple of 10 will remove the
decimal place), turn that number into an integer and then convert it into a
string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
Ta,
M. E. Houston
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Take a look at this
declare @.d decimal(12,2)
select @.d =3.04
select @.d, right('000000000000' +
convert(varchar,replace(@.d,'.','')),12)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
birdbyte@.gmail.com wrote:
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.|||Try,
declare @.m money
declare @.i int
set @.m = 12345.54
set @.i = 12
select replace(str(round(@.m, 0, 1), @.i, 0), ' ', '0')
go
AMB
"birdbyte@.gmail.com" wrote:

> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Replicate to 13, and then REPLACE({your stuff below}, '.', '')
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||I don't think this this idea is quite right. If you REPLACE() the decimal in
the decimal value, it will round.
I think you need to REPLACE() the decimal after it is converted to a
varchar().
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1151514826.136274.129340@.x69g2000cwx.googlegroups.com...
> Take a look at this
> declare @.d decimal(12,2)
> select @.d =3.04
> select @.d, right('000000000000' +
> convert(varchar,replace(@.d,'.','')),12)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> birdbyte@.gmail.com wrote:
>|||Or convert the decimal to an int then convert to varchar. These two
methods are assuming you want to always round down.
On Wed, 28 Jun 2006 11:52:35 -0500, "M. E. Houston"
<m.e.houston@.gmail.com> wrote:

>Multiply the number by 100 (or which ever multiple of 10 will remove the
>decimal place), turn that number into an integer and then convert it into a
>string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
>Ta,
>M. E. Houston
><birdbyte@.gmail.com> wrote in message
>news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
>|||Great suggestion. Thanks.
Arnie Rowland wrote:
> Replicate to 13, and then REPLACE({your stuff below}, '.', '')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <birdbyte@.gmail.com> wrote in message
> news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...

Conversion failed when converting from a character string to uniqueidentifier. - PLEASE HE

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.

Conversion failed when converting from a character string to uniqueidentifier.

My Code is shown below:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

This has worked before but isnt' anymore. Any ideas?

jsmith3465:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

have you tried as...

comSQL.Parameters.AddWithValue("@.PROPERTYID",New Guid(Format("werwerwerwerwerwerwerwerwerwerwe","{0:########-####-####-####-############}")))

|||

I tried adding the New Guid() and that did not solve the problem either. Anymore ideas? I am trying to convert Text to Unique Identifier for storage in SQL Server 2005.

Thanks for all of your help!

Ryan

Conversion failed when converting from a character string to uniqueidentifier.

Hi, i have a problem, i keep getting this Error.

I want to insert an uniqueidentifier using a textbox, i use the following code to insert.

SqlDataSource1.InsertParameters[

"RWID"] =newParameter("RWID",TypeCode.String, RWID);

SqlDataSource1.Insert();

The databasetype is an uniqueidentifier of that column.

Anyone who can help me with this problem?

Hi friend,

Have you tried TypeCode.Object

|||

I tried using TypeCode.Object, then I get another error:

Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.

|||

Hi friend,

I tried a sample to reproduce the error. But it its working fine for me. I created a table named t1 with one column c1 of datatype unique identifier.

SQL datasource code

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:iGoldWebConnectionString %>"

SelectCommand="SELECT * FROM [T1]"InsertCommand="insert into t1 values(@.g)" ></asp:SqlDataSource>Data Insert Code

SqlDataSource1.InsertParameters["g"] =newParameter("g",TypeCode.String,Guid.NewGuid().ToString());

SqlDataSource1.Insert();

Its working fine for me.

I hope the problem is with the guid which you get from textbox . Have you checked you receive only valid GUID.

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anything

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anythingsqlsql

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), '')
,
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are dat
e
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anything

Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh

Conversion failed when converting character string to smalldatetime data type.

Hello, I have problem with this code.(This programpresents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.

Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.

Conversion failed when converting character string to smalldatetime data type.

Here is code:

<%

@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string datePatt =@."yyyymmdd";

// Get start and end of day

DateTime StartDate =DateTime.Today;

string @.StartDate1 = StartDate.ToString(datePatt);

string @.EndDate = StartDate.AddDays(1).ToString(datePatt);// Get start and end of weekstring @.startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);string @.startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);

switch (DropDownList1.SelectedValue)

{

case"1":

// day

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.StartDate1'" +" AND [DATE] < " +"'@.EndDate'";break;case"2"://week

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.startOfWeek'" +"AND [DATE] <" +"'@.startOfNextWeek'";break;

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title><styletype="text/css">body {font:1emVerdana;

}

</style>

</

head>

<

body><formid="form1"runat="server"><div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"Style="z-index: 100; left: 414px; position: absolute; top: 22px"><asp:ListItemSelected="True"Value="1">jeden den</asp:ListItem><asp:ListItemValue="2">jeden tyden</asp:ListItem></asp:DropDownList>

<asp:GridViewID="GridView1"runat="server"Style="z-index: 102; left: 228px; position: absolute;

top: 107px"

DataSourceID="SqlDataSource1"AutoGenerateColumns="True">

</asp:GridView> <br/><br/><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=CR\SQLEXPRESS;

Initial Catalog=MyConn;Integrated Security=True"

ProviderName="System.Data.SqlClient"></asp:SqlDataSource></div></form>

</

body>

</

html>

string datePatt =@."yyyymmdd";

In your 'date' pattern, mm is minutes.

You want MM for month: yyyyMMdd

|||Thank you, for your reply, Icorrected this error. But the problem is still here.|||

I notice you are trying to use parameters but I don't see the code that adds parameters: Parameters.Add(...).

If you pass in the date as a DateTime, you will not have to format it to a string.

|||Thank you, I will try.

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

Thanks

anybody here?|||

The issue here seems to be specific to the authoring in the ASP.Net pages...someone in the ASP.Net forum may be able to help you.

Here's a potentially useful links to start with: http://forums.asp.net/thread/881828.aspx

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

</asp:SqlDataSource>

|||

Now I am getting following error

Must declare the scalar variable "@.DropDownList1".

How do I do it in asp, I found the syntax for declare statement but don't know how put it in asp?

Thanks

|||Go to the design view, right click the sqldatasource, choose properties, then choose the selectcommand. A dialog should open, in there you can add all your parameters for the select.|||

I did that and still getting same error

after changes it looks like this

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data"InsertCommand="INSERT INTO data(Apps, Location, Impact, System, tDate, Start_Time, End_Time, Duration, Problem, Cause, Solution, DW_Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

<SelectParameters>

<asp:FormParameterFormField="DropDownList1"Name="@.Apps"/>

<asp:FormParameterFormField="DropDownList2"Name="@.Location"/>

<asp:FormParameterFormField="DropDownList3"Name="@.Impact"/>

<asp:FormParameterFormField="TextBox6"Name="@.System"/>

<asp:FormParameterFormField="DropDownCalendar1"Name="@.tdate"/>

<asp:FormParameterFormField="DropDownCalendar2"Name="@.Start_time"/>

<asp:FormParameterFormField="DropDownCalendar3"Name="@.End_time"/>

<asp:FormParameterFormField="TextBox1"Name="@.Duration"/>

<asp:FormParameterFormField="TextBox2"Name="@.Problem"/>

<asp:FormParameterFormField="TextBox3"Name="@.Cause"/>

<asp:FormParameterFormField="TextBox4"Name="@.Solution"/>

<asp:FormParameterFormField="TextBox5"Name="@.DW_case"/>

<asp:FormParameterFormField="TextBox7"Name="@.Comments"/>

</SelectParameters>

</asp:SqlDataSource>

I really appreciate your.

Thanks

|||I also tried doing samething with insert parameter and still no luck|||You have to call them the same thing. @.DropDownList1=@.Apps. Change one to the other and repeat for all parameters. Either change the insert statement, replacing @.DropDownList1 with @.Apps, *OR* change the parameter "@.Apps" to use the name "@.DropDownList1".|||

Motley

I really appreciate your help,

And a BIG Thanks to you.

sqlsql

Conversion datetime to string

Hi
Can we convert DateTime to String. If so how?
Thanks in advance.

Mahathi.My problem has been solved.

Conver String to Date

Hi all,
I am trying to convert the string value of '12.01.50' to a propert date
value of 12/01/1950, I have tried various things but cant seem to get it int
o
the right format although I can convert it to a date type. Can anyone help?
Thanks PhilYou either have to do the conversion going directly from string to string. O
r you have to go from
string to datetime and then string again. You can't just go from string to d
atetime, since datetime
doesn't have any format (the client application does the formatting). So, so
mething like:
CONVERT(varchar(zz), CONVERT(datetime, '12.01.50' , xxx), yyy)
Where xxx and yyy are the appropriate formatting codes (documented in Books
Online, CONVERT). I
prefer to do formatting in the client app, though. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:24C094A5-2B67-49B3-A40D-BECF53AB5530@.microsoft.com...
> Hi all,
> I am trying to convert the string value of '12.01.50' to a propert date
> value of 12/01/1950, I have tried various things but cant seem to get it i
nto
> the right format although I can convert it to a date type. Can anyone hel
p?
> Thanks Phil

control concurrent users?

is it possible to limit the concurrent users in SQL connection string? Now, i m using .net 2003.

regards,

You can change the "max worker threads" option in SQL Server by running the statements below:

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'max worker threads',255
reconfigure
go

For more information about this option, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_09wu.asp

|||

the problem is how can i configure my customer's SQL Server configuration. Now, i get one way. is it the right one?

Data Source=XXX;database=XXX;UID=XXX;PWD=XXX;MAX POOL SIZE=5

|||Sorry for misunderstanding. Yes the MAX POOL SIZE property should work in this case.

Contains(@v1, @v2) Is this legal?

I am attempting to perform a contains of one variable string in another.
here is a simple example of what I am attempting to do, this should return
true, but I am not sure if this is a limitation of sql server, that it will
now allow a contains on two datatypes. Any ideas?
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if (Contains(@.t2, @.t1))
print 'true'
else
print 'false'
Thanks.Hi, kapsolas
You probably want to use the CHARINDEX function:
IF CHARINDEX(@.t1,@.t2)<>0 ...
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms186323.aspx
Razvan|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
>I am attempting to perform a contains of one variable string in another.
> here is a simple example of what I am attempting to do, this should return
> true, but I am not sure if this is a limitation of sql server, that it
> will
> now allow a contains on two datatypes. Any ideas?
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if (Contains(@.t2, @.t1))
> print 'true'
> else
> print 'false'
> Thanks.
Another solution:
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if @.t2 like '%' + @.t1 + '%'
print 'true'
else
print 'false'|||Raymond,
that is the solution I have implemented. Using the LIKE. I wanted to clean
it up a bit to make it more readable by using the Contains.
I'll play with the char index as recommended in the other post as well.
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
> Another solution:
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if @.t2 like '%' + @.t1 + '%'
> print 'true'
> else
> print 'false'
>
>|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> Raymond,
> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
> I'll play with the char index as recommended in the other post as well.
I have no experience with Contains.
This is the information I got in BOL:
...You can use the CONTAINS predicate to search a database for a specific
phrase. Of course, such a query can be written using the LIKE predicate.
However, many forms of CONTAINS provide far more text query capabilities
than can be obtained with LIKE. Additionally, unlike using the LIKE
predicate, a CONTAINS search is always case insensitive...
So, if you are not using the extra "query capabilities" of Contains, I
suggest you use one of the other solutions that you got for this post.
Of course, the best would be to test all solutions with your database and
data to find the one that performs the best.|||Thanks for that piece Raymond.
For now I'll use the LIKE and as soon as I have a bit more time i'll
investigate the contains a bit more.
Thanks for your help
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> I have no experience with Contains.
> This is the information I got in BOL:
> ...You can use the CONTAINS predicate to search a database for a specific
> phrase. Of course, such a query can be written using the LIKE predicate.
> However, many forms of CONTAINS provide far more text query capabilities
> than can be obtained with LIKE. Additionally, unlike using the LIKE
> predicate, a CONTAINS search is always case insensitive...
> So, if you are not using the extra "query capabilities" of Contains, I
> suggest you use one of the other solutions that you got for this post.
> Of course, the best would be to test all solutions with your database and
> data to find the one that performs the best.
>
>|||> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
I don't know why you think that's cleaner or more readable. I guess for
someone who has never used T-SQL and only used FTS, but I think that'd be a
pretty rare bird.
A

CONTAINS with substring

I have a table field that is full-text indexed. I am trying to locate
records where a substring of data is present in a string without spaces. The
format is something like this:
"AAAAAABBAAAA"
If I am looking for the existence of "AABBA" in the string, CONTAINS
apparently will not work, because this command works only with complete words
with spaces between. So this type of query fails:
CONTAINS(fieldname, "AABBA")
or
CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
Is there a way to use CONTAINS with substrings like this?
seeker
No, the only thing you can do is store the string in reverse in your table
you are FTI'ing, and then reserve the search string and do wildcarding. This
only works if you are searching for suffixes, not letter patterns in the
middle of a word/token.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"seeker" <seeker@.discussions.microsoft.com> wrote in message
news:3F5BA0DE-AC1B-44A0-886E-8D54E2AFC3A0@.microsoft.com...
> I have a table field that is full-text indexed. I am trying to locate
> records where a substring of data is present in a string without spaces.
The
> format is something like this:
> "AAAAAABBAAAA"
> If I am looking for the existence of "AABBA" in the string, CONTAINS
> apparently will not work, because this command works only with complete
words
> with spaces between. So this type of query fails:
> CONTAINS(fieldname, "AABBA")
> or
> CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
> Is there a way to use CONTAINS with substrings like this?
> --
> seeker

Contains Predicate and Double Quotes

I have been searching for an escape character or a way of escaping
double quotes that are actually in a string that I am using in the
contains predicate.

Here is an example

select *
from table
where contains(field, '"he said "what is wrong", that is what he
said"')

I need the double quotes in the string because they are part of the
text. Of course, Fulltext search raises the error

Server: Msg 7631, Level 15, State 1, Line 1
Syntax error occurred near 'what is wrong", that is what he said'.
Expected '' in search condition '"he said "what is wrong", that is
what he said"'.

If I remove the double quotes, the search does not return the proper
results.

Thanks in advance for the help
Bill"swtwllm" <swtwllm@.alum.iup.edu> wrote in message
news:c38d6cfb.0402201036.6cdf8d87@.posting.google.c om...
> I have been searching for an escape character or a way of escaping
> double quotes that are actually in a string that I am using in the
> contains predicate.
> Here is an example
> select *
> from table
> where contains(field, '"he said "what is wrong", that is what he
> said"')
> I need the double quotes in the string because they are part of the
> text. Of course, Fulltext search raises the error
> Server: Msg 7631, Level 15, State 1, Line 1
> Syntax error occurred near 'what is wrong", that is what he said'.
> Expected '' in search condition '"he said "what is wrong", that is
> what he said"'.
> If I remove the double quotes, the search does not return the proper
> results.
> Thanks in advance for the help
> Bill

It looks like this has been answered in
microsoft.public.sqlserver.fulltext - please don't post to multiple
newsgroups separately.

Simon|||swtwllm (swtwllm@.alum.iup.edu) writes:
> I have been searching for an escape character or a way of escaping
> double quotes that are actually in a string that I am using in the
> contains predicate.
> Here is an example
> select *
> from table
> where contains(field, '"he said "what is wrong", that is what he
> said"')
> I need the double quotes in the string because they are part of the
> text. Of course, Fulltext search raises the error
> Server: Msg 7631, Level 15, State 1, Line 1
> Syntax error occurred near 'what is wrong", that is what he said'.
> Expected '' in search condition '"he said "what is wrong", that is
> what he said"'.
> If I remove the double quotes, the search does not return the proper
> results.

I would expect doubling the quotes would help, but I don't use full-text
myself, so I don't know.

microsoft.public.sqlserver.fulltext may a better place to ask.

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

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

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!