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

converting diagram to word

i want to convert my diagram that I have created in SQL
server 2000 to word an editable format e.g. jpg.
(not using print screen)
thanks to anyone who can helpYou can use a third-party product like Paint Shop Pro to capture the
image, edit it, and save it as a jpg.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 6 Nov 2003 05:19:27 -0800, "koby"
<anonymous@.discussions.microsoft.com> wrote:
>i want to convert my diagram that I have created in SQL
>server 2000 to word an editable format e.g. jpg.
>(not using print screen)
>thanks to anyone who can help|||I believe that screen capture utilities like SnagIt capture to jpg. =Perhaps that method would work for you.
-- Keith
"koby" <anonymous@.discussions.microsoft.com> wrote in message =news:044401c3a468$9a9f48b0$a001280a@.phx.gbl...
> i want to convert my diagram that I have created in SQL > server 2000 to word an editable format e.g. jpg.
> > (not using print screen)
> > thanks to anyone who can help

converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

Converting DateTime to SqlDateTime format

Hi,

I have a function that generates a range of DateTimes, which I then cast to SqlDateTime to compare with SqlDateTime values in a database.

The problem is my converted DateTimes come out in this type of format "6/2/2006 12:00:00 AM"

wheras my SqlDateTimes in the database are in this format "2006-01-18T00:00:00.0000000-12:00"

Any ideas how I can convert the DateTime values to SqlDateTime correctly so that I can compare them? As I said I tried creating a new SqlDateTime object with the DateTime value ie

DateTime dt = new DateTime("");

SqlDateTime sdt = new SqlDateTime(dt);

But that doesn't work correctly, its still not in the format that is in the database.

Assuming you are using the datetime datatype, the format that it is in the database is not"2006-01-18T00:00:00.0000000-12:00". From Books Online, the database actually stores datetime values as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So, the"2006-01-18T00:00:00.0000000-12:00" is just an output representation of that value.

If you give us a little more information about what you are trying to do we should be better able to help you.

converting datetime int

I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.

select iorg_name as org, ref_num as [ticketnum], c_first_name as
[firstname], c_last_name as [lastname], sym as type, [description] as
summary, status, dateadd(s,open_date,'12/31/1969 08:00:00pm') as
opened, dateadd(s,last_mod_dt,'12/31/1969 08:00:00pm') as irt,
dateadd(s,close_date,'12/31/1969 08:00:00pm') as closed from
AHD.dbo.HDreports reportview WHERE reportview.open_date >= 1080882000
AND reportview.open_date <= 1081227599.

The result shows correctly with those records that are in daylight
saving time. Those records in standard time show 1 hour behind.

Does anyone know how to make this query correctly display the data in
properly?js (androidsun@.yahoo.com) writes:
> I have tables with columns that stores datetime data in int format on
> SQL server 2000. For example, the datetime for '4/5/2004
> 00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
> 1081180799. I need to generate reports that display datetime columns
> in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
> my query statment.
>...
> The result shows correctly with those records that are in daylight
> saving time. Those records in standard time show 1 hour behind.
> Does anyone know how to make this query correctly display the data in
> properly?

That was a very odd way of storing dates, and probably not the best one.
Apparently this is some variation of Unix, where time is counted as number
of seconds since 1970-01-01 00:00:00, except that here the staring point
is 1969-12-30 20:00:00.

SQL Server is not timezone aware, so you should not expect to be able
to get fully accurate results. You are probably best of getting the
integer value to the client, and try the Windows functions for date
and time. They are likely to work out better.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting datetime in a string

Hi there,

Can anyone help?

I am trying to convert datetime into a string in the following format


2003-10-09 13:23:15.967



Current syntax:



SELECT '"' + CAST(t.Stamp AS varchar(30)) + '"' AS 'Start'

Any one any ideas?

THANKYOU!!!!

Melanie :)Try this:

select convert(varchar,getdate(),121)sqlsql

converting datetime for comparison

I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?
declare @.Company varchar(50)
declare @.SerialNumber varchar(50)
declare @.ProductGroup varchar(10)
declare @.InstallationDate datetime
set @.Company = ''
set @.SerialNumber = ''
set @.ProductGroup = '9'
set @.InstallationDate = '21/03/2003 00:00:00'
select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@.InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @.Company + '%'
and p.SerialNumber like '%' + @.SerialNumber + '%'
and t.ProductTypeID like '%' + @.ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@.InstallationDate, 11), 103)
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" wrote ...

> Can somebody tell me what I am doing wrong in my code?
A guess only...but..

> declare @.InstallationDate datetime
^^ a datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'

> convert(char(11), left(@.InstallationDate, 11), 103)
char(11)
Perhaps the different data types is the problem?
Something else you could try which might help you achieve the same result
would be using the DATEDIFF function, if you are trying to find matches for
the same day, just use DATEDIFF, specify days, and look for where it equals
0 (ie, no days difference)..
Hope this helps..
Rob|||I have some best practices for datetime here: http://www.karaszi.com/SQLServer/in...
o_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike P" <mike.parr@.gmail.com> wrote in message news:e3zsKC2nGHA.3348@.TK2MSFTNGP03.phx.gbl.
.
>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
> declare @.Company varchar(50)
> declare @.SerialNumber varchar(50)
> declare @.ProductGroup varchar(10)
> declare @.InstallationDate datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
> p.InstallationDate,
> t.ProductTypeID, t.[Description], convert(datetime,
> left(p.InstallationDate, 11), 103),
> convert(datetime, left(@.InstallationDate, 11), 103)
> from Products p
> inner join companysite s
> on p.OwnedByCompanyID = s.CompanySiteKey
> inner join Competitors c
> on c.CompetitorID = p.ProducedByID
> inner join CompetitorMachines m
> on p.MachineID = m.CompetitorMachineID
> inner join ProductTypes t
> on t.ProductTypeID = m.ProductTypeID
> where s.[name] like '%' + @.Company + '%'
> and p.SerialNumber like '%' + @.SerialNumber + '%'
> and t.ProductTypeID like '%' + @.ProductGroup + '%'
> and convert(char(11), left(p.InstallationDate, 11), 103) =
> convert(char(11), left(@.InstallationDate, 11), 103)
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
Well, what is the problem? Do you get an error message, or is it not
returning any rows? Could you give us DDL, sample data, and desired results
as per http://www.aspfaq.com/5006 ?

Converting DateTime

Hi,
When I open a tabe in the SQL enterprise manager I see the Timestamp Field
in this Format :
24/01/2005 16:45:00

However when I'm using the Query analyzer or other SQL Client I see the
Timestamp Field in this Format :
2005-01-24 16:44:59.997

Does Anybody know what to do in order to display the Timestamp Field in
first Format (24/01/2005 16:45:00) ?

Please Advise,
Yariv

--
Message posted via http://www.sqlmonster.comLook up "Date Time String Transformation" in SQL Server BOL

"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com|||"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com

Check out CONVERT() in Books Online. But you need to remember that MSSQL
stores datetime values in an internal format, and each client (including EM
and QA) decides how to display them. See here for more details:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Yariv via SQLMonster.com (forum@.SQLMonster.com) writes:
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?

Under Tool->Options->Connections, check the third checkbox, "Use
regional settings...".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting Dates as paramters

Hey guys,

Hoping i can get some help with this one..

Problem:

Data source has a datetime format as YYYYMMDD

I would like to have my users enter a date in the format of DD/MM/YYYY and in the background have it convert to the YYYYMMDD so i can filter my data according to the data

ANy ideas on how i will do this?

thanks

scotty

Use Format function and convert the date parameter to required format.

Format(Parameters!prmStartDate.Value, "yyyy-MM-dd")

converting date format on for xml query

im trying to convert date format with this with no sucess
http://myserver/?sql=select%20*%20,CONVERT(datetime,DATA,3)
as%20[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%
20AUTO&root=root
it order by date, but dont convert the date format...any
help ?
What is the date formay you want to get? Note that all datetime values will
mapped to an ISO format. If you want your own format, CONVERT to a string in
your select statement.
Michael
"max" <anonymous@.discussions.microsoft.com> wrote in message
news:7df801c43160$6ca153b0$a001280a@.phx.gbl...
> im trying to convert date format with this with no sucess
> http://myserver/?sql=select%20*%20,CONVERT(datetime,DATA,3)
> as%20[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%
> 20AUTO&root=root
> it order by date, but dont convert the date format...any
> help ?
|||hi
thanks
im trying to get the datetime field that on my sql is on
this format
3/5/2004 14:30:18
but on xml it shows me this
2004-05-03T14:30:18
and i want it to return me
DD/MM/YY HH:MM:SS
how could it be done
max

>--Original Message--
>What is the date formay you want to get? Note that all
datetime values will
>mapped to an ISO format. If you want your own format,
CONVERT to a string in
>your select statement.
>Michael
>"max" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7df801c43160$6ca153b0$a001280a@.phx.gbl...
sucess[vbcol=seagreen]
(datetime,DATA,3)
>
>.
>
|||its impossible or something ?
thanks
max
[vbcol=seagreen]
>--Original Message--
>hi
>thanks
>im trying to get the datetime field that on my sql is on
>this format
>3/5/2004 14:30:18
>but on xml it shows me this
>2004-05-03T14:30:18
>and i want it to return me
>DD/MM/YY HH:MM:SS
>how could it be done
>
>max
>
>datetime values will
>CONVERT to a string in
>message
>sucess
>(datetime,DATA,3)
format...any
>.
>
|||Here you are:
create table t (d datetime)
go
insert into t values ('3/5/2004 14:30:18')
go
select CONVERT(nvarchar(50), d, 101)+' '+CONVERT(nvarchar(50), d, 108) as d
from t for xml auto
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:7ada01c43164$b6cf3520$a401280a@.phx.gbl...[vbcol=seagreen]
> hi
> thanks
> im trying to get the datetime field that on my sql is on
> this format
> 3/5/2004 14:30:18
> but on xml it shows me this
> 2004-05-03T14:30:18
> and i want it to return me
> DD/MM/YY HH:MM:SS
> how could it be done
>
> max
>
> datetime values will
> CONVERT to a string in
> message
> sucess
> (datetime,DATA,3)
sqlsql

Converting Date

I am unable to convert following date format in seconds (ss). Plz provide me query for the same.
Date Available :
2007-03-27 09:55:00.000select convert(datetime, '2007-03-27 09:55:00.000')

??|||select convert(datetime, '2007-03-27 09:55:00.000')
Yes, and the seconds are extracted by:
select datepart(ss, convert(datetime, '2007-03-27 09:55:00.000'))|||even the convet part of

select datepart(ss, convert(datetime, '2007-03-27 09:55:00.000'))

can be avoided and used as

select datepart(ss,'2007-03-27 09:55:00.000')|||but select datepart(ss,'2007-03-27 09:55:00.000') yields 0

i think perhaps milind wanted the answer to be 3383974800, which is that datetime converted in seconds in SQL Server

note that in unix timestamp format, the value would be 1175003700|||Perhaps, yes. Then the query he asked would be:

SELECT 3383974800|||no, not really, because you don't get that answer without doing a conversion

for example, what is the answer for '2006-09-09 09:37'?|||'2006-09-09 09:37' is not one of the available dates listed in the OP. ;)

Seriously, I think we need Milind to clarify what s/he wants.|||'2006-09-09 09:37' is not one of the available dates listed in the OP. ;)

good one :cool:

Converting Crystal Code

I'm evaluating Reporting Services and currently converting a Crystal Report
to this format. However I've ran in to a problem and it is to do with a
function written in Crystal syntax but I cannot seem to be able to convert
this logic Heres the functio
----
StringVar name = "";
If NOT IsNull({Query.FIELD1_Company}) Then
name := {Query.FIELD1_Company};
If NOT IsNull({Query.FIELD2_COMPANY}) Then
name := {Query.FIELD2_COMPANY};
If NOT IsNull({Query.FIELD3_COMPANY}) Then
name := {Query.FIELD3_COMPANY};
If NOT IsNull({Query.FIELD4_COMPANY}) Then
name := {Query.FIELD4_COMPANY};
If NOT IsNull({Query.FIELD5_COMPANY}) Then
name := {Query.FIELD5_COMPANY};
If NOT IsNull({Query.FIELD6_COMPANY}) Then
name := {Query.FIELD6_COMPANY};
If NOT IsNull({Query.FIELD7_COMPANY}) Then
name := {Query.FIELD7_COMPANY};
If NOT IsNull({Query.FIELD8_COMPANY}) Then
name := {Query.FIELD8_COMPANY};
name
----
For a start, 'Null' cannot be used, neither can System.DBNull etc only
Nothing.
StringVar is a variable declaration in Crystal. What this all does is
prevents empty fields being returned (in Column 1) and the report format is
something similar to this
Company, Number of..., Export To..., etc
SomeCompany, 1, 1, etc
NULL, 0, 3, etc
So the code prevents record number 2 from appearing in the report and
returns the company name from one of the other columns (as code above).
NOTE All columns actually return the Company name but we are simply doing a
DISTINCTCOUNT on all the other columns(Query.FIELD2_COMPANY etc), so Column 1
will always have a company name (if the code works, as in Crystal).
Any ideas on this? and thanks in advanceIs TSql It is
ISNULL(Filed1, ISNULL(Field2, ISNULL(Field3, ISNULL(Field4,
ISNULL(Field5,'')))))
What this does is if Field1 is null it moves to Field2. A lot less
complicated.
"slk55guy" wrote:
> I'm evaluating Reporting Services and currently converting a Crystal Report
> to this format. However I've ran in to a problem and it is to do with a
> function written in Crystal syntax but I cannot seem to be able to convert
> this logic Heres the function
> ----
> StringVar name = "";
> If NOT IsNull({Query.FIELD1_Company}) Then
> name := {Query.FIELD1_Company};
> If NOT IsNull({Query.FIELD2_COMPANY}) Then
> name := {Query.FIELD2_COMPANY};
> If NOT IsNull({Query.FIELD3_COMPANY}) Then
> name := {Query.FIELD3_COMPANY};
> If NOT IsNull({Query.FIELD4_COMPANY}) Then
> name := {Query.FIELD4_COMPANY};
> If NOT IsNull({Query.FIELD5_COMPANY}) Then
> name := {Query.FIELD5_COMPANY};
> If NOT IsNull({Query.FIELD6_COMPANY}) Then
> name := {Query.FIELD6_COMPANY};
> If NOT IsNull({Query.FIELD7_COMPANY}) Then
> name := {Query.FIELD7_COMPANY};
> If NOT IsNull({Query.FIELD8_COMPANY}) Then
> name := {Query.FIELD8_COMPANY};
> name;
> ----
> For a start, 'Null' cannot be used, neither can System.DBNull etc only
> Nothing.
> StringVar is a variable declaration in Crystal. What this all does is
> prevents empty fields being returned (in Column 1) and the report format is
> something similar to this
> Company, Number of..., Export To..., etc
> SomeCompany, 1, 1, etc
> NULL, 0, 3, etc
> So the code prevents record number 2 from appearing in the report and
> returns the company name from one of the other columns (as code above).
> NOTE All columns actually return the Company name but we are simply doing a
> DISTINCTCOUNT on all the other columns(Query.FIELD2_COMPANY etc), so Column 1
> will always have a company name (if the code works, as in Crystal).
> Any ideas on this? and thanks in advance
>

Converting Access Code - FORMAT

Hello,
A colleague passed me this make-table query which works in Access, and we'd
like to automate in SQL. I did some basic debugging but now on parse, SQL
returns that Trim and Format are not recognized function nameS. I think you
can see the what formatting is doing to provide a standard string length.
Could someone advise what I need to do to get this to work in SQL? Any help
would be much appreciated. Thanks, Pancho
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) &
Trim(GVMOI2.TraceNbr) AS TraceNbrEdt, IIf(IsNumeric(GVMOI2.CreditAmtCash),
Left(Format([CreditAmtCash],"0000000000.00"),10) &
Right(Format([CreditAmtCash],"0000000000.00"),2),
"000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2!DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) &
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10) &
Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
CreditAmtChecksFmt,
IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10) &
Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
DebitAmtChecksFmt,
GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
GVMOI2.CheckReferenceNbr,
GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
GVMOI2.ThirdParty,
"0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
GVMOI2.IDNumber,
GVMOI2.IDIssueBy, GVMOI2.IDOthers
INTO MOI_Prep
FROM GVMOI2;Follow the below guidelines to convert the query.
1. & ==> +
2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
else <false> end
3. <table>!<column> ==> <table>.<column>
4. trim(<value> ) ==> rtrim(ltrim(<value> ))
-oj
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
> Hello,
> A colleague passed me this make-table query which works in Access, and
> we'd
> like to automate in SQL. I did some basic debugging but now on parse, SQL
> returns that Trim and Format are not recognized function nameS. I think
> you
> can see the what formatting is doing to provide a standard string length.
> Could someone advise what I need to do to get this to work in SQL? Any
> help
> would be much appreciated. Thanks, Pancho
> SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
> GVMOI2.TaxIDType,
> GVMOI2.ApplicationCode, GVMOI2.AccountNo,
> Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) &
> Trim(GVMOI2.TraceNbr) AS TraceNbrEdt, IIf(IsNumeric(GVMOI2.CreditAmtCash),
> Left(Format([CreditAmtCash],"0000000000.00"),10) &
> Right(Format([CreditAmtCash],"0000000000.00"),2),
> "000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2!DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) &
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10)
> &
> Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
> CreditAmtChecksFmt,
> IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10)
> &
> Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
> DebitAmtChecksFmt,
> GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
> GVMOI2.CheckReferenceNbr,
> GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
> GVMOI2.ThirdParty,
> "0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
> GVMOI2.IDNumber,
> GVMOI2.IDIssueBy, GVMOI2.IDOthers
> INTO MOI_Prep
> FROM GVMOI2;
>
>|||OJ,
Thank you for these clear instructions. I will try these tomorrow. Looks
good!
Best Wishes,
P
"oj" wrote:

> Follow the below guidelines to convert the query.
> 1. & ==> +
> 2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
> else <false> end
> 3. <table>!<column> ==> <table>.<column>
> 4. trim(<value> ) ==> rtrim(ltrim(<value> ))
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
>
>|||Hi oj,
I replaced & with +, ! with . on table.column names, and
rtrim(ltrim(<value> ) parses OK now.
Pls take a look at the revised script. I don't quite follow how to change
the iif to case when. I am getting an incorrect syntax error near THEN.
Please let me know what I'm doing wrong on the first CASE line and I'll fix
the rest the same way. Thanks! P
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
CASE WHEN (IsNumeric(GVMOI2.CreditAmtCash)) THEN
Left(Format([CreditAmtCash],"0000000000.00"),10) +
Right(Format([CreditAmtCash],"0000000000.00"),2), ELSE FALSE
"000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2.DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) +
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10) +
Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
CreditAmtChecksFmt,
IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10) +
Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
DebitAmtChecksFmt,
GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
GVMOI2.CheckReferenceNbr,
GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
GVMOI2.ThirdParty,
"0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
GVMOI2.IDNumber,
GVMOI2.IDIssueBy, GVMOI2.IDOthers
INTO MOI_Prep
FROM GVMOI2;
"oj" wrote:

> Follow the below guidelines to convert the query.
> 1. & ==> +
> 2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
> else <false> end
> 3. <table>!<column> ==> <table>.<column>
> 4. trim(<value> ) ==> rtrim(ltrim(<value> ))
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
>
>|||There is no format() in sqlserver. You'd want to use convert() instead.
IIf(IsNumeric(GVMOI2.DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) +
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
==>
case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
else replicate('0',10) AS DebitAmtCashFmt,
-oj
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
> Hi oj,
> I replaced & with +, ! with . on table.column names, and
> rtrim(ltrim(<value> ) parses OK now.
> Pls take a look at the revised script. I don't quite follow how to change
> the iif to case when. I am getting an incorrect syntax error near THEN.
> Please let me know what I'm doing wrong on the first CASE line and I'll
> fix
> the rest the same way. Thanks! P
> SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
> GVMOI2.TaxIDType,
> GVMOI2.ApplicationCode, GVMOI2.AccountNo,
> Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
> RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
> CASE WHEN (IsNumeric(GVMOI2.CreditAmtCash)) THEN
> Left(Format([CreditAmtCash],"0000000000.00"),10) +
> Right(Format([CreditAmtCash],"0000000000.00"),2), ELSE FALSE
> "000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10)
> +
> Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
> CreditAmtChecksFmt,
> IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10)
> +
> Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
> DebitAmtChecksFmt,
> GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
> GVMOI2.CheckReferenceNbr,
> GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
> GVMOI2.ThirdParty,
> "0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
> GVMOI2.IDNumber,
> GVMOI2.IDIssueBy, GVMOI2.IDOthers
> INTO MOI_Prep
> FROM GVMOI2;
>
> "oj" wrote:
>|||Thanks again oj. I have marked both of your posts as helpful and will try
this code.
Best Regards,
p
"oj" wrote:

> There is no format() in sqlserver. You'd want to use convert() instead.
> IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> ==>
> case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
> right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
> else replicate('0',10) AS DebitAmtCashFmt,
>
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
>
>|||oj,
This is getting closer but now I get an incorrect syntax near the word AS on
the last line of the section below:
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
CASE WHEN IsNumeric(GVMOI2.CreditAmtCash)=1 THEN
Right(Convert(varchar,convert(money,1000
000000000+GVMOI2.CreditAmtCash)),13)
ELSE
Replicate('0',13) AS CashCRFmt,
It looks like they want to name the column CreditAmtCash when there is a
numeric value and name it CashCRFmt when the value is non-numeric. Let me
know if I am using Replicate correctly.
Thanks,
p
"oj" wrote:

> There is no format() in sqlserver. You'd want to use convert() instead.
> IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> ==>
> case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
> right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
> else replicate('0',10) AS DebitAmtCashFmt,
>
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
>
>

converting a text field to number

I have a table with over a million rows and one of the fields contains
amounts of money in text format.
What is the most efficient way of converting this field to a number
format that I can sum on?

Regards,
CiarnHi Ciaran,

Before converting the type of the col, make sure that there are no
invalid values in that column. You can pull them out by

SELECT colName FROM tabName WHERE IsNumeric(colName) = 0

Alter your column type by

ALTER TABLE tableName ALTER COLUMN colName NUMERIC

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Nazeer Oasis (nazeerpp@.indiatimes.com) writes:
> Before converting the type of the col, make sure that there are no
> invalid values in that column. You can pull them out by
> SELECT colName FROM tabName WHERE IsNumeric(colName) = 0
> Alter your column type by
> ALTER TABLE tableName ALTER COLUMN colName NUMERIC

Unfortunately, this may still fail, since IsNumeric will approve of
values than converts to float or money, but not to numeric. Also, I
say that it's extremely bad practice to say numeric without specifying
scale and precision. You get some defaults, but these may not be what
you expect.

As for the original query, the easy way is:

SELECT SUM(convert(int, textcol)) FROM tbl
or SELECT SUM(convert(money, textcol)) FROM tbl

But this will of course fail if there are strings that does not convert.

If all data is integer, that is the text is undelimited and there are
no decimals, then it's pretty easy to test:

textcol NOT LIKE '%[0-9]%'

If the text can delimiters and decimals, it can become quite hairy
to filter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> If all data is integer, that is the text is undelimited and there are
> no decimals, then it's pretty easy to test:
> textcol NOT LIKE '%[0-9]%'

This is wrong. I forgot a ^:

textcol NOT LIKE '%[^0-9]%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting a parent-child table into a genrational table / text file

I need to convert a parent - child table into another table or a text file containing in a generational format.

eg.

child / parent / grand parent / great grand parent / ....

Does anyone have a stored procedure of code to do this?

I'm working with a dimension having 250,000 + members, writing code is fast enough for much smaller hierarchies but with a dimension this size we need something fast.

Thank you

Hi Rod,

Not sure if this is the same problem as you posted in TSQL under "Adjacency List' - as suggested there, recursive CTE would be one approach in SQL Server 2005:

>>

with GenTable(LeafKey, LeafName, GenNum, AncestorKey, AncestorName) as

(select do.OrganizationKey as LeafKey, do.OrganizationName as LeafName,

1 as GenNum, do1.OrganizationKey as AncestorKey,

do1.OrganizationName as AncestorName

from dbo.DimOrganization do

join dbo.DimOrganization do1

on do.ParentOrganizationKey = do1.OrganizationKey

where not exists(select *

from dbo.DimOrganization do2

where do2.ParentOrganizationKey = do.OrganizationKey)

union all

select gt.LeafKey, gt.LeafName,

gt.GenNum + 1 as GenNum, do.ParentOrganizationKey as AncestorKey,

do1.OrganizationName as AncestorName

from GenTable gt

join dbo.DimOrganization do

on gt.AncestorKey = do.OrganizationKey

join dbo.DimOrganization do1

on do.ParentOrganizationKey = do1.OrganizationKey)

select LeafKey, [1] as Gen1Key, [2] as Gen2Key, [3] as Gen3Key

from (select LeafKey, GenNum, AncestorKey

from GenTable) gt

Pivot (Max(AncestorKey)

for GenNum in ([1], [2], [3])) as pt

order by LeafKey

--

3 14 2 1
4 14 2 1
5 14 2 1
6 14 2 1
7 14 2 1
8 2 1 NULL
11 9 1 NULL
12 9 1 NULL
13 10 1 NULL

>>

|||Hi Deepak,

Thank you very much, it's been as long day and I'll look closer in the morning. It looks vey helpful. I should of mentioned that I have to do the with 2000 and 2005 but this likes I'm half way there and on the right track

Thanks again,

Rod

converting a number into a date type using an expression

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!

Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.

Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.

I'm just getting started with SQL 2005 so I don't know how to do this using an expression.

Any help would be greatly appreciated.

Thanks,

Gray

I think your approach sounds valid. You're going to have to parse it with substring and build it into a date format. Then you can cast the string to a date/time field.

(DT_DBTIMESTAMP)(substring([YourColumn + 19000000],x,y) + "/" + .......)

|||

Hi Phil,

Thanks for the info ... it really helped ... here's my final expression ...

(DT_DBTIMESTAMP)(SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),5,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),7,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),1,4))

Thanks again,

Gray

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

Converting a file into multiple tables

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)

It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.

But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.

--
Wes Groleau

If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:K7x6h.10720$l%2.2462@.trnddc05...

Quote:

Originally Posted by

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)
>
It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.
>
But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.
>
--
Wes Groleau
>
If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?|||You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:PCH6h.708$Fg.683@.tornado.socal.rr.com...

Quote:

Originally Posted by

Dan Guzman wrote:
>

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

|||Dan Guzman wrote:

Quote:

Originally Posted by

You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

Of course, I could easily write something to
convert it to XML that SQL server can read.
But then I could just as easily convert it
directly into INSERT statements. I'm just
wondering whether DTS or anything else is faster.

I already have a tool that loads the entire
file into an array of lines and provides various
query functions for other apps to access it.

But I'd like to put multiple files in the database
instead of having to select one file at a time.

By the way, whatever the technique is, it could
probably also handle GEDCOM files.

--
Wes Groleau

There ain't no right wing,
there ain't no left wing.
There's only you and me and we just disagree.
(apologies to Jim Krueger)|||Ed Murphy wrote:

Quote:

Originally Posted by

Dan Guzman wrote:

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

I think I figured out a solution (haven't tried it yet).

Comments on this idea welcome (I'm kind of new to SQL):

The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1

So if a file has (data elem delims changed to spaces)
....
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
....
then the first pass through the file could create rows

.... A B C X Y Z ...
.... A B C 1 2 3 ...
.... D E F P Q R ...
.... D E F 5 6 7 ...

Next, one query could SELECT DISTINCT to give

.... A B C
.... D E F

while another could SELECT for

.... A X Y Z ...
.... A 1 2 3 ...
.... D P Q R ...
.... D 5 6 7 ...

and the same strategy could be used on each adjacent pair of levels.

Right ?

--
Wes Groleau

He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin|||It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you can
leverage a high-performance XML import utility like SQLXML without writing
additional code. Although it will take a while, I expect XML will
eventually replace both EDI and GEDCOM formats. You'll be a step ahead if
you can process XML too.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:uJv7h.211$9e.25@.trnddc02...

Quote:

Originally Posted by

Ed Murphy wrote:

Quote:

Originally Posted by

>Dan Guzman wrote:

Quote:

Originally Posted by

>>Take a look at SQLXML Bulk Load
>>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>>
>He'd also need an EDI to XML translator. (I recognize those
>damnable start/end tags.) Google indicates that several
>translators exist; anyone want to offer a recommendation?

>
I think I figured out a solution (haven't tried it yet).
>
Comments on this idea welcome (I'm kind of new to SQL):
>
The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1
>
So if a file has (data elem delims changed to spaces)
...
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
...
then the first pass through the file could create rows
>
... A B C X Y Z ...
... A B C 1 2 3 ...
... D E F P Q R ...
... D E F 5 6 7 ...
>
Next, one query could SELECT DISTINCT to give
>
... A B C
... D E F
>
while another could SELECT for
>
... A X Y Z ...
... A 1 2 3 ...
... D P Q R ...
... D 5 6 7 ...
>
and the same strategy could be used on each adjacent pair of levels.
>
Right ?
>
--
Wes Groleau
>
He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin

|||Dan Guzman wrote:

Quote:

Originally Posted by

It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you
can leverage a high-performance XML import utility like SQLXML without
writing additional code. Although it will take a while, I expect XML
will eventually replace both EDI and GEDCOM formats. You'll be a step
ahead if you can process XML too.

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?

I got the impression when I was reading about it that one
XML file makes one table and vice versa.

--
Wes Groleau
----

"Thinking I'm dumb gives people something to
feel smug about. Why should I disillusion them?"
-- Charles Wallace
(in _A_Wrinkle_In_Time_)|||Wes Groleau wrote:

Quote:

Originally Posted by

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?
>
I got the impression when I was reading about it that one
XML file makes one table and vice versa.

The impression was wrong. I studied the MS KB article cited
earlier, and I can easily make such XML files. Only, the process
of transforming the file into XML is similar to the process used
by bulk load to turn the XML into records. So I suspect it would
add a little speed if I went directly to records.

--
Wes Groleau

Words of the Wild Wes(t) = http://ideas.lang-learn.us/WWWsqlsql

Converting a date with a derived column

I'm having trouble converting a date in the format of:

Jan 11 2006 12:00:00:000AM

to a smalldatetime in my new SSIS package. I'm trying the derived column transformation, but I'm at a wall now, especially with the conversion of 'Jan' string to integer/month/value of 1. Anyone have experience/advice on this transformation?

What happened to the DateTime String transformation in SQL 2000? Was that too unpopular to move to integration services? That transformation saved my butt many times - every banks' data feed we import uses a different date format.

Thanks in advance for any advice/help!

-Erik

Have you attempted to chop the seconds and milliseconds off the date? And then stuff it into a smalldatetime column.|||

Seems like it does not like those 000 at the end.

The only way I see at this point - to cat the 000 out of it and them cast it with DT_DBDATE or something

|||I can use SUBSTRING to skip the time at the end, but what about converting Jan to the value 1?|||After you do SUBSTRING, use type casting to cast to needed date format. If this is not enough, you can use YEAR,MONTH abd DAY after you converted to ANY date format

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select '01/'+convert(varchar(2), month(OrderDate))+'/06' from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECT DATEADD(mm, DATEDIFF(mm,0,orderdate), 0) as firstdayofOrdersDates , count(*)as ordercount from orders

group by DATEADD(mm, DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!