Solved

format date from yyyy/mm/dd to mm/dd/yyyy when this field is varchar type

Posted on 2004-10-04
19
491 Views
Last Modified: 2009-07-29
Hi,

We need to format date from yyyy/mm/dd to mm/dd/yyyy when this field type is char. Could anyone help us on this?

THanks.
0
Comment
Question by:mei_liu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +1
19 Comments
 
LVL 1

Accepted Solution

by:
xabimond earned 500 total points
ID: 12220722
If the data format is yyyy-mm-dd then you can convert to a datetime specifying the style like this:

select convert(datetime, '2004-10-04', 120)

if you want to convert this back to a char, but American date format then use this:

select convert(varchar, convert(datetime, '2004-10-04', 120), 101)

If you start date actually has the "/" rather than "-" the you'll need to use substring to reformat the date.  To convert to datetime do this:

select convert(datetime, substring('2004-10-04', 6, 2) + '/' + substring('2004-10-04', 9, 2) + '/' + substring('2004-10-04', 1, 4), 101)

or to char, but American format.

select substring('2004-10-04', 6, 2) + '/' + substring('2004-10-04', 9, 2) + '/' + substring('2004-10-04', 1, 4)

Remember SQL server doesn't hold dates in a particular style they are held as number where the integer part is the number of days past 1900-01-01 and the decimal represent miliseconds throught the day.   So most of the above is superfluous if you're just converting the char format to datetime.  You only have to worry about the format when you want to display the date.

Hope some of this helps!
0
 
LVL 34

Expert Comment

by:arbert
ID: 12220812
What?  format 101 will give you slashes:

select convert(varchar(10),getdate(),101)

returns:  10/04/2004
0
 
LVL 34

Expert Comment

by:arbert
ID: 12220824
Convert(varchar(10),getdate(),110) will return dashes:

10-04-2004
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:mei_liu
ID: 12220912
Thanks for your response. Here is what we have received:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." How could we ignore the invalid date entered in the system, yet continue converting varchar type to datetime type?

Thanks.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12220918
select convert(varchar(20),convert(datetime, '2004-10-04', 101),101)
0
 
LVL 34

Expert Comment

by:arbert
ID: 12220965
Select case isdate(yourdatecolumn)
   when 1 then convert(varchar(10),yourdate,101)
   else NULL end YourNewDate
0
 
LVL 34

Expert Comment

by:arbert
ID: 12220988
mcp111, select convert(varchar(20),convert(datetime, '2004-99-04', 101),101) returns a conversion error....
0
 
LVL 1

Expert Comment

by:xabimond
ID: 12220997
Format 101 will give you dashes but yyyy/mm/dd is not a recognised SQL Server date format hence the substrings.
0
 

Author Comment

by:mei_liu
ID: 12221029
xabimond's select substring('2004-10-04', 6, 2) + '/' + substring('2004-10-04', 9, 2) + '/' + substring('2004-10-04', 1, 4)
 worked. It also ignored the invalid date users entered.

Thanks all!
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12221052
arbert

please copy and paste properly before testing.
it is '2004-10-04' not '2004-99-04' as you have indicated in your post.
I have tested my code and it works fine!
0
 
LVL 34

Expert Comment

by:arbert
ID: 12221072
"recognised SQL Server date format hence "

Sure it is....

mcp111, I know, I was pasting to show you what happens with invalid dates....Mei_liu posted ""The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."    So I tested your post with invalid dates and it returned the same thing....
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12221118
I think my code is much more compact than xabimond's.

Also this is incorrect
"If you start date actually has the "/" rather than "-" the you'll need to use substring to reformat the date"

Check this code out. It works fine!

select convert(varchar(20),convert(datetime, '2004/10/04', 101),101)

It doesn't make any difference whether it is "/" or "-" in the input.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12221210
but it doesn't account for invalid dates...
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12221231
arbert.

even xabimond's code will not work with invalid dates.

I don't know how mei liu can say this
---------------------------------------------------------------------------
xabimond's select substring('2004-10-04', 6, 2) + '/' + substring('2004-10-04', 9, 2) + '/' + substring('2004-10-04', 1, 4)
 worked. It also ignored the invalid date users entered.

Try putting 2004-99-04, an invalid date in this.

Date validation should be done at the front end and only valid data passed to sql.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12221298
Not sure--that's why I posted the code above with ISDate--it does work....
0
 

Author Comment

by:mei_liu
ID: 12221564
I know for fact that our data were entered wrong for some records. When I tested xabimond's code, it worked.

0
 
LVL 6

Expert Comment

by:mcp111
ID: 12221593
mei liu:

don't you have validation in data entry to prevent wrong dates being entered? That should be your first step.

If you put 2004-99-04 in xabimond's code,it returns 99/10/2004
which is certainly not a valid date!
0
 

Author Comment

by:mei_liu
ID: 12221864
Yes, I agree with you that 99/04/2004 is not a valid date; however, it is a "valid" in a sense that it matches what the end user entered.

When I tested your date "2004-99-01", it returned to me as "99/04/2004".
0
 
LVL 34

Expert Comment

by:arbert
ID: 12222102
"Yes, I agree with you that 99/04/2004 is not a valid date; however, it is a "valid" in a sense that it matches what the end user entered."

Doesn't really sound valid then does it....GIGO
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 51
mssql 7 32
Begin Transaction 12 25
Email SQL Applications not able send to 4 or more recipients 2 32
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question