Conditional Formatting - text to text

Hello,
My query returns numbers for months. Can I use conditional formatting to
convert a 1 into Jan, 2 into Feb, ect?
I know that there is a way to do this within the query itself, but am
curious as to the conditional formatting way. Thanksabunch.You could use the MonthName function to achieve this effect in the report:
=MonthName(CInt(Fields!Month.Value))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctMonthName.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Drew" <Drew@.discussions.microsoft.com> wrote in message
news:D4481668-F461-41CF-972B-162E88F716B8@.microsoft.com...
> Hello,
> My query returns numbers for months. Can I use conditional formatting to
> convert a 1 into Jan, 2 into Feb, ect?
> I know that there is a way to do this within the query itself, but am
> curious as to the conditional formatting way. Thanksabunch.

concatenation uniqueidentifier into nvarchar

where t.[PortalID] = '

+convert(nvarchar(36), @.PortalID)+'

where t.[PortalID] ='+ convert(nvarchar(36), @.PortalID) +'

Im trying to concate a uniqueidentifier into a BIG nvarchar(4000) string so i can execute it at the end (

exec

sp_executesql @.sql)

But my problem is that i got an error saying that i can not do so or the first 5 digit of the uniqueidentifier object is not well formated.

any ideas ? thank you

try:

where t.[PortalID] = '''+convert(nvarchar(36), @.PortalID)+'''

|||

It does not work.

|||

Needs to be quoted as per my previous post. However, I believe the you are getting a second problem. Most likely that you are trying to pass the uniqueidentifier from .NET code to sql, and you've either assigned the wrong type, or you have use concatenation that mangled the guid.

concatenation of '0' + converted interger value into a string?

I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts the
integer, my results remain unchanged. I would appreciate any assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not nullHi Jeff,
You can strip the monthpart out of string representation of a date, that
will always include a leading zero when necessary, so you don't have to
worry about that, for example:
SELECT CONVERT(CHAR(2), trandate, 1)
FROM Offtable where trandate is not null
Style 1 with convert returns mm/dd/yy, and we are only interested in the
leftmost two characters, so a CHAR(2) will do.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
> I am unsure as to why I cannot concatenate '0' with the when '1' case
> statement below. Even thought the convert statement explicitly converts
the
> integer, my results remain unchanged. I would appreciate any
assistance...
>
> SELECT
> case LEN(datepart(m,trandate))
> when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
> when '2' then DATEPART(M,trandate)
> end
> FROM Offtable where trandate is not null
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
There I go again:
select
replace (str (datepart (mm, TranDate), 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OEKO955dDHA.1152@.TK2MSFTNGP11.phx.gbl...
You can rewrite the statement:
select
replace (str (TranDate, 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message =news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts =the
integer, my results remain unchanged. I would appreciate any =assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not null
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

There I go again:
select
replace (str =(datepart (mm, TranDate), 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau" = wrote in message news:OEKO955dDHA.1152=@.TK2MSFTNGP11.phx.gbl...
You can rewrite the =statement:
select
replace (str =(TranDate, 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Humphrey" wrote in message news:uYhK335dDHA.1636=@.TK2MSFTNGP12.phx.gbl...I am unsure as to why I cannot concatenate '0' with the when '1' =casestatement below. Even thought the convert statement explicitly converts theinteger, my results remain unchanged. I would appreciate =any assistance...SELECT case LEN(datepart(m,trandate)) when '1' then '0' =+ CONVERT(varchar(1),DATEPART(M, trandate)) =when '2' then DATEPART(M,trandate) end FROM Offtable where =trandate is not null

---

--=_NextPart_000_0181_01C37783.62DF5350--

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?
<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegro ups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA
|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.
|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegr oups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegroups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegroups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating Numbers/Converting Dates to Integers

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:

SET @.Macola = Cast(Datepart(yy,@.MacolaDate) as varchar) + Cast(Datepart(mm,@.MacolaDate) as varchar) + Cast(Datepart(dd,@.MacolaDate) as varchar)

However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.just to be sure
what is the type of your variable @.Macola

doeas it HAVE to be integer ?|||Well, I am working with a database design that I cannot modify and it stores the dates in an int column with the format 20040101 and any conversions to the 100,000 values in the table takes too long, so I want to convert my date to an integer.|||i'm not sure if this is what you want

but create this user define function

CREATE function dbo.Date(@.Date DateTime)
returns Varchar(10) as
begin
return(
Cast(Datepart(yy,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(mm,@.Date) as varchar)))+Cast(Datepart(mm,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(dd,@.Date) as varchar)))+Cast(Datepart(dd,@.Date) as varchar)
)
end

then you'll be able to get easily

set @.Macola=dbo.date('1/1/2004')
set @.Macola=dbo.date(@.MacolaDate)

the Replicate+Len functions adds the 0 if necessary|||Thank you!|||is it what you needed ('cause i'm going offline)|||Yes it is, I appreciate your help.|||Cross post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32771

Anyway, what does your base data look like

Since you say you want it to be 20040101, are you assuming that that's the int value?

Where do you get 1/1/2004 from?

Some ddl might help

I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields,

That part confused me...|||Sorry for any confusion. Let me try this again.

I need to pass StartDate and EndDate parameters to the query I use as the record-source for several reports.

My database stores dates as integers in the format 20040101, but I don't want my end users to have to enter dates in that integer format, also, I figured it would be easier to convert the integer dates to datetimes in the query so that I wouldn't have to make this conversion on every report, it would already be done for me.

I wrote a T-SQL scalar function that converts from the integer format to a datetime format, and I just used that 'fnIntToDate(doc_date)' as the column returned in the header for the 3 date columns I have. This function looks like this, and works well:

Convert(Datetime,Left(Right(@.MacolaDate,4),2) + '/' + Right(@.MacolaDate,2) + '/' + Left(@.MacolaDate,4))

I do my development on a database with 1,000 rows, but our production database has over 100,000, and this date conversion (along with a few other things I have since fixed) caused the query to time-out. And according the the query analyzer, I can save 3-4 seconds PER COLUMN if I just leave the integers as integers and convert my date parameters to integers instead.

So I'm asking for help on a function that does the opposite of what my function does. I want to pass it a datetime value and have it return a date in the format 20040101. I had gotten all of it except the leading 0s, but I think the previous poster answered that for me, so i'm goign to go try that now.

Thank you.|||And what he told me worked great :). I just converted it to an integer and I'm good to go.