Day counts in words - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Day counts in words

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-07 : 09:04:37

Dear All,

I have Days count in integer values,

I would like to convert into words, How can i do this..?
is sql server has any function to do like this

For Eg:
100 Days: --> Hundred Days

Thanks and regards
Krishnakuamr.C

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-07 : 09:25:38

http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWords.htm

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-07 : 09:35:40

Thank U JoeNak

If It's Possible to find the day diff. and print in words like

12.09.2005 - 31.07.2005 Output is tenmonths and 19 days ..?
Thanks and reagrds
Krishnakumar.C

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-07 : 12:08:58

If you use Reports like Crystal Reports, make use of ToWords function there

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-09 : 00:01:29

No Madhi, We are Using Our Company report writter, There is no option to convert like that

Krishna

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-09 : 22:54:20

For the datediff, you can do something like this (and your second date should be '31.07.2006', not '31.07.2005'):

declare @date1 datetime, @date2 datetime, @datediff datetime

set @date1 = '2005-09-12'
set @date2 = '2006-07-31'

SET @datediff = DATEADD(month, -1, DATEADD(day, DATEDIFF(day, @date1, @date2), 0))

SELECT CONVERT(varchar, MONTH(@datediff)) + ' months and ' + CONVERT(varchar, DAY(@datediff)) + ' days'

And then you can use JoeNak's function to convert the numbers to words.

youngoz
Starting Member

6 Posts

Posted - 2006-09-11 : 09:40:05

What happpens is the DATEDiff is over a year? I've tried your Query and if the DateDiff is over a year an error occurs and it says

Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(1 row(s) affected)

WHY?

Thanks in Advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-11 : 10:06:11

It is not the interval between dates that is the problem. Sounds like you are storing your dates as something other than datetime (or smalldatetime) which is almost always a bad idea.

If you need to convert '31.07.2005' to a datetime you probably need to first issue a set command prior to the conversion:
set dateformat dmy


select convert(datetime, '31.07.2005')

output:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


set dateformat dmy
select convert(datetime, '31.07.2005')

output:
2005-07-31 00:00:00.000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources