yet another SQL question.. how to pull of yy/mm/dd as mm/dd/yy?

Factual Questions
1

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.

2

The SQL92 standard appears to have the TO_CHAR function. It should work something like



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.

3

I ran this through MS SQL Query Analyzer. Works.



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.

4

That should probably be



SELECT TO_CHAR(date_column,'DD/MM/YY') FROM table_name;
5

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.

6

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.

7

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 for lots of questions and answers abotu SQL Server. They are really great over there.