Sunday, February 12, 2012

Convert datetime to char string

Hi all,
CREATE TABLE Result (
ValueString char(8),
ValueDate datetime
)
Sample data from ValueDate column in british (103) format:
29/1/2005
1/12/2004
1/1/2003
How to convert to ValueString respectively
20050129
20041201
20030101
Thanks
ZedI'd use a nested CONVERT. First use convert into a datetime, adding the type
103 as 3;rd parameter.
Then you use an outer convert into a string, using code 112.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Private Man" <orivate@.yahoo.com.au> wrote in message
news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> Hi all,
> CREATE TABLE Result (
> ValueString char(8),
> ValueDate datetime
> )
> Sample data from ValueDate column in british (103) format:
> 29/1/2005
> 1/12/2004
> 1/1/2003
> How to convert to ValueString respectively
> 20050129
> 20041201
> 20030101
> Thanks
> Zed
>|||UPDATE Result
SET ValueString = CONVERT(CHAR(8), ValueDate , 112)
Jacco Schalkwijk
SQL Server MVP
"Private Man" <orivate@.yahoo.com.au> wrote in message
news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> Hi all,
> CREATE TABLE Result (
> ValueString char(8),
> ValueDate datetime
> )
> Sample data from ValueDate column in british (103) format:
> 29/1/2005
> 1/12/2004
> 1/1/2003
> How to convert to ValueString respectively
> 20050129
> 20041201
> 20030101
> Thanks
> Zed
>|||select convert(char(10), ValueDate, 112) from Result
Enjoy
Peter
"The best minds are not in government. If any were, business would steal
them away."
Ronald Reagan
"Private Man" wrote:

> Hi all,
> CREATE TABLE Result (
> ValueString char(8),
> ValueDate datetime
> )
> Sample data from ValueDate column in british (103) format:
> 29/1/2005
> 1/12/2004
> 1/1/2003
> How to convert to ValueString respectively
> 20050129
> 20041201
> 20030101
> Thanks
> Zed
>
>|||or if i'm going to be exact select convert(char(8), ValueDate, 112) from
Result.
Peter
"I only made one mistake today - got up"
"Peter 'Not Peter The Spate' Nolan" wrote:
> select convert(char(10), ValueDate, 112) from Result
> Enjoy
> Peter
> "The best minds are not in government. If any were, business would steal
> them away."
> Ronald Reagan
>
> "Private Man" wrote:
>|||Thanks
Zed
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ueZEy$VJFHA.3132@.TK2MSFTNGP12.phx.gbl...
> UPDATE Result
> SET ValueString = CONVERT(CHAR(8), ValueDate , 112)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Private Man" <orivate@.yahoo.com.au> wrote in message
> news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>|||My bad. I missed that ValueDate already is datetime. The inner CONVERT isn't
needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:eKTO0$VJFHA.1280@.TK2MSFTNGP09.phx.gbl...
> I'd use a nested CONVERT. First use convert into a datetime, adding the ty
pe 103 as 3;rd
> parameter. Then you use an outer convert into a string, using code 112.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Private Man" <orivate@.yahoo.com.au> wrote in message
> news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>

No comments:

Post a Comment