Can any one tell me?

Question

Can any one tell me?

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
February 26, 2009 at 4:10 am

#155335

how can we convert date(2009-02-26) format to this format Twenty-Second May Nineteen Nineteen Eighty Two?
is it possible in 2000/2005?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Write a function to do it. There are methods of converting numbers to text from 0 up to one million and all numbers in between, but you're working with quite small sets of numbers so hard-coding the lot probably won't cost much in terms of performance. Here's something to start you off:

DECLARE @Mydate DATETIME

SELECT @Mydate = GETDATE()

SELECT

CASE DATEPART(dd, @Mydate)

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 3 THEN 'Third'

WHEN 4 THEN 'Fourth'

WHEN 25 THEN 'Twenty Fifth'

WHEN 26 THEN 'Twenty Sixth'

WHEN 27 THEN 'Twenty Seventh'

ELSE 'xx' END + ' ' +

CASE DATEPART(mm, @Mydate)

WHEN 1 THEN 'January'

WHEN 2 THEN 'February'

WHEN 3 THEN 'March'

ELSE 'xx' END + ' ' +

CASE DATEPART(YY, @Mydate)

WHEN 1982 THEN 'Nineteen Eighty Two'

WHEN 2008 THEN 'Two Thousand and Eight'

WHEN 2009 THEN 'Two Thousand and Nine'

ELSE 'xx' END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

saravanantvr1984 (2/26/2009)
how can we convert date(2009-02-26) format to this format Twenty-Second May Nineteen Nineteen Eighty Two?
is it possible in 2000/2005?

I can understand maybe doing this on numbers for a check writer, but it's normally done in the app. What are you going to use spelled out dates on?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Heh... must be a one way street... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 4

Saravanan_tvr

SSCarpal Tunnel

Points: 4558

February 27, 2009 at 4:20 am

#951053

My hearty thanks to Chris Morris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

saravanantvr1984 (2/27/2009)
My hearty thanks to Chris Morris

That's nice - you're welcome!

I have to agree with Jeff however, this type of processing is almost always better done by the application/presentation layer - it is presentation, after all.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

I'd still like to know why we're spelling out dates. What are the business rules that would require such a thing?

Also, just a friendly tip... you don't need a CASE statement for the month...

SELECT DATENAME(mm,GETDATE())

... saves a clock cycle here and there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Jeff Moden (2/27/2009)
I'd still like to know why we're spelling out dates. What are the business rules that would require such a thing?
Also, just a friendly tip... you don't need a CASE statement for the month...
SELECT DATENAME(mm,GETDATE())
... saves a clock cycle here and there.

Thanks for the reminder Jeff...pity SELECT DATENAME(yy,GETDATE()) doesn't work!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

saravanantvr1984 (2/27/2009)
My hearty thanks to Chris Morris

You got help here... help us, please.... what is the business rule that you've been made to suffer that requires all parts of a date to be spelled out?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

saravanantvr1984 (2/26/2009)
how can we convert date(2009-02-26) format to this format Twenty-Second May Nineteen Nineteen Eighty Two?
is it possible in 2000/2005?

Heh... two way street here... why do you need to do this? It's a simple question. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)