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 data question

I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.Your destination column should be a varchar column if the column from your source varchar is not an integer. You can post a set of sample data and expected result here to help answer your question.|||Source Columns Destination
Varchar Dec int
0660 10 66010
0660 2 66002
0660 3 66003
0660 11 66011|||

Kinny:

I put together a quick mock-up of what I think you are trying to do. Do either of the output columns look like what you are trying to do?

Dave

-- --
-- This mock-up works correctly for "correct values" as discribed in the request.
-- However, it does not know what to do with most "error conditions." If you don't
-- have to worry about "incorrect values" this select should work fine
--
-- Problem areas:
--
-- 1. What do you do with nulls?
-- 2. What do you do with 3-digit numbers?
-- 3. What do you do with negative numbers?
-- --
set nocount on
declare @.mockUp table
( sampleString varchar (20) not null,
sampleDecimal decimal (3,0) null
)

insert into @.mockUp values ('testView', null) -- ? Do you have to worry about null?
-- ? If so, display double-zero for nulls?
insert into @.mockUp values ('testView', 0) -- O0; OK
insert into @.mockUp values ('testView', 4) -- 04; OK
insert into @.mockUp values ('testView', 21) -- 21; OK
insert into @.mockUp values ('nextSample', 99) -- 99; OK
insert into @.mockUp values ('negative', -1) -- -1; Not sure; is this a problem?
insert into @.mockUp values ('negative', -99) -- 99; Wrong; is this a problem?
insert into @.mockUp values ('tooBig', 100) -- 00; Wrong; is this a problem?
--select * from @.mockUp

select sampleString,
sampleDecimal,
sampleString + isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00')
as [String First],
isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00') + sampleString
as [Decimal First]
from @.mockUp

-- --
-- Sample Output:
-- --

-- sampleString sampleDecimal String First Decimal First
-- -- - - -
-- testView NULL testView00 00testView
-- testView 0 testView00 00testView
-- testView 4 testView04 04testView
-- testView 21 testView21 21testView
-- nextSample 99 nextSample99 99nextSample
-- negative -1 negative-1 -1negative
-- negative -99 negative99 99negative
-- tooBig 100 tooBig00 00tooBig

|||

Hi,

Here is the update statement for question:

UPDATE yourTable

SET DesINTColumn= CAST(CAST(CONVERT(int, VarCharColumn1) AS varchar)+ RIGHT('0' + CAST(DecimalColumn1 AS varchar), 2) AS INT)

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
Chris
Hi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>

> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>

> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John

converting columns in an INSERT (was "SQL Query")

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...First of all, I'd strongly suggest that you list out the columns. That solves all kinds of problems before they get a chance to happen to you. If you are determined to do things the hard way, you don't have to enumerate the columns yourself, but I'd still recommend it.

You ought to be able to use any of those conversions if you like, but as long as the contents of the column can be converted to MONEY, the SQL Server engine ought to handle the conversion for you.

-PatP|||"SELECT *" is shorthand for "I'm a lazy programmer". I would never leave it in any finished code. Bad. Bad. Bad bad code.

Convert when multiplying smallints

Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
ThanksTry,
SELECT cast(Quantity as bigint) * UnitCost FROM Transactions
AMB
"hals_left" wrote:

> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>|||Which of those statements produce the error? And do you know what values are
producing the error? Small int can go upto 32,767
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
Thanks|||SELECT Cast(Quantity as Bigint)*Cast(UnitCost as Bigint) FROM Transactions
works...but best option?
Lee-Z
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

sqlsql

Convert varchar to date on a column

I have a table with a column defined as a nvarchar. The strings contained in the columns are in the form of YYYYMMDD. I searched the forums here and believe that if I have a date as a string then YYYYMMDD is the correct format for a date stored as string. However, I think I need to store the date as a Date type for selecting, sorting, searching, and indexing. What is the best method of converting the entire column to a Date type from a nvarchar type with the assumption that all the string dates are in the same YYYYMMDD format? When I tried to modify the table using the Managment Studio Express, I get a warning: "Data might be lost converting column 'Date' from 'nvarchar(50)'.

Thanks!

DeBug

Update: I just noticed that all the text fields have double quotes at the start and end of each text string so my so called YYYYMMDD is really "YYYYMMDD". So I guess the answer would need to include how to scrub the " with an UPDATE command. I used the DTS that came with Express to import the data from a flat file.

Moving to TSQL forum. There are many ways i can think of doing this, one of the easiest would be to create a new column, call it DateCol2 with datetime datatype, and then do an update statement on the table to set this column properly. it might involve some parsing, but if you make a mistake, you can always try it again since this is just a new column. Once you've updated it correctly, you can drop the original column and rename the new column appropriately.

Someone here can probably give you the proper update statement.

|||

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

|||

For starters, with datetime datatype you can do arithmatic operations using built-in functions (dateadd, datediff, datefirst, etc.), you can also reference it in multiple ways - "December 27, 2006" or "12/27/2006", or using convert(). You can read more about this datatype in Books Online topic "Using Date and Time Data", http://msdn2.microsoft.com/en-us/library/ms180878.aspx.

|||

Create Table #test(mydate varchar(25))

Alter Table #test

Alter column mydate datetime

drop table #test

Adamus

|||

Doug DeBug wrote:

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

Using the field as a datetime vs. varchar() is the logical thing to do. There's no advantage but only a disadvantage. You can't use operators as comparison and you remove the possibility of using BETWEEN.

Adamus

|||

You can do the convert using UPDATE statement and then do ALTER like:

update tbl

set your_col = replace(your_col, '"', '')

go

alter tbl alter column your_col smalldatetime

go

As for the benefits of using datetime/smalldatetime vs varchar type, the obvious ones are:

1. Appropriate type checking and domain enforcement

2. Better performance

3. Compatibility with other built-in date functions (although they accept strings it depends on the format)

4. Storage depending on how you store the date value in character format

5. Ordering semantics that follow the date or datetime rules

There are cases where you may want to store datetime values in more compact form than using datetime/smalldatetime but those need to be done with care & careful consideration.

|||

Thanks for the example. I will have not used T-SQL as much I should but have relied on the GUIs in the past. I will need a solution to update the customer's database at least once per week. Getting the things like quoted strings out of the way now is a plus.

Regards,

DeBug

Convert unique clustered indexes to PK constraints

Hi,
We have a database where no PK constraints are defined and only unique
clustered indexes. Is there a way for us to change all the columns that make
up the unique clustered index in all of the tables in the database to be the
columns that make up the PK constraint of the tables? In other words, we
want to change the clustered indexes to actual PK constraints.
Thanks,
DeeThere is no easy way. You have to drop the CI and then run the alter table
commands. You could use the system catalogs to help generate the commands
though.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:8366A973-76BE-4731-8960-E3D487D41AB8@.microsoft.com...
> Hi,
> We have a database where no PK constraints are defined and only unique
> clustered indexes. Is there a way for us to change all the columns that
> make
> up the unique clustered index in all of the tables in the database to be
> the
> columns that make up the PK constraint of the tables? In other words, we
> want to change the clustered indexes to actual PK constraints.
> Thanks,
> Dee

Convert unique clustered indexes to PK constraints

Hi,
We have a database where no PK constraints are defined and only unique
clustered indexes. Is there a way for us to change all the columns that make
up the unique clustered index in all of the tables in the database to be the
columns that make up the PK constraint of the tables? In other words, we
want to change the clustered indexes to actual PK constraints.
Thanks,
Dee
There is no easy way. You have to drop the CI and then run the alter table
commands. You could use the system catalogs to help generate the commands
though.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:8366A973-76BE-4731-8960-E3D487D41AB8@.microsoft.com...
> Hi,
> We have a database where no PK constraints are defined and only unique
> clustered indexes. Is there a way for us to change all the columns that
> make
> up the unique clustered index in all of the tables in the database to be
> the
> columns that make up the PK constraint of the tables? In other words, we
> want to change the clustered indexes to actual PK constraints.
> Thanks,
> Dee

Convert sql proc to a c# class?

Anyone have code to convert a sql proc to a C# class?

Specifially something to create the columns returned from the proc.

Thanks.I figured this out myself.

Just execute the proc in a sqldataadapter, fill a dataset, then loop thru the columns
while creating text output for a C# class.

Pretty simple.|||Are you referring to a code gen script? Can you elaborate on your request?

convert rows to columns

I hope I am able to explain you my situation
I have a table called levelAllocation. A snapshot of the table looks as
follows
LAID LVID ALID PERCENT
1 1 4 10
2 1 5 20
3 1 6 30
4 2 4 45
5 2 5 55
6 2 6 65
I want a stored procedure that would output following
LAID LVID ALID4 ALID5 ALID6 PERCENT
1 1 4 NULL NULL 10
2 1 NULL 5 NULL 20
3 1 NULL NULL 6 30
4 2 4 NULL NULL 45
5 2 NULL 5 NULL 55
6 2 NULL NULL 6 65
Any other suggestion will be welcomed.
Regards,
A
Try this:
(edit with your table name)
select t1.LAID
, t1.LVID
, t2.ALID4
, t2.ALID5
, t2.ALID6
, t1.[Percent]
from [table_name] t1
inner join (
select LAID
, (case ALID when 4 then ALID else NULL end) AS ALID4
, (case ALID when 5 then ALID else NULL end) AS ALID5
, (case ALID when 6 then ALID else NULL end) AS ALID6
from [table_name]
group by LAID, ALID
) t2
on t1.LAID = t2.laid
"Ashutosh" wrote:

> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
>
|||Yes , you can search for pivot tables, that is what i think you're
looking for.
Ashutosh wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
|||Check out RAC.
www.rac4sql.net

convert rows to columns

Hi All,

I have two tables

TblEmployee

EmpID,

EmpName

TblAddress

AddressID

EmpID

Address1

Address2

AddressType (will hold values like ‘TP’, ‘HP’ storing different address types)

If I do a simple join assuming 2 different address types exists for each employee. I would be getting two rows. But I need address1 and address2 of different address types as four columns I mean address1 of type ‘TP’, address2 of type ‘TP’, address1 of type ‘HP’, address2 of type ‘HP’

along with employee information.

Can some body help me with the query.select *
from TblEmployee e
inner join (
select EmpID,
TP_Address1 = max(case when AddressType = 'TP' then Address1 end),
TP_Address2 = max(case when AddressType = 'TP' then Address2 end),

HP_Address1 = max(case when AddressType = 'HP' then Address1 end),

HP_Address2 = max(case when AddressType = 'HP' then Address2 end)
from TblAddress
group by EmpID
) a
on e.EmpID = a.EmpID|||Thanks a lot K H Tan

convert rows to columns

I hope I am able to explain you my situation
I have a table called levelAllocation. A snapshot of the table looks as
follows
LAID LVID ALID PERCENT
1 1 4 10
2 1 5 20
3 1 6 30
4 2 4 45
5 2 5 55
6 2 6 65
I want a stored procedure that would output following
LAID LVID ALID4 ALID5 ALID6 PERCENT
1 1 4 NULL NULL 10
2 1 NULL 5 NULL 20
3 1 NULL NULL 6 30
4 2 4 NULL NULL 45
5 2 NULL 5 NULL 55
6 2 NULL NULL 6 65
Any other suggestion will be welcomed.
Regards,
ATry this:
(edit with your table name)
select t1.LAID
, t1.LVID
, t2.ALID4
, t2.ALID5
, t2.ALID6
, t1.[Percent]
from [table_name] t1
inner join (
select LAID
, (case ALID when 4 then ALID else NULL end) AS ALID4
, (case ALID when 5 then ALID else NULL end) AS ALID5
, (case ALID when 6 then ALID else NULL end) AS ALID6
from [table_name]
group by LAID, ALID
) t2
on t1.LAID = t2.laid
"Ashutosh" wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
>|||Yes , you can search for pivot tables, that is what i think you're
looking for.
Ashutosh wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A|||Check out RAC.
www.rac4sql.net

convert rows to columns

I hope I am able to explain you my situation
I have a table called levelAllocation. A snapshot of the table looks as
follows
LAID LVID ALID PERCENT
1 1 4 10
2 1 5 20
3 1 6 30
4 2 4 45
5 2 5 55
6 2 6 65
I want a stored procedure that would output following
LAID LVID ALID4 ALID5 ALID6 PERCENT
1 1 4 NULL NULL 10
2 1 NULL 5 NULL 20
3 1 NULL NULL 6 30
4 2 4 NULL NULL 45
5 2 NULL 5 NULL 55
6 2 NULL NULL 6 65
Any other suggestion will be welcomed.
Regards,
ATry this:
(edit with your table name)
select t1.LAID
, t1.LVID
, t2.ALID4
, t2.ALID5
, t2.ALID6
, t1.[Percent]
from [table_name] t1
inner join (
select LAID
, (case ALID when 4 then ALID else NULL end) AS ALID4
, (case ALID when 5 then ALID else NULL end) AS ALID5
, (case ALID when 6 then ALID else NULL end) AS ALID6
from [table_name]
group by LAID, ALID
) t2
on t1.LAID = t2.laid
"Ashutosh" wrote:

> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
>|||Yes , you can search for pivot tables, that is what i think you're
looking for.
Ashutosh wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A|||Check out RAC.
www.rac4sql.net

Convert Rows to Columns

Hi All,

I need to help with converting rows to columns in SQL2k.

Input:
Id Name Role

58Ron Doe Associate
58Mark BonasDoctor
59Mike JohnsonDoctor
59John SmithAssociate
102Chris CarterAssociate
102Ron Doe Associate
102James JonesAssociate

Output should look like:

IdDoctorAssoc1Assoc2Assoc3

58Mark BonasRon Doe NULLNULL
59Mike JohnsonJohn SmithNULLNULL
102NULLChris CarterRon Doe James Jones

There could be more than 3 associates in the input but I only need 3
above columns for associates.

I used following query:
SELECT Q.sales_id,
doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'
and Q2.sales_id = Q.sales_id),
assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id)
FROM view1 Q
GROUP BY sales_id

and I get this error "Subquery returned more than 1 value" since there
are multiple associate for Id 102.

Thenks<ambersaria420@.yahoo.com> wrote in message
news:1106185678.994241.297820@.c13g2000cwb.googlegr oups.com...
> Hi All,
> I need to help with converting rows to columns in SQL2k.
> Input:
> Id Name Role
> 58 Ron Doe Associate
> 58 Mark Bonas Doctor
> 59 Mike Johnson Doctor
> 59 John Smith Associate
> 102 Chris Carter Associate
> 102 Ron Doe Associate
> 102 James Jones Associate
>
> Output should look like:
> Id Doctor Assoc1 Assoc2 Assoc3
> 58 Mark Bonas Ron Doe NULL NULL
> 59 Mike Johnson John Smith NULL NULL
> 102 NULL Chris Carter Ron Doe James Jones
>
> There could be more than 3 associates in the input but I only need 3
> above columns for associates.
> I used following query:
> SELECT Q.sales_id,
> doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'
> and Q2.sales_id = Q.sales_id),
> assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role =
> 'associate' and Q2.sales_id = Q.sales_id),
> assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role =
> 'associate' and Q2.sales_id = Q.sales_id),
> assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role =
> 'associate' and Q2.sales_id = Q.sales_id)
> FROM view1 Q
> GROUP BY sales_id
> and I get this error "Subquery returned more than 1 value" since there
> are multiple associate for Id 102.
> Thenks

This is very awkward to write in TSQL, especially since the number of
associates may vary - you would need a cursor (maybe even nested cursors) to
loop through the table for each ID. A better solution is to do this in the
front end or using a reporting tool.

Simon|||I changed the query to following:

select distinct v1.sales_id, v1.name as doctor2 , v2.name as assoc1,
v3.name as assoc2, v4.name as assoc3

from

(select sales_id, max(case when role = 'Doctor' then name else NULL
end) as name from view1

group by sales_id) v1

left join (select sales_id , name from view1 where role = 'Associate'
) v2 on v1.sales_id = v2.sales_id

left join (select sales_id , name from view1 where role = 'Associate'
) v3 on v1.sales_id = v3.sales_id and (v3.name is null or v3.name >
v2.name)

left join (select sales_id , name from view1 where role = 'Associate'
) v4 on v1.sales_id = v4.sales_id and (v4.name is null or( v4.name >
v2.name and v4.name > v3.name))

However now it return extra row if there is more than one associate.

New Output:

58Mark BonasRon Doe NULL NULL
59Mike Johnsonjohn2 smithNULL NULL
102NULL Chris CarterJames JonesRon Doe
102NULL Chris CarterRon Doe NULL
102NULL James JonesRon Doe NULL
102NULL Ron Doe NULL
NULL

Thanks Again.|||It's ugly to write this in SQL. If you really want to do this, you can
do it using temp tables.
-------------------------------------

create table #T
(i int, name varchar(50), role varchar(50))

insert #T
values('58','Ron Doe','Associate')
insert #T
values('58','Mark Bonas','Doctor')
insert #T
values('59','Mike Johnson','Doctor')
insert #T
values('59','John Smith','Associate')
insert #T
values('102','Chris Carter','Associate')
insert #T
values('102','Ron Doe','Associate')
insert #T
values('102','James Jones','Associate')

-- doctor and first associate
select
i,
min(case when role='doctor' then name else 'zzz' end) as doctor,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T1
from #T
group by i

-- second associate
select
#T.i,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T2
from #T
left join #T1 on #T.i=#T1.i and #T.name=#T1.associate
where #T1.associate is null
group by #T.i

-- third associate
select
#T.i,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T3
from #T
left join #T1 on #T.i=#T1.i and #T.name=#T1.associate
left join #T2 on #T.i=#T2.i and #T.name=#T2.associate
where #T1.associate is null and #T2.associate is null
group by #T.i

-- output
select #T1.i, #T1.doctor, #T1.associate as assoc1, #T2.associate as
assoc2, #T3.associate as assoc3
from #T1
join #T2 on #T1.i=#T2.i
join #T3 on #T1.i=#T3.i

Convert Rows into Columns... (Cross tab).

Hi genius,

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

Regards,
j_jst

You can try "Pivot transformation". Book online has examples.|||

You can also select the cells, copy, shift F10, select paste and transpose. The selected rows will copy in columnar form.

Good Luck

|||

Hi,

I also have a same problem. Can you suggest what was your solution?

regards

Josh

|||

I also need to do something similar...

It was suggested that I would probably need to use a cursor in a stored procedure which loops through the rows and updates a temporary table with the values I need.

I haven’t got round to doing this yet… so if anybody has a solution which I could have a look at I’d be very grateful.

I’ll post my solution when I’ve got it.

|||

Jon:

What version of SQL Server are you using? Also, a cursor is NOT normally a good idea. Take a look at some of these posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

Please open a new thread and post what you are trying to accomplish.

|||

select [Month],
max(case when [Status]='I' then [Count] end) as I,
max(case when [Status]='O' then [Count] end) as O,
max(case when [Status]='S' then [Count] end) as S
from mytable
group by [Month]
order by [Month]

|||Mark's response is why I want you to post a new thread. Sorry, Mark. Should I split this thread?|||

Thanks for the links kent.

I have managed to solve my problem.

Convert Rows into Columns... (Cross tab).

Hi genius,

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

Regards,
j_jst

You can try "Pivot transformation". Book online has examples.|||

You can also select the cells, copy, shift F10, select paste and transpose. The selected rows will copy in columnar form.

Good Luck

|||

Hi,

I also have a same problem. Can you suggest what was your solution?

regards

Josh

|||

I also need to do something similar...

It was suggested that I would probably need to use a cursor in a stored procedure which loops through the rows and updates a temporary table with the values I need.

I haven’t got round to doing this yet… so if anybody has a solution which I could have a look at I’d be very grateful.

I’ll post my solution when I’ve got it.

|||

Jon:

What version of SQL Server are you using? Also, a cursor is NOT normally a good idea. Take a look at some of these posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

Please open a new thread and post what you are trying to accomplish.

|||

select [Month],
max(case when [Status]='I' then [Count] end) as I,
max(case when [Status]='O' then [Count] end) as O,
max(case when [Status]='S' then [Count] end) as S
from mytable
group by [Month]
order by [Month]

|||Mark's response is why I want you to post a new thread. Sorry, Mark. Should I split this thread?|||

Thanks for the links kent.

I have managed to solve my problem.