Showing posts with label formatting. Show all posts
Showing posts with label formatting. Show all posts
Saturday, February 13, 2016
Quickly change m/d/yyyy dates to mm/dd/yyyy dates in Excel
Sometimes you get data in an Excel or csv file and it will be in a m/d/yyyy format.
Here is an example
1/1/2005
2/1/2012
2/12/2012
12/5/2015
This will look fine if you open the csv file in Excel but if you need to import this data into a database you might get an error that the format is not correct.
What you want is the data to be in this format
01/01/2005
02/01/2012
02/12/2012
12/05/2015
If you use Format Cells and then pick Date you will see that there is no mm/dd/yyyy format What you have to do is pick the Custom Category and click on the m/d/yyyy format
Now change m/d/yyyy to mm/dd/yyyy
Click OK and you are all set, you will see that the dates are now in mm/dd/yyyy format
Let me just mention that the problem was not the conversion in SQL itself but a tool that did the import choked
BTW SQL Server can do this fairly easily
If I run the following
I will get this as output
2001年01月01日
2001年11月01日
2001年01月12日
2001年05月05日
2001年01月22日
2001年12月01日
2001年01月01日
That of course is just a date, if you want mm/dd/yyyy format, you can use convert and style 101...
And here is that output
01/01/2001
11/01/2001
01/12/2001
05/05/2001
01/22/2001
12/01/2001
01/01/2001
Here is an example
1/1/2005
2/1/2012
2/12/2012
12/5/2015
This will look fine if you open the csv file in Excel but if you need to import this data into a database you might get an error that the format is not correct.
What you want is the data to be in this format
01/01/2005
02/01/2012
02/12/2012
12/05/2015
If you use Format Cells and then pick Date you will see that there is no mm/dd/yyyy format What you have to do is pick the Custom Category and click on the m/d/yyyy format
Click OK and you are all set, you will see that the dates are now in mm/dd/yyyy format
Let me just mention that the problem was not the conversion in SQL itself but a tool that did the import choked
BTW SQL Server can do this fairly easily
If I run the following
1 2 3 4 5 6 7 8 9 10 11
;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(date,date) from dates
I will get this as output
2001年01月01日
2001年11月01日
2001年01月12日
2001年05月05日
2001年01月22日
2001年12月01日
2001年01月01日
That of course is just a date, if you want mm/dd/yyyy format, you can use convert and style 101...
1 2 3 4 5 6 7 8 9 10 11 12 13 14
;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(varchar(10),convert(date,date),101) from dates select convert(date,date) from dates
And here is that output
01/01/2001
11/01/2001
01/12/2001
05/05/2001
01/22/2001
12/01/2001
01/01/2001
Subscribe to:
Posts (Atom)