Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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

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
mei_liu
Asked:
mei_liu
  • 8
  • 5
  • 4
  • +1
1 Solution
 
xabimondCommented:
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
 
arbertCommented:
What?  format 101 will give you slashes:

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

returns:  10/04/2004
0
 
arbertCommented:
Convert(varchar(10),getdate(),110) will return dashes:

10-04-2004
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mei_liuAuthor Commented:
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
 
mcp111Commented:
select convert(varchar(20),convert(datetime, '2004-10-04', 101),101)
0
 
arbertCommented:
Select case isdate(yourdatecolumn)
   when 1 then convert(varchar(10),yourdate,101)
   else NULL end YourNewDate
0
 
arbertCommented:
mcp111, select convert(varchar(20),convert(datetime, '2004-99-04', 101),101) returns a conversion error....
0
 
xabimondCommented:
Format 101 will give you dashes but yyyy/mm/dd is not a recognised SQL Server date format hence the substrings.
0
 
mei_liuAuthor Commented:
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
 
mcp111Commented:
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
 
arbertCommented:
"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
 
mcp111Commented:
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
 
arbertCommented:
but it doesn't account for invalid dates...
0
 
mcp111Commented:
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
 
arbertCommented:
Not sure--that's why I posted the code above with ISDate--it does work....
0
 
mei_liuAuthor Commented:
I know for fact that our data were entered wrong for some records. When I tested xabimond's code, it worked.

0
 
mcp111Commented:
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
 
mei_liuAuthor Commented:
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
 
arbertCommented:
"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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now