CODEWITHSUNDEEP
sqlsql-serveroracle-databaset-sqlreporting-services
Raj More
Raj More

Reputation: 48048

Oracle style dates in SQL Server

We have an SSRS report on which we want to show a date field in the following format DD-MMM-YY like in Oracle. That converts dates to 25-Jan-11

So in the SQL that does the final select, we use Replace (CONVERT (VarChar, DateTimeColumn,106), ' ', '-')

Is there a better way of doing this conversion?

Upvotes: 3

Views: 299

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

Within SQL Server

SQL Server CONVERT: http://msdn.microsoft.com/en-us/library/ms187928.aspx

No, that specific format is not available so what you are doing is what there is. I normally prefer to size varchar columns instead of leaving it unsized.

I also notice you have 106, which is DD-MMM-YYYY not DD-MMM-YY. Did you mean to use 6?

REPLACE(CONVERT (varchar(9), DateTimeColumn, 6), ' ', '-')

Using SSRS formatting

=Format(Fields!DateTimeColumn.Value, “dd-MMM-yy”)

Upvotes: 2

Related Questions