Converting date to YYYYMMDD

[TomR100]

Hello,
I am having trouble converting a date to YYYYMMDD. I have used the CONVERT logic but the cahnges does not seem to be taking place when I run the code in query analyzer.

Here is my code:
select TOP 5
[EMPLOYEE],
[INCID_STATUS],
[COST],
CONVERT(VARCHAR(8),R_DATE,112) AS R_DATE
from [LawITTest].[dbo].[tmpOSHA]

Here is the output:
EMPLOYEE INCID_STATUS COST R_DATE
----------- ------------ ------------- --------
17524 CLOSED 1500.00 1142003
15251 CLOSED .00 1232003
12306 CLOSED .00 2042003
2945 CLOSED .00 1282003
21709 CLOSED 1225.00 2192003

Can anyone see what I may be doing wrong?

Thank you,
TomR100

 

[JamesLean]

Can't see anything wrong with your SQL statement. Style 112 is correct for yyyymmdd format.

Are you sure the r_date column is datetime data type? Can you run the same query again but include the actual r_date column in the select list so we can compare the two.

--James

 

[rudejohn]

I tried working with the CONVERT function awhile back and posted a similar question in this forum:

thread183-600280 I gathered is that when you use the CONVERT function, SQL Server returns the date in its default format. I found that in my application AS WELL AS within Query Analyzer, it doesn't matter WHAT format code you choose (101,113, etc), it always returns the date in the same format.

The only way I found to fix this was to format the date on the business application layer (you can alter it using Visual Basic, Java, etc.) I used the FORMAT function in Visual Basic...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/refctFormat.asp

************
RudeJohn
************

 

[JamesLean]

rudejohn,
The reason your use of convert didn't work as you expected was because, as SQLBill posted, you were converting to datetime rather than to varchar.

If you convert to varchar using the style (as in the original query) you can get the output in the correct format.

--James

 

[TomR100]

Hello everyone,
I figured out my problem and got it to work.

I import data from an Excel spreadsheet. The date fields on the spreadsheet and in the temporary table I created were set to character so when I did a convert nothing was done. I changed the field format in the Excel spreadsheet to date and the date fields in the temporary table to datetime.

Now my CONVERT works.

Thank you all for your input.

TomR100