Convert date to English given in any language.

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

General SQL Server Forums

New to SQL Server Programming

Convert date to English given in any language.

Author

Topic

Jayesh
Starting Member

7 Posts

Posted - 2010-01-10 : 04:32:10

Hi Friends,
I am trying to write a stored procedure which converts date given in any language to Us_English(like maart in Dutch language to march in English).I want to do this without replace function..Can any one help me in doing this.

I have tried in this format...

GO
/****** Object: StoredProcedure [dbo].[CheckDate]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CheckDate] (@locales as varchar(5000), @tempDate as varchar(200), @validDate as varchar(200) OUTPUT)
AS
Declare @tDate as DateTime
-- Declare @End Int
-- Declare @Len int
Declare @oldLang as varchar(50)
Declare @tmpLang as varchar(50)

BEGIN

select @oldLang =@@language
if isDate(@tempDate) =1
begin
select @validDate= right('0'+ LTrim(Rtrim(str(Day(@tempDate)))),2) + '/' + right('0' + LTrim(Rtrim(str(Month(@tempDate)))),2) + '/' + LTrim(Rtrim(str(Year(@tempDate))))
--print @validDate
end
while (Charindex(',',@locales)>0)

BEGIN
Set @tmpLang = ltrim(rtrim(Substring(@locales,1,Charindex(',',@locales)-1)))
Set @locales = Substring(@locales,Charindex(',',@locales)+len(','),len(@locales))
set language @tmpLang

select @validDate = isDate(@tempDate)
if (@validDate=1 )
BEGIN
select @validDate= right('0'+ LTrim(Rtrim(str(Day(@tempDate)))),2) + '/' + right('0' + LTrim(Rtrim(str(Month(@tempDate)))),2) + '/' + LTrim(Rtrim(str(Year(@tempDate))))
set Language @oldLang
break
END
else
continue
END
--While End

Select @tmpLang = ltrim(rtrim(@locales))
set language @tmpLang
select @validDate = isDate(@tempDate)
if (@validDate=1 )
BEGIN
select @validDate= right('0'+ LTrim(Rtrim(str(Day(@tempDate)))),2) + '/' + right('0'+LTrim(Rtrim(str(Month(@tempDate)))),2) + '/' + LTrim(Rtrim(str(Year(@tempDate))))
set Language @oldLang
Return
END
else
BEGIN
select @validDate=@tempDate
set Language @oldLang
END
END

/*
declare @validDate as varchar(200)
exec CheckDate 'English,German ,French,Japanese,Danish,Danish,Italian,Dutch,Norwegian,Portuguese,Finnish,Swedish,Czech,Hungarian,Polish,Romanian,Croatian,Slovak,Greek,Bulgarian,Russian,Turkish','30 mars 2010',@validDate OUTPUT
select @validDate
*/

Jayesh

Jayesh
Starting Member

7 Posts

Posted - 2010-02-03 : 07:53:06

Any way guys, I have made that...I will paste my code for you, hope it will help you anytime...And if someone can find out my mistake from this, please tell me, I will be happy to see that...

USE [store_proc]
GO
/****** Object: StoredProcedure [dbo].[sp_langdetect_checkdate] Script Date: 02/03/2010 18:21:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-- =============================================
AUTHOR : <Jayesh Upadhyay>
CREATE DATE : 20100120
-- ==============================================
*/
ALTER procedure [dbo].[sp_langdetect_checkdate](@PassDate varchar(100), @ReturnDate varchar(100) OUTPUT)
AS
BEGIN
DECLARE @alias varchar(20)
Declare @oldLang as varchar(50)
DECLARE @i integer
Declare @tempDate as varchar(50)
set @tempDate=@PassDate
select @oldLang =@@language
select langid as langno,alias as alias,lcid as lcid into #tbldateconvert from sys.syslanguages
set @i=0
while(@i is not null)
Begin
select @alias=alias from #tbldateconvert where langno=@i
set language @alias
If(isDate(@tempDate)=1)
BEGIN
set @tempDate=right('0'+ LTrim(Rtrim(str(Day(@tempDate)))),2) + '/' + right('0' + LTrim(Rtrim(str(Month(@tempDate)))),2) + '/' + LTrim(Rtrim(str(Year(@tempDate))))
set @ReturnDate=@tempDate
break
END
set @i=@i+1
End
set language @oldLang
drop table #tbldateconvert
END

/*declare @sss varchar(50)
exec [dbo].[sp_langdetect_checkdate] '12 maart 2010',@sss OUTPUT
select @sss*/

Jayesh

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-03 : 08:26:20

If you do your dates in CCYYMMDD format then this is unnecessary

Jayesh
Starting Member

7 Posts

Posted - 2010-02-03 : 10:32:47

Yaa, right...Thanks, I will find its solution too...

Jayesh

Jayesh
Starting Member

7 Posts

Posted - 2010-02-15 : 00:43:19

quote:
Originally posted by Jayesh

Yaa, right...Thanks, I will find its solution too...

Jayesh

SO you can use this, you can pass the result of previous stored procedure to this and get date in any format you want..
GO
/****** Object: StoredProcedure [dbo].[datetimeformats_test] Script Date: 02/15/2010 11:11:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Jayesh Upadhyay>
-- Create date: <20100204>
-- Description: <to get date and time in different formats>
-- =============================================
--You can give @input values from among 1 to 14,20 to 25,100 to 114,120,121,126,130,131
/*You can see different format by passing below values to @input.
1 = 01/21/10, 2 = 10.01.21, 3 = 21/01/10, 4 = 21.01.10, 5 = 21-01-10, 6 = 21 Jan 10, 7 = Jan 21, 10,
8 = 15:37:33, 9 = Jan 21 2010 3:37:33:483PM, 10 = 01-21-10, 11 = 10/01/21,
12 = 100121, 13 = 21 Jan 2010 15:37:33:483, 14 = 15:37:33:483,
20 = 2010-01-21 15:37:33, 21 = 2010-01-21 15:37:33.483, 22 = 01/21/10 3:37:33 PM, 23 = 2010-01-21,
24 = 15:37:33, 25 = 2010-01-21 15:37:33.483,
100 = Jan 21 2010 3:37PM, 101 = 01/21/2010, 102 = 2010.01.21 ,103 = 21/01/2010, 104 = 21.01.2010,
105 = 21-01-2010, 106 = 21 Jan 2010, 107 = Jan 21, 2010,
108 = 15:37:33, 109 = Jan 21 2010 3:37:33:483PM, 110 = 01-21-2010, 111 = 2010/01/21, 112 = 20100121,
113 = 21 Jan 2010 15:37:33:483, 114 = 15:37:33:483,
120 = 2010-01-21 15:37:33, 121 =2010-01-21 15:37:33.483 , 126 =2010-01-21T15:37:33.483 ,
130 = 6 ??? 1431 3:37:33:483PM, 131 = 6/02/1431 3:37:33:483PM,*/
ALTER Procedure [dbo].[datetimeformats_test](@inputDate varchar(30),@input int)
As
Begin
SET NOCOUNT ON
DECLARE @date DATETIME
DECLARE @extract varchar(30)

SET @date = @inputDate
--SELECT @date

SET @extract=CONVERT(VARCHAR,@date,@input)
SELECT @extract

END
--exec datetimeformats_test '12 dec 2010',111
--select convert(varchar(10), getdate(), 20)

Jayesh

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-15 : 01:12:53

all you've done is put a wrapper around the convert function. if the caller doesn't know what @input to pass in, then you still have the same problem.

as said, demand that dates are in universal format (ccyymmdd) and you're problem is solved.

are u importing values from multiple international clients?

and why are you accepting dates as varchar?

why are you declaring @extract which isn't used?

in fact, the more i look at it, i see this accomplishes nothing. you've declared a bunch of variables, but still need to use the native convert function without adding anything useful to it....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources