Putting dates into varchar

I've got 2 tables :

TABLE DateSqlServer
Date as DateTime

TABLE DateDb2
Date as VarChar(26)

I run these queries :

INSERT INTO DateSqlServer (Date) VALUES (GetDate())
INSERT INTO DateDb2 SELECT Date FROM DateSqlServer

Then, when I run :

SELECT Date FROM DateDb2

I get
"march 8 2004 3:45 PM"

instead of
"2004-03-08 03:45:12:000"

How can I transfer the date as I see it in table DateSQLServer
WITHOUT doing FORMATs on the Date column ?

Why does the INSERT transform the date format ?It's an implicit conversion..you need to CONVERT it into the format you want.

Dates are stored as 8 position numerc and SQL does the conversion that way...

It's another SQL Server "feature"

In my Own Opinion (MOO) it should fail.

Can you jam a varchar in to an int? No

Try

INSERT INTO DateDb2 (you really should always list the columns here)
SELECT CONVERT(varchar(26), [Date], 120)
FROM DateSqlServer

MOO|||I wanted to avoid using list of columns

I'm loading my SQL-Server database from the linked DB2 database
with a unique distributed query (good for all tables)

for each table (Table1, Table2, Table3, ..)

INSERT INTO [SQL-Server-DB].[TableX]
SELECT *
FROM OPENQUERY ([DB2-Server],
'SELECT DB2-DB.TableX.* FROM DB2-DB.TableX')

This way I did'nt need the structure of each table
AND the datatype of each column
to load the data|||I would recommend a different approach ...
Build a repository table and store name of tables and fields in that ...
build dynamic queries ( yeah, I know that is bad) for getting the data from the db2 tables with whatever conversions you need|||oye...

Where to start...

First, open a BIG bottle of Tequila...

Next, I'm big on the dynamic code generation...saves lots of time...

But not on a nightly process.

Yo uneed to know the structures and they need to be static.

Otherwise you're looking for trouble.

And while you're looking, it'll find you.

I would set up unloads of the data on DB2...ftp/copy it to your server and bcp

You can write format card generators...gen ftps, gen bcps...everything...

But then that's it...you have to then test it all, then install it...

As static sprocs...

Don't forget error handling...

What's the whole reason for this?

Oh, and MOO|||I am using this type of proc for import from SAP where the table structure remains constant ..|||Well if it's constant, why would you need dynamic anything (except to gen the statement in the first place?)

Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

Pull only numeric values

I have a varchar field that contains both numeric and text data. I need to pull only numeric, non-null values.

I am fairly new to SQL, so I applogise if this is a really basic question.

Thanks.

hi

try:

SELECT col1, col2
FROM table
WHERE (ISNUMERIC(col1) AND col1 IS NOT NULL)

where col1 is your varchar column