Convert String to Date

Question

Post reply

Convert String to Date

sada-268493

Old Hand

Points: 309
More actions
October 17, 2005 at 3:59 am

#169183

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

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

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

SELECT convert(datetime,'20050910') as myDate

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

Should do it.

VivekKumar Gupta Ten Centuries Points: 1219 More actions · Answer 2

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 SSC-Dedicated Points: 35293 More actions · Answer 3

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 Old Hand Points: 309 More actions · Answer 4

sada-268493

Old Hand

Points: 309

October 17, 2005 at 6:52 am

#597574

Thank you very much for your replies

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

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 SSChampion Points: 12335 More actions · Answer 6

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 Ten Centuries Points: 1013 More actions · Answer 7

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