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"
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"
--=_NextPart_000_0181_01C37783.62DF5350--
No comments:
Post a Comment