DDMMCCYY to DD/MM/CCYYY 1

[NiceArms]

Basically I am trying to convert a date (which is stored as a VARCHAR(8)) in the format DDMMCCYY into DD/MM/CCYY.

What I have tried:

1. CONVERT(VARCHAR,Create_Date,103) - returns: DDMMCCYY

2. CONVERT(VARCHAR,CAST(Create_Date as datetime),103) - returns: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Any suggestions would be appreciated

/Nice

 

[gmmastros]

do you mean like this....

declare @Test VarChar(8)

Set @Test = '07212009'

Select Stuff(Stuff(@Test, 5, 0, '/'), 3, 0, '/')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NiceArms]

Thank you for you help once again George

Just to tax your brain a little more, why would the CONVERT not work?


/Nice

 

[gmmastros]

convert didn't work because there are no date delimiters. There are only 2 unambiguous date formats when converting strings to date.

Some countries use dd/mm/yyyy and others use mm/dd/yyyy. If you have date delimiters, then the language setting of the login account is used to determine the date.

The 2 unambiguous date formats are:

YYYYMMDD hh:mm:ss.mmm
YYYY-MM-DDThh:mm:ss.mmm

Basically... if you have a string of 8 numbers without any separators, SQL Server interprets it as YYYYMMDD

So, using you format, today would be 07222009. SQL Server would interpret that to be year 722, 20th month, 9th day.

For more reading on dates...

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NiceArms]

Thank you for taking the time to respond.

I will look at the additional info you have suggested.

/Nice