SQL Server Script to get Date Format

[radcaesar]

Hi all,
I Want a sql script which gets the DateFormat of the SQL Server (Whether its dmy or mdy etc). The Script should have to run in both sqlserver 2000 and 2005.

Solutions pls.

[iburyak]

I am not sure if I understand your question.... :(

[pkv7005]

when u got script then send me...<Email removed>

[javeed123]

Hi iburyak,

I want your help in writing function

I want to write a function which converts number to word

for example : 1200 to twelve hundred

Please mail me at <Removed>

byeee

[iburyak]

Try this:
1. Create Function
[PHP]
Create Function fnWriteNumber (@Number varchar(255))
Returns varchar(1000)
AS
BEGIN
declare @LookupNumber table(
ID int Primary Key,
Description varchar(255))

insert into @LookupNumber values (1,'One')
insert into @LookupNumber values (2,'Two')
insert into @LookupNumber values (3,'Three')
insert into @LookupNumber values (4,'Four')
insert into @LookupNumber values (5,'Five')
insert into @LookupNumber values (6,'Six')
insert into @LookupNumber values (7,'Seven')
insert into @LookupNumber values (8,'Eight')
insert into @LookupNumber values (9,'Nine')

insert into @LookupNumber values (10,'Ten')
insert into @LookupNumber values (11,'Eleven')
insert into @LookupNumber values (12,'Twelve')
insert into @LookupNumber values (13,'Thirteen')
insert into @LookupNumber values (14,'Fourteen')
insert into @LookupNumber values (15,'Fifteen')
insert into @LookupNumber values (16,'Sixteen')
insert into @LookupNumber values (17,'Seventeen')
insert into @LookupNumber values (18,'Eighteen')
insert into @LookupNumber values (19,'Nineteen')

insert into @LookupNumber values (20,'Twenty')
insert into @LookupNumber values (30,'Thirty')
insert into @LookupNumber values (40,'Forty')
insert into @LookupNumber values (50,'Fifty')
insert into @LookupNumber values (60,'Sixty')
insert into @LookupNumber values (70,'Seventy')
insert into @LookupNumber values (80,'Eighty')
insert into @LookupNumber values (90,'Ninety')

Declare @Ret varchar(1000)
IF Len(@Number) = 1
Return (Select Description From @LookupNumber Where ID = @Number)
ELSE IF Len(@Number) = 2
Select @Ret = Isnull((Select Description From @LookupNumber Where ID = Right(@Number, 2)),
(Select Description From @LookupNumber Where ID = Left(Right(@Number, 2),1) + '0') + ' ' +
(Select Description From @LookupNumber Where ID = Right(@Number, 1))
)
ELSE IF Len(@Number) = 3
Select @Ret = (Select Description From @LookupNumber Where ID = Left(@Number,1)) + ' Hunderd ' +
Isnull((Select Description From @LookupNumber Where ID = Right(@Number, 2)),
(Select Description From @LookupNumber Where ID = Left(Right(@Number, 2),1) + '0') + ' ' +
(Select Description From @LookupNumber Where ID = Right(@Number, 1))
)
ELSE IF Len(@Number) = 4
Select @Ret = (Select Description From @LookupNumber Where ID = Left(@Number,1)) + ' Thousand ' +
(Select Description From @LookupNumber Where ID = Left(@Number,1)) + ' Hunderd ' +
Isnull((Select Description From @LookupNumber Where ID = Right(@Number, 2)),
(Select Description From @LookupNumber Where ID = Left(Right(@Number, 2),1) + '0') + ' ' +
(Select Description From @LookupNumber Where ID = Right(@Number, 1))
)

RETURN @Ret
END

go[/PHP]

2. Call Function
[PHP]select dbo.fnWriteNumber(1206)[/PHP]

Good Luck.

[HelenT]

Hello,
Firstly - thank you for the function - it was exactly what I needed!

I did spot a few issues with it though that I have tweaked and thought you might like to know:

1) Lines 48 and 65 - Hundred spelt Hunderd
2) Line 41
Single digits don't return correctly - had to replace Return here with Set @Ret =
The final Return @Ret (line 61) seems to be overriding it
3) Line 55
Thousand update repeats the 'thousand' digit twice e.g. 1250 becomes One Thousand One Hundred Fifty
I've replaced the second digit check with Substring(@Number,2,1)
4) Lines 48 and 65 I added an 'and' after the Hundred because it 'reads nicer'
e.g. Three Thousand Six Hundred and Fifty Two

[ck9663]

Hello,
Firstly - thank you for the function - it was exactly what I needed!

I did spot a few issues with it though that I have tweaked and thought you might like to know:

1) Lines 48 and 65 - Hundred spelt Hunderd
2) Line 41
Single digits don't return correctly - had to replace Return here with Set @Ret =
The final Return @Ret (line 61) seems to be overriding it
3) Line 55
Thousand update repeats the 'thousand' digit twice e.g. 1250 becomes One Thousand One Hundred Fifty
I've replaced the second digit check with Substring(@Number,2,1)
4) Lines 48 and 65 I added an 'and' after the Hundred because it 'reads nicer'
e.g. Three Thousand Six Hundred and Fifty Two

kindly post the final version....

thanks

[srinit]

kindly post the final version....

thanks

HI ,
I have the same requirement.kindly post the final verson

[srinit]

kindly post the final version....

thanks

HI ,
I have the same requirement.kindly post the final verson

[atfigues]

Hi all,
I Want a sql script which gets the DateFormat of the SQL Server (Whether its dmy or mdy etc). The Script should have to run in both sqlserver 2000 and 2005.

Solutions pls.

HI, i was seeing the blog and notice that you have the same problem that I have. By nay chance you managed to solve it? you already know how to detect the date format of SQL?

[KevinFairchild]

This is for "ck9663", "srinit", and any of the other folks who couldn't be bothered troubleshooting things on their own.... despite "HelenT" explaining ways to get around the original code's issues...

Create Function fnWriteNumber (@Number varchar(255)) 
Returns varchar(1000) 
AS
BEGIN
declare @LookupNumber table (ID int Primary Key, Description varchar(255))
insert into @LookupNumber values (1,'One')
insert into @LookupNumber values (2,'Two')
insert into @LookupNumber values (3,'Three')
insert into @LookupNumber values (4,'Four')
insert into @LookupNumber values (5,'Five')
insert into @LookupNumber values (6,'Six')
insert into @LookupNumber values (7,'Seven')
insert into @LookupNumber values (8,'Eight')
insert into @LookupNumber values (9,'Nine')
insert into @LookupNumber values (10,'Ten')
insert into @LookupNumber values (11,'Eleven')
insert into @LookupNumber values (12,'Twelve')
insert into @LookupNumber values (13,'Thirteen')
insert into @LookupNumber values (14,'Fourteen')
insert into @LookupNumber values (15,'Fifteen')
insert into @LookupNumber values (16,'Sixteen')
insert into @LookupNumber values (17,'Seventeen')
insert into @LookupNumber values (18,'Eighteen')
insert into @LookupNumber values (19,'Nineteen')
insert into @LookupNumber values (20,'Twenty')
insert into @LookupNumber values (30,'Thirty')
insert into @LookupNumber values (40,'Forty')
insert into @LookupNumber values (50,'Fifty')
insert into @LookupNumber values (60,'Sixty')
insert into @LookupNumber values (70,'Seventy')
insert into @LookupNumber values (80,'Eighty')
insert into @LookupNumber values (90,'Ninety')
 
Declare @Ret varchar(1000)
 
IF @Number < 10
 
    Select @Ret = Description From @LookupNumber Where ID = @Number
 
ELSE IF @Number < 100
 
    Select @Ret = Isnull((Select Description From @LookupNumber Where ID = Right(@Number, 2)),
 
     (Select Description From @LookupNumber Where ID = Left(Right(@Number, 2),1) + '0') + ' ' +
 
     (Select Description From @LookupNumber Where ID = Right(@Number, 1))
 
     )
 
ELSE IF @Number BETWEEN 100 AND 999
 
    Select @Ret = (Select Description From @LookupNumber Where ID = Left(@Number,1)) + ' Hundred ' +
 
  Isnull((Select Description From @LookupNumber Where ID = Right(@Number, 2)),
 
   (Select Description From @LookupNumber Where ID = Left(Right(@Number, 2),1) + '0') + ' ' +
 
   (Select Description From @LookupNumber Where ID = Right(@Number, 1))
 
   )
 
ELSE IF @NUMBER BETWEEN 1000 AND 9999
 
  Select @Ret = (Select Description From @LookupNumber Where ID = Left(@Number,1)) + ' Thousand ' +
 
(Select Description From @LookupNumber Where ID = Right(Left(@Number,2),1)) + ' Hundred ' +
 
    Isnull((Select Description From @LookupNumber Where ID = Right(@Number, 2)),
 
     (Select Description From @LookupNumber Where ID = Left(Right(@Number, 2),1) + '0') + ' ' +
 
     (Select Description From @LookupNumber Where ID = Right(@Number, 1))
 
    )
 
 
RETURN @Ret 
 
END
 
go