DATETIME TO VARCHAR

Question

DATETIME TO VARCHAR

er sql

SSC Eights!

Points: 943
More actions
June 9, 2005 at 9:25 am

#68015

Hi, I have a column that stores a Datetime value and I need to insert the value in other table but in varhcar, like this:
Table A (Source)                 Table B (Destiny)
Column DateA (DATETIME)     Column DateB (VARCHAR 10)
Ene 06 2005                        20050106
Table B and column DateB is the desire format.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Try using convert.

the third argument is style, thats where you can control what you want the date to look like, Look up cast and convert in sql books on line

create table Temp1(pk int identity, day1 datetime)

create table Temp2(pk int identity, day1 varchar(10))

insert into temp1(day1)

values ('01/01/2005')

insert into temp1(day1)

values ('01/02/2005')

insert into temp1(day1)

values ('01/03/2005')

insert into Temp2 (day1)

select convert(varchar(10),day1, 112)

from temp1

select * from temp2

ron k SSCertifiable Points: 6103 More actions · Answer 2

If you're looking for the format to be yyyyddmm

select cast(datepart(yy,getdate()) as varchar) +

Right ('00' + cast(datepart(dd,getdate())as varchar),2) +

Right ('00' + cast(datepart(mm,getdate()) as varchar),2)

----------------------------------

20050906

Edit: For yyyymmdd see the post above.

Greg Grow SSCrazy Points: 2386 More actions · Answer 3

You can also use the convert with a style. If you want to store this as a YYYYMMDD, the column needs to be varchar. This shows the type of column the table needs to be to store as 8 characters.

create table #t (datecol datetime,

varcol varchar(8))

insert into #t

select convert(datetime, getdate(), 112),

convert(varchar(8), getdate(), 112)

select * from #t

drop table #t

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I would also go with the CONVERT. However, I would use CHAR(8) instead of VARCHAR(8).

However, the more interesting question is, why would you want to do this?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

SET NOCOUNT ON

DECLARE @CTR INT

SET @CTR = 100

WHILE @CTR < 115

BEGIN

SELECT @CTR Val, CONVERT(VARCHAR, GETDATE(), @CTR) DteFormat

SET @CTR = @CTR + 1

END

Regards,
gova

ron k SSCertifiable Points: 6103 More actions · Answer 6

I also prefer to use convert but the example given by the original poster is ambiguous and I thought they were looking for a format of YYYYDDMM which I don't believe can be done using convert/style.

Edit: I see now that the original post was in Spanish! I took French and didn't do very well. Never took any Spanish classes.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 7

SELECT CONVERT(varchar(10),GETDATE(),112)

returns:

20050609

So CONVERT / Style can do it...

Andy

ron k SSCertifiable Points: 6103 More actions · Answer 8

Not exactly. That produces the format yyyymmdd. My statement (several posts above) produces the format yyyyddmm.