Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert String to Date Expand / Collapse
Author
Message
Posted Monday, October 17, 2005 3:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 23, 2006 6:59 AM
Points: 15, 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

Post #229277
Posted Monday, October 17, 2005 4:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 1,390, Visits: 6,345

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

Should do it.

Post #229280
Posted Monday, October 17, 2005 4:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 19, 2014 2:23 AM
Points: 217, Visits: 141
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**/
Post #229281
Posted Monday, October 17, 2005 5:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:49 AM
Points: 2,553, Visits: 578
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 !!!**
Post #229283
Posted Monday, October 17, 2005 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 23, 2006 6:59 AM
Points: 15, Visits: 1
Thank you very much for your replies
Post #229292
Posted Monday, October 17, 2005 6:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

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




Post #229293
Posted Wednesday, October 19, 2005 8:26 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:24 AM
Points: 685, 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.

Post #230173
Posted Thursday, April 10, 2008 2:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 21, 2008 7:03 AM
Points: 19, 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
Post #482822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse