Convert field from YYYYMMDD format to MM/DD/YYYY ?

Question

Convert field from YYYYMMDD format to MM/DD/YYYY ?

SQL SERVER ROOKIE

Ten Centuries

Points: 1032
More actions
November 19, 2012 at 2:54 pm

#265013

Hello,
I have a date filed in the YYYYMMDD format ,want to convert it into MM/DD/YYYY.
I have tried using
SELECT convert(varchar, MY_date_field,101) from mytable;
it still gives result in YYYYMMDD format
Thanks
SM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

swarup.malli (11/19/2012)
Hello,
I have a date filed in the YYYYMMDD format ,want to convert it into MM/DD/YYYY.
I have tried using
SELECT convert(varchar, MY_date_field,101) from mytable;
it still gives result in YYYYMMDD format
Thanks
SM

Is this a datetime column? I suspect it is NOT a datetime column. Your code will convert a datetime to the display format you desire. It will NOT convert a varchar column to anything other than the original data.

SELECT convert(varchar, '20121119',101), convert(varchar, getdate(),101)

This is one of about 1293058390473054 reason to use datetime datatypes for datetime information instead of varchar.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 2

Hi Sean,

You are right mycolumn is defined as decimal in its DDL statement.

Thanks

SM

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 3

Hi Sean,

You are right mycolumn is defined as decimal in its DDL statement.

SELECT convert(varchar, mycolumn,101)

FROM Mytable;

that's why the above code is not giving the right result.

COuld you help me with the syntax ?

I have been working on this for a while..all my efforts to get the right result are futile ;(

Thanks

SM

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

swarup.malli (11/19/2012)
Hi Sean,
You are right mycolumn is defined as decimal in its DDL statement.
SELECT convert(varchar, mycolumn,101)
FROM Mytable;
that's why the above code is not giving the right result.
COuld you help me with the syntax ?
I have been working on this for a while..all my efforts to get the right result are futile ;(
Thanks
SM

EEK! Any chance you can make it a datetime? Why decimal?

To work with it as is you need to make sure you that your dateformat is the same as the table is stored and that you don't have any values that are not valid datetimes values.

The real ugliness is that you have to first convert your decimal to a varchar and to a datetime so that you can convert it to a display value. UGH!!! Display formatting really should happen in the front end...of course that isn't always possible.

Here is one way that will work. This is likely to perform horribly if you have very much data because it has to be fiddled with so much.

create table #BadDates

(

ID int identity,

BadDate decimal

)

insert #BadDates

select 20121119 union all

select 20121118

select BadDate, convert(varchar, cast(CAST(BadDate as CHAR(8)) as datetime), 101) from #BadDates

drop table #BadDates

Like I said, the best approach is to use datetime. There are few absolutes in SQL server but you should always always always store datetime data in a datetime column. Validation, presentation, calculation etc...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 5

man, if at all possible, change your columns to true datetimes immediately.

Like Sean said, one of a billion plus reasons to use the right datetime data type is headaches like this one.

here's one possibility to convert the data to date time,a dn than back to a varchar format:

--YYYYMMDD decimal

With MyDatesAsDecimals

AS

(

SELECT CONVERT(DECIMAL(8,0),20120708) AS Val UNION ALL

SELECT 20121212 UNION ALL

SELECT 20121225 UNION ALL

SELECT 20130101

)

SELECT CONVERT(datetime,STR(Val)),

convert(varchar, CONVERT(datetime,STR(Val)),101)

FROM MyDatesAsDecimals

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 6

Thank u Sean,

Actually I was trying to create a view ,I can't modify the real table.

SM

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 7

Thank you for the reply Lowell,

I was trying to create a view for all the tables.

Could you demostrate how to implement this commoand inside a DDL statement for creating view

Thanks

SM

Lowell SSC Guru Points: 323518 More actions · Answer 8

swarup.malli (11/19/2012)
Thank you for the reply Lowell,
I was trying to create a view for all the tables.
Could you demostrate how to implement this commoand inside a DDL statement for creating view
Thanks
SM

look at the code, it's right there:

CREATE VIEW VW_

AS

SELECT

convert(varchar, CONVERT(datetime,STR(Val)),101)

FROM MyDatesAsDecimals

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

swarup.malli (11/19/2012)
Thank you for the reply Lowell,
I was trying to create a view for all the tables.
Could you demostrate how to implement this commoand inside a DDL statement for creating view
Thanks
SM

You can wrap a similar cte to Lowell's with CREATE VIEW VIEWNAME. 😀

--EDIT--

Once again Lowell typed faster than me.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 10

Lowell,

I tried the following code

select CONVERT (varchar, CONVERT(datetime,STR(MY_COLUMN)),101)

FROM mytable;

It is giving me the following error

Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

MY_COLUMN is defined as decimal in its originakl ddl code

SM

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

SQL server rokee (11/19/2012)
Lowell,
I tried the following code
select CONVERT (varchar, CONVERT(datetime,STR(MY_COLUMN)),101)
FROM mytable;
It is giving me the following error
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
MY_COLUMN is defined as decimal in its originakl ddl code
SM

This goes back to my previous comment. You have some data in that table that is not a valid datetime. You might try ordering first by that column and look at the top and bottom values. Then order by len(Yourcolumn), again look at both ends. Are they always 8 characters? Are your single digit months and days "zero padded". In other words do you have values like '20120801' or do you have '201281'? This is potentially going to take quite a bit of digging and fiddling with this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/