March 30, 2009 at 4:03 am
I want to convert my datetime value to Month date(th), YYYY Format
eg. if my date is 1/7/2008 i want July 1st, 2008
but i want 'st' as a suffix.
Any Idea how to do it. I'm using SQL Server 2005.
Thanks,
Kavita
March 30, 2009 at 4:27 am
use convert option 107 and do some manuplation for suffixed (st,th etc)
select convert(char(20),getdate(),107)
March 30, 2009 at 4:37 am
That much I've done but i don't know how to add suffix.
March 30, 2009 at 4:38 am
Check this ...
select Replace(convert(char(20),GETDATE(),107),',','st,').
You will have to do some manipulation towards setting "st" part. Ex:- if the data is 5 then it should be "th" (5th) and so on.
Keep in mind that this converts the date to character data type.
"Keep Trying"
March 30, 2009 at 4:43 am
This should be done in the application because it is a presentation issue. You can do it from within SQL Server, but the fact that it can be done doesn’t mean that it should be done from SQL Server. Having said that – here is one way of doing it (but again I recommend that it will be done in the application):
select replace(convert(char(20),getdate(),107),',', 'st, ')
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 30, 2009 at 6:30 am
What i actually need is this -> January 19th, 2008
like a superscript. Actually i'm writting data to a Word 2007 .docx template so all the work has to be done in view.
Can this be achieved?
March 30, 2009 at 6:31 am
What i actually need is this -> January 19th, 2008
like a superscript. Actually i'm writting data to a Word 2007 .docx template so all the work has to be done in view.
Can this be achieved?
March 30, 2009 at 7:45 am
No, it can’t be done. This should be done in the presentation layer and not in the data layer (just like you can’t decide on font and color in the database).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 30, 2009 at 1:22 pm
Sure you can write a huge CASE WHEN block in a view or procedure/function which translates the days to your format but guideline says do any formatting in your client application.
You wrote that you want to add the information to a word 2007 template so I assume you are using VBA or something like that. You should consider to handle the formatting here.
Greets
Flo
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply