Converting Existing Data into Unicode Format?

Hi Everybody,
We have an application which accepts transactions in local regional languages. The SQL Database is in 7.0 and OS is NT, with all latest SPs. We are planning to move it SQL 2000. During our testing we found, that Crystal Reports 9.0 is not able extract data which is in Korean/Chinese Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to Unicode data format. For New data, we can change the datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting -- Locale ID =3D 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description ---
---
--
Character Set =3D 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order =3D 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Characters, in Order ---
---
--
! " # $ % & ' ( ) * + , - . / : ; < =3D > ? @. [ \ ] ^ _ ` { | }
~ =A1 =A2 =A3 =A4 =A5 =A6 =A7 =A8 =A9 =AA =AB =AC =AD =AE =AF =B0 = =B1 =B2 =B3 =B4 =B5 =B6 =B7 =B8 =B9 =BA =BB =BC =BD =BE
=BF =D7 =F7 0 1 2 3 4 5 6 7 8 9 A=3Da =C0=3D=E0 =C1=3D=E1 =C2=3D=E2 = =C3=3D=E3 =C4=3D=E4 =C5=3D=E5 =C6=3D=E6 B=3Db C
=3Dc =C7=3D=E7 D=3Dd E=3De =C8=3D=E8 =C9=3D=E9 =CA=3D=EA =CB=3D=EB = F=3Df G=3Dg H=3Dh I=3Di =CC=3D=EC =CD=3D=ED =CE=3D=EE =CF=3D=EF J
=3Dj K=3Dk L=3Dl M=3Dm N=3Dn =D1=3D=F1 O=3Do =D2=3D=F2 =D3=3D=F3 = =D4=3D=F4 =D5=3D=F5 =D6=3D=F6 =D8=3D=F8 P=3Dp Q=3Dq R=3Dr S
=3Ds =DF T=3Dt U=3Du =D9=3D=F9 =DA=3D=FA =DB=3D=FB =DC=3D=FC V=3Dv = W=3Dw X=3Dx Y=3Dy =DD=3D=FD =FF Z=3Dz =D0=3D=F0 =DE=3D=FE
Can anybody help me, how to convert existing data (Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasumVasum,
Is this the same issue that is posted with the subject "UNICODE" in the
microsoft.public.sqlserver.programming group? If so, see the replies
there.
Thanks,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Sender: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Subject: Converting Existing Data into Unicode Format?
Date: Sat, 5 Jul 2003 21:10:21 -0700
Lines: 64
Message-ID: <034501c34374$848f5880$a501280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Newsreader: Microsoft CDO for Windows 2000
Thread-Index: AcNDdISPYMUUigRbRNmnrEVJZrvb6Q==X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa09.phx.gbl
Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23000
NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi Everybody,
We have an application which accepts transactions in local
regional languages. The SQL Database is in 7.0 and OS is
NT, with all latest SPs. We are planning to move it SQL
2000. During our testing we found, that Crystal Reports
9.0 is not able extract data which is in Korean/Chinese
Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to
Unicode data format. For New data, we can change the
datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting
--
Locale ID = 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description
---
---
--
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit
character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with
several We
stern-European languages including English, French,
and German
. Uses the ISO 8859-1 character set.
Characters, in Order
---
---
--
! " # $ % & ' ( ) * + , - . / : ; < = > ? @. [ \ ] ^ _
` { | }
~ ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º »
¼ ½ ¾
¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å
Æ=æ B=b C
=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í
Î=î Ï=ï J
=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø P=p
Q=q R=r S
=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z
Ð=ð Þ=þ
Can anybody help me, how to convert existing data
(Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasum

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

|||I have the same question as KoryS. Anyone have an answer?

Thanks.

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

Convert GMT to Local Timezone

I've got the following to convert my seconds field to date/hours, but
now I'd like to convert the NewDate to local time.
Select DATEADD(ss, LastOccurTime, '19700101') as NewDate,
LastOccurTime
Found this on the newsgroup, but I need to go the other way:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),
GETDATE())
Suggestions/help are greatly appreciated!
Thanks,
Hank> Found this on the newsgroup, but I need to go the other way:
> SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),
> GETDATE())
How about just swapping GETDATE() and GETUTCDATE() inside DATEDIFF()? This
makes the DATEADD perform the inverse operation.
SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), GETDATE())
An example of exactly what you expect to see would be helpful.

Convert GMT to Local Time

Hi,
I have the following query which works fine, but now I want to convert
the date/time to local time (-7 GMT).
Select DateAdd(ss, LastOccurTime, '19700101') as EventDate

>From Event
Order By EventDate DESC
The LastOccurTime field is in seconds in the database so I have to
conver that first.
Thanks!<hammerin.hankster@.gmail.com> wrote in message
news:1144788163.801906.131970@.i40g2000cwc.googlegroups.com...
> Hi,
> I have the following query which works fine, but now I want to convert
> the date/time to local time (-7 GMT).
> Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
>
> Order By EventDate DESC
> The LastOccurTime field is in seconds in the database so I have to
> conver that first.
> Thanks!
>
Assuming all values represent the same time zone you can easily subtract a
fixed number of hours by adjusting the base date for the calculation:
SELECT DateAdd(ss, LastOccurTime, '1969-12-31T17:00:00.000') as EventDate
This won't account for Daylight Saving Time (if any). To do that you would
need to know what time difference was in effect for each date in your table.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If 2005, here is a way to convert from UTC to any local time or between
local times, etc
http://channel9.msdn.com/ShowPost.a...D=142586#142586
William Stacey [MVP]
<hammerin.hankster@.gmail.com> wrote in message
news:1144788163.801906.131970@.i40g2000cwc.googlegroups.com...
| Hi,
|
| I have the following query which works fine, but now I want to convert
| the date/time to local time (-7 GMT).
|
| Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
|
| >From Event
|
| Order By EventDate DESC
|
| The LastOccurTime field is in seconds in the database so I have to
| conver that first.
|
| Thanks!
||||Thanks for response. Worked great!

Convert GMT to Local Time

Hi,
I have the following query which works fine, but now I want to convert
the date/time to local time (-7 GMT).
Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
>From Event
Order By EventDate DESC
The LastOccurTime field is in seconds in the database so I have to
conver that first.
Thanks!<hammerin.hankster@.gmail.com> wrote in message
news:1144788163.801906.131970@.i40g2000cwc.googlegroups.com...
> Hi,
> I have the following query which works fine, but now I want to convert
> the date/time to local time (-7 GMT).
> Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
>>From Event
> Order By EventDate DESC
> The LastOccurTime field is in seconds in the database so I have to
> conver that first.
> Thanks!
>
Assuming all values represent the same time zone you can easily subtract a
fixed number of hours by adjusting the base date for the calculation:
SELECT DateAdd(ss, LastOccurTime, '1969-12-31T17:00:00.000') as EventDate
This won't account for Daylight Saving Time (if any). To do that you would
need to know what time difference was in effect for each date in your table.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If 2005, here is a way to convert from UTC to any local time or between
local times, etc
http://channel9.msdn.com/ShowPost.aspx?PostID=142586#142586
--
William Stacey [MVP]
<hammerin.hankster@.gmail.com> wrote in message
news:1144788163.801906.131970@.i40g2000cwc.googlegroups.com...
| Hi,
|
| I have the following query which works fine, but now I want to convert
| the date/time to local time (-7 GMT).
|
| Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
|
| >From Event
|
| Order By EventDate DESC
|
| The LastOccurTime field is in seconds in the database so I have to
| conver that first.
|
| Thanks!
||||Thanks for response. Worked great!