# yet another SQL question.. how to pull of yy/mm/dd as mm/dd/yy? **URL:** \ **Category:** Factual Questions\ **Created:** [September 29, 2005, 6:58pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067 "2005-09-29T18:58:23Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [September 29, 2005, 6:58pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/1 "2005-09-29T18:58:23Z")
I am doing a query on a database where the date is stored yy/mm/dd. to save having to fart about in excel (where I drag and drop the query results) to fix the dates how do I make the query pull the dates off in the same way excel cells see them? (mm/dd/yy I think) Thanks. ---
**Author:** ![Erinaceus\_europaeus](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@Erinaceus\_europaeus](https://boards.straightdope.com/u/Erinaceus_europaeus)\ **Post date:** [September 29, 2005, 7:06pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/2 "2005-09-29T19:06:59Z")
The SQL92 standard appears to have the TO\_CHAR function. It should work something like ```auto SELECT TO_CHAR(date_column,'YY/Mon/DD') FROM table_name; ``` This assumes your date\_column is actually of a DATE type. Most databases also have other built-in functions to format dates. You should probably consult the manual. ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [September 29, 2005, 7:28pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/3 "2005-09-29T19:28:47Z")
I ran this through MS SQL Query Analyzer. Works. ```auto SELECT CAST(Datepart(yy, MyDateCol) AS nvarchar(10)) + '/' + CAST(DatePart(mm, MyDateCol) AS nvarchar(10)) + '/' + CAST(DatePart(d, MyDateCol) AS nvarchar(10)) AS MyDate FROM MyTable ``` The result is a string but I don’t see why Excel wouldn’t accept it. It’s just text to Excel. ---
**Author:** ![Erinaceus\_europaeus](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@Erinaceus\_europaeus](https://boards.straightdope.com/u/Erinaceus_europaeus)\ **Post date:** [September 29, 2005, 7:32pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/4 "2005-09-29T19:32:08Z")
> [@Erinaceus europaeus](#): > > ```auto > > SELECT TO_CHAR(date_column,'YY/Mon/DD') FROM table_name; > > ``` That should probably be ```auto SELECT TO_CHAR(date_column,'DD/MM/YY') FROM table_name; ``` ---
**Author:** ![minor7flat5](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/minor7flat5/32/258_2.png) [@minor7flat5](https://boards.straightdope.com/u/minor7flat5)\ **Post date:** [September 29, 2005, 8:16pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/5 "2005-09-29T20:16:53Z")
I’m not sure if your source column is CHAR or DATE. If it is a DATE column then it is not in any text format: it is a native date format that needs to be converted to text in the fashion described above in order to be displayed. If your date is actually in a text column, then you can go to date and back to character in a double function call like this: SELECT TO\_CHAR(TO\_DATE(MYDATE\_COLUMN, ‘YY/MM/DD’), ‘MM/DD/YY’) FROM MYTABLE I tried this on an Oracle database and it works peachy. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [October 4, 2005, 7:10pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/6 "2005-10-04T19:10:11Z")
None of the above worked or were acceptable commands in enterprise manager (query analyser) Some of them just pulled the day part as 2020 or 2021 for example. So I did the following substring(\_date,7,2)+’/’+substring(\_date,4,2)+’/’+substring(\_date,1,2) as newdate did the trick. ---
**Author:** ![crazyjoe](https://avatars.discourse-cdn.com/v4/letter/c/f14d63/32.png) [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)\ **Post date:** [October 4, 2005, 8:59pm UTC](https://boards.straightdope.com/t/yet-another-sql-question-how-to-pull-of-yy-mm-dd-as-mm-dd-yy/324067/7 "2005-10-04T20:59:44Z")
> [@Lobsang](#): > > None of the above worked or were acceptable commands in enterprise manager (query analyser) Some of them just pulled the day part as 2020 or 2021 for example. So I did the following > > substring(\_date,7,2)+’/’+substring(\_date,4,2)+’/’+substring(\_date,1,2) as newdate > > did the trick. I am assuming you have stored the dates in text format in your database and not in actual datetime format. If you store them as datetime, then it is simply a formatting issue when you select the records. If you couldn’t use the CAST function, then I have to assume that you stored them as text, otherwise the CAST function should work fine in Microsoft SQL Server. Storing the date as text puts a little extra overhead on the server in terms of space used as well as making formatting conversions more difficult at times like these. oh, and try [www.sqlteam.com](http://www.sqlteam.com) for lots of questions and answers abotu SQL Server. They are really great over there.