SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert String to Date


Convert String to Date

Author
Message
sada-268493
sada-268493
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 1

Hi

Could anyone help me on how to convert a string such as 20050910 to a date format e.g 10/09/2005.

Thanx


Jo Pattyn
Jo Pattyn
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7324 Visits: 10023

SELECT convert(datetime,'20050910') as myDate
select convert(varchar(20),convert(datetime,'20050910'),103) as myVarCharDate

Should do it.


VivekKumar Gupta
VivekKumar Gupta
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 150
SELECT SUBSTRING('20050910',7,2)+'/'+SUBSTRING('20050910',5,2)+'/'+SUBSTRING('20050910',1,4)

/**A strong positive mental attitude will create more miracles than any wonder drug**/
sushila
sushila
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8541 Visits: 639
Just a note of caution when using the date format styles - 103 will give you dd/mm/yyyy whereas 101 will give you mm/dd/yyyy...

select convert(varchar,convert(datetime,'20050910'),103) as UKDate
select convert(varchar,convert(datetime,'20050910'),101) as USDate







**ASCII stupid question, get a stupid ANSI !!!**
sada-268493
sada-268493
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 1
Thank you very much for your replies
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6851 Visits: 699

Agreed.

Be very careful when and where you use dateconversions.

I sincerely hope that the poster wants to know this for the purpose of display only, and not for writing!

I, as a thinking (hopefully ) being cannot say if '10/09/2005' is 9th of October or 10th of September, so how can we expect that a computer would be able to? Be extremely careful not to use formats such as '10/09/2005' when you save dates to tables as datetimes. For that purpose you should use yyyymmdd format only.

/Kenneth





bledu
bledu
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3037 Visits: 104
once had a problem with the date conversions.i ended up changing to 126 from 103 because of logical errors in reports that where being generated.


Everything you can imagine is real.

Emmanouil Karaiskakis
Emmanouil Karaiskakis
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 73
Hi,

I was wondering if you could give me some advice regarding the following:
Let's say you have a table with a varchar() column that contains date values in different format.

What if you wanted to convert them all in one format eg: DD/MM/YYYY

Here's some code to help you test.
Create table dbo.date
(
DATE VARCHAR(50)
)

Insert into dbo.date
VALUES ((DATEADD(yy,-45,GETDATE())))
Insert into dbo.date
VALUES ((DATEADD(yy,-35,GETDATE())))
Insert into dbo.date
VALUES ('Jan 1 2005')
Insert into dbo.date
VALUES ('11/23/98')
Insert into dbo.date
VALUES ('11/23/1998')
Insert into dbo.date
VALUES ('72.01.01')
Insert into dbo.date
VALUES ('1972.01.01')
Insert into dbo.date
VALUES ('19/02/72')
Insert into dbo.date
VALUES ('19/02/1972')
Insert into dbo.date
VALUES ('25.12.05')
Insert into dbo.date
VALUES ('25.12.2005')
Insert into dbo.date
VALUES ('24-01-98')
Insert into dbo.date
VALUES ('24-01-1998')
Insert into dbo.date
VALUES ('04 Jul 06')
Insert into dbo.date
VALUES ('04 Jul 2006')
Insert into dbo.date
VALUES ('Jan 24, 98')
Insert into dbo.date
VALUES ('Jan 24, 1998')
Insert into dbo.date
VALUES ('Apr 28 2006')
Insert into dbo.date
VALUES ('01-17-06')
Insert into dbo.date
VALUES ('98/11/23')
Insert into dbo.date
VALUES ('1998/11/23')
Insert into dbo.date
VALUES ('980124')
Insert into dbo.date
VALUES ('19980124')
Insert into dbo.date
VALUES ('28 Apr 2006')
Insert into dbo.date
VALUES ('1972-01-21')
Insert into dbo.date
VALUES ('1972-02-19')

Your help is really appreciated.
Manos
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search