Solved

SQL Convert style values

Posted on 2004-11-01
1,525 Views
Last Modified: 2008-02-07
I am trying to convert from "July 30, 2004" to "JULY 30, 2004".  I have the code for the short date i.e. JUL 30, 2004 but I need the month spelled out.  This date field is being built in a work table inside a stored procedure then it is passed into Crystal Reports v8.0.  I would like to do all the date formating inside the stored procedure so I don't run into Crystal issues down the road when I do my upgrade project.

Here is what I am doing so far:
UPPER(convert(varchar,convert(datetime,#rptrows.print_date),107))

This Outputs: JUL 30, 2004 I want JULY 30, 2004

Is there an undocumented style number that will do this or do I need to perform some crazy formating or write a case statement?

Any help would be appreicated.

msas94-
0
Question by:msas1994
    3 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    Maybe this?

    UPPER(DATENAME(month, #rptrows.print_date)) + ' ' + DAY(#rptrows.print_date) + ', ' + YEAR(#rptrows.print_date)
    0
     
    LVL 3

    Expert Comment

    by:sanjaykp
    Your field seems to be in string format since you're converting it to datetime and then back to a varchar. You're better off leaving it like a date field and passing it on to Crystal Reports as a datetime value (don't use smalldatetime, problems), since Crystal has excellent customizable field displays (Format Field...). Another advantage being, the field can be used natively as a datetime by any application.  
    0
     
    LVL 8

    Accepted Solution

    by:
    try this...

    UPPER(DATENAME(month, getdate())) + ' ' + right('00'+Convert(varchar(2),DAY(getdate())),2) + ', ' + Convert(varchar(4),YEAR(getdate()))

    right('00'  - function add zero infront for day between 1-9
    Convert(varchar(2) - function will convert day digit into varchar to avoid miss match
    Convert(varchar(4) - function will convert year digits into varchar to avoid miss match

    Select UPPER(DATENAME(month, getdate())) + ' ' + right('00'+Convert(varchar(2),DAY(getdate())),2) + ', ' + Convert(varchar(4),YEAR(getdate()))

    result: NOVEMBER 21, 2004
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    845 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now