Converting DataSet to new SQL Table

Hello Everyone,

I have a dataset that I created from an external database using an ODBC connection. I would like to take that dataset and create a new table in an SQL 2005 database. Can anyone point me in the right direction. The problem that I am having right now is getting the object types etc.

Thank you!!

The first method is to use ExecuteNoneQuery in ADO.NET to create you table, try the links below for code samples. Hope this helps.

http://forums.asp.net/thread/1385209.aspx

http://www.functionx.com/csharp/adonet/Lesson04.htm

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.

Converting ACCESS and EXCEL data to SQL

Hi,

I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.
How may this be done?
I am able to create a Data Component with the ACCESS mdb file.

Likewise, how may I convert EXCEL data to SQL2005 table?
Thanks.

David

Access should have an upsizing wizard look for it, excel try the link and code below. Hope this helps.

http://www.sqlis.com/

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||

Thank you very much for the reply!
Moving along, I tried do an "upsize" from ACCESS to SQL, but I was not able to create a DSN for the SQL server. (Could not specify the "server"; I have tried using "local" and it didn't work.)
I am using SQL 2005 express - not the full version.
Can anyone tell me if this is due to limitation of the express version?
i.e. is it not possible to create a DSN to SQL 2005 Express ?

David

|||

You don't need DSN see if you can upsize it into SQL Server 2000. Then you can backup and restore it. Try the link below download and install the SQL Server 2005 Express Manager it, there must be a way to upsize. BTW Microsoft bought a migration tool company I forgot all about it you can download from the company site and test drive or use the link below to sign up for a Microsoft beta program. Hope this helps.

http://www.microsoft.com/sql/migration/default.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

sqlsql

Converting a SQL Query to a Web page

I have a web site, and want to create a query on it using my current SQl database information, can someone explain to me how this is done? Or just send me a link to where I can get the information.
ThanksI am not entirely sure what you mean. Do you wish to
execute a query from the web site to retrieve data from
the database or send an update query to the database? Or
something else? Also, which server side technology are
you using for the web site? (PHP, ASP, ASP.NET, etc)
David Grant
>--Original Message--
>I have a web site, and want to create a query on it
using my current SQL database information, can someone
explain to me how this is done? Or just send me a link
to where I can get the information.
>Thanks
>.
>

Converting a date field to the month

How can I create a field in a view that takes a date field and
converts it to a month?
For instance, if I have a field called "CreatedOn" with a date of
'2/22/2007', how can I create another field called "Month" that will
say "February"? Is there a built-in SQL function that does this (like
using CONVERT) or do I need to write my own?

Thanks!
Lisa(lisa.moffitt@.gmail.com) writes:

Quote:

Originally Posted by

How can I create a field in a view that takes a date field and
converts it to a month?
For instance, if I have a field called "CreatedOn" with a date of
'2/22/2007', how can I create another field called "Month" that will
say "February"? Is there a built-in SQL function that does this (like
using CONVERT) or do I need to write my own?

Look up datename in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

converting .dbf files to sql server 2000

Hi..

I want to convert .dbf files to sql server 2000 tables.. without using any tools. I need to create a different structure for sql server tables other than contains in the .dbf files. May be the dbf files contain only 3 columns. but i need 5 columns and some calculations to determine the values of some fields to insert into sql server table...

i need to code this using c# in asp.net.. can u help me?

thanks in advance..

Fraijo

A .dbf file is Character and Number data types but the chart below is all the different types yo need to convert that to so I don't see how you can do it without ETL(extraction transformation and loading) tool. So create a DTS package to move your data. Hope this helps.

.NET Framework Type

ADO.NET Database Type

SQL Data Type

String

Varchar

Varchar()

String

Nvarchar

Nvarchar()

String

NChar

Nchar()

String

NText

NText

String

Text

Text

Double

BigInt

Float

DateTime

DateTime

Datetime

DateTime

SmallDateTime

Smalldatetime

Int

Int

Int

Int64

BigInt

Bigint

Int16

SmallInt

smallint

Byte[]

Binary

Binary()

Byte[]

Image

Image

Byte[]

VarBinary

Varbinary()

Byte

TinyInt

Tinyint

Bool

Bit

Bit

Decimal

Decimal

Decimal

Decimal

Money

Money

Decimal

SmallMoney

SmallMoney

Float

Float

Float

Guid

UniqueIdentifier

Uniqueidentifier

Real

Real

Real

|||

Hi..

Thanks for ur reply.. but how can i access the .dbf files/tables from ASP.NET?

What are the procedures used to get the values from a .dbf file/table? the connection string. and driver and the things to connect

Hope get reply soon..

with regards

Fraijo

|||

Assuming your dbf files are for FoxPro the links below is all I have got and I cannot tell you anything about it because I have never used it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfoxgen7/html/usingaspnetwithvfp7.asp

http://forums.asp.net/853129/ShowPost.aspx

convert unicode to greek

hello
i have a table like this
CREATE TABLE [dbo].[test1] (
[nom] [nchar] (10) COLLATE Greek_CI_AI NULL ,
[prenom] [nchar] (10) COLLATE Greek_BIN NULL
) ON [PRIMARY]
GO
i insert some string in greek caracter in it but when a execute a select a
have a result in latin
could you help mepierreyves,
What are you using to view the data?
Is your tool what is mapping the Greek Characters to Latin when you look at
them?
Russell Fields
"pierreyves" <pierreyves@.digitallife.be> wrote in message
news:%23G7FzVTfDHA.1764@.TK2MSFTNGP09.phx.gbl...
> hello
> i have a table like this
> CREATE TABLE [dbo].[test1] (
> [nom] [nchar] (10) COLLATE Greek_CI_AI NULL ,
> [prenom] [nchar] (10) COLLATE Greek_BIN NULL
> ) ON [PRIMARY]
> GO
> i insert some string in greek caracter in it but when a execute a select a
> have a result in latin
> could you help me
>

convert text to a table

I want to create a function which can convert text to a table...
let's said @.text='1,2,3,a,b'
select * from ConvertTextToTable(@.text)
it will return
key
--
1
2
3
a
b
Note: parameter must be text, not varchar.Britney wrote:
> I want to create a function which can convert text to a table...
> let's said @.text='1,2,3,a,b'
>
> select * from ConvertTextToTable(@.text)
>
> it will return
> key
> --
> 1
> 2
> 3
> a
> b
> Note: parameter must be text, not varchar.
You can't use text as a local variable in a stored procedure. Could you
explain better about how this code is executed and where the data comes
from. Are you wanting to write this as a stored procedure and a
function? If so, you could fetch the data from the table as text and
pass the value to the function. The function will need to parse the data
in the text parameter and insert into a table variable. I don't have
time to write the parsing routine, but the basic structure of the
function is as follows:
create function dbo.Text2Table (@.t text)
Returns @.Results Table (MyCol char(1)) -- check the column datatype
as
Begin
-- iterate through the text parameter and parse into a local variable
-- Insert into the table variable as each new value is found
Insert Into @.Results Values ('1')
Return
End
David Gugick
Quest Software
www.imceda.com
www.quest.com|||http://www.aspfaq.com/2248
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23Z0wAbjrFHA.1168@.TK2MSFTNGP11.phx.gbl...
>I want to create a function which can convert text to a table...
> let's said @.text='1,2,3,a,b'
>
> select * from ConvertTextToTable(@.text)
>
> it will return
> key
> --
> 1
> 2
> 3
> a
> b
> Note: parameter must be text, not varchar.
>
>|||well , your example is limited to 8000 varchar,
I want to write a function that accept longer than 8000,
that's why I said we must use TEXT datatype..
is it because function doesn't support TEXT as parameter?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eicRHojrFHA.1168@.TK2MSFTNGP11.phx.gbl...
> http://www.aspfaq.com/2248
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%23Z0wAbjrFHA.1168@.TK2MSFTNGP11.phx.gbl...
>|||No, it's because you can't use a TEXT variable as a local variable. So, you
would have to do this in a much more complex way, e.g. insert the @.text
parameter directly into a table, and then loop through it using SUBSTRING
and PATINDEX to find the next comma. Ugh.
Are you really going to pass more than 8000 characters worth of
comma-separated values to a stored procedure? Have you considered doing
this parsing elsewhere?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23ZlLlzjrFHA.460@.TK2MSFTNGP15.phx.gbl...
> well , your example is limited to 8000 varchar,
> I want to write a function that accept longer than 8000,
> that's why I said we must use TEXT datatype..
> is it because function doesn't support TEXT as parameter?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eicRHojrFHA.1168@.TK2MSFTNGP11.phx.gbl...
>|||yes... it's a big pain.
we have more than 8000 characters sometimes.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ufuOm4jrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> No, it's because you can't use a TEXT variable as a local variable. So,
> you would have to do this in a much more complex way, e.g. insert the
> @.text parameter directly into a table, and then loop through it using
> SUBSTRING and PATINDEX to find the next comma. Ugh.
> Are you really going to pass more than 8000 characters worth of
> comma-separated values to a stored procedure? Have you considered doing
> this parsing elsewhere?
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%23ZlLlzjrFHA.460@.TK2MSFTNGP15.phx.gbl...
>|||> yes... it's a big pain.
> we have more than 8000 characters sometimes.
Well, does this mean that sometimes there are 8,200 characters, or sometimes
there is 1 GB worth of data?
It might make sense for the application to do the parsing.
It might make sense for the application to split the strings up into 8,000
character chunks, and pass multiple parameters into the stored procedure.
It might make sense to call a single stored procedure multiple times for
each 8,000 character chunk that the app parses.
It might make sense to pass all 2 GB into the stored procedure as a TEXT
parameter, then parse/loop using SUBSTRING() and PATINDEX() as earlier
described.
Or it might make sense to redesign.
Who knows? We don't really have enough information about your app and what
is happening to this big comma-separated string to determine how to solve
this problem best.
A

convert sql smalldatetime column into numbers

HI all;

I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.

Any help would be highly appreciated, thanks all.select convert(varchar,getdate(),112) ??

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 input parameter into field

create procedure [dbo].[findtext]
(
@.fieldname nvarchar(50),
@.searchtext nvarchar(50)
)
AS
SELECT * FROM tablename WHERE @.fieldname = @.searchtext
it doesn't work!Hi Joe,
Use dynamic SQL to build the SQL statement, something like this:
DECLARE @.sql nvarchar(200)
Set @.sql = 'SELECT * FROM tablename WHERE ' + @.fieldname + ' = ''' +
@.searchtext + ''''@.searchtext '''
EXEC(@.sql)
Ray
"joe" wrote:

> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||joe
You will have to use dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||Like rb and Uri said, dynamic SQL. Be careful to validate your data though,
since that could open you up to SQL injection attacks.
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!

convert hexadecimal datetime to normal datetime

Hi,
I have a field of timestamp datatype. The data is hexadecimal.
I would like to create a function or query the field so that I can see it
as normal 00:00:00 format?
It would also be nice to be able to query the field by entering a 00:00:00
value but it searches the field in the hexadecimal format and then returns
the results again in the 00:00:00 format.
thanksChris wrote:
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks
Use the undocumented extended sproc xp_varbintohexstr
CREATE FUNCTION dbo.TStoString
(
@.ts binary(8)
)
RETURNS varchar(20)
AS
BEGIN
declare @.s varchar(20)
EXEC master.dbo.xp_varbintohexstr @.ts, @.s out
RETURN @.s
END|||Hi Chris
What do you mean by the normal 00:00:00 format?
A timestamp value has absolutely nothing to do with time. It is an internal
counter. Timestamps are also not meant to be queried. SQL Server compares
them internally to determine if a row has been updated.
If you want a datetime column for your own querying, you can add one to the
table.
HTH
Kalen Delaney, SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Chris,
I think your by the name of the data type. SQL Server has datetime
and timestamp data types, but the second one has nothing to do with datetime
.
Example:
use northwind
go
create table dbo.t1 (
c1 int not null identity(1, 1) unique,
c2 datetime,
c3 timestamp
)
insert into dbo.t1 default values
insert into dbo.t1 default values
select * from t1
update
t1
set
c2 = getdate()
where
c1 = 1
select * from dbo.t1
drop table dbo.t1
go
See datetime and timestamp in BOL for more info.
AMB
"Chris" wrote:

> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Thanks Kalen,
I learned in the books online that is useless to me as an actual way to
determine at what time a record was updated.
"Kalen Delaney" wrote:

> Hi Chris
> What do you mean by the normal 00:00:00 format?
> A timestamp value has absolutely nothing to do with time. It is an interna
l
> counter. Timestamps are also not meant to be queried. SQL Server compares
> them internally to determine if a row has been updated.
> If you want a datetime column for your own querying, you can add one to th
e
> table.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
>
>

CONVERT Error Message

Greetings,
I have a "newbie" question in relation to dates. I am trying to append the
year, month, and day to one another in order to create a new date. The SQL
I
am using is below. The error message I am receiving is "The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value." I have also tried using "CAST", but I receive a similar error
message on that as well. Any idea as to what I am doing wrong?
Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
Convert(varchar(12),year(ebm.Hire_Date + 30))
Thanks in advance!
--
SherwoodSherwood (Sherwood@.discussions.microsoft.com) writes:
> I have a "newbie" question in relation to dates. I am trying to append
> the year, month, and day to one another in order to create a new date.
> The SQL I am using is below. The error message I am receiving is "The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value." I have also tried using "CAST", but I
> receive a similar error message on that as well. Any idea as to what I
> am doing wrong?
> Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
> Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
> Convert(varchar(12),year(ebm.Hire_Date + 30))
There are two safe date formats in SQL Server (three in SQL 2005). The
most commonly used is YYYYMMDD. When you use delimited formats, it's up
to the settings how the date will be interpreted.
Anyway, I am not really sure what you want to do, but you should have a
look at the dateadd() function, that may simplify your problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was able to resolve it by using the code below. The reason it initially
failed was due to the fact that I was using it in one of the conditions of a
CASE statement. The other condition had a different format and apparently
that executed first. At any rate, the code below seems to be working.
Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
Convert(varchar(15),day(ebm.Hire_Date))
Thanks.
--
Sherwood
"Erland Sommarskog" wrote:

> Sherwood (Sherwood@.discussions.microsoft.com) writes:
> There are two safe date formats in SQL Server (three in SQL 2005). The
> most commonly used is YYYYMMDD. When you use delimited formats, it's up
> to the settings how the date will be interpreted.
> Anyway, I am not really sure what you want to do, but you should have a
> look at the dateadd() function, that may simplify your problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Sherwood (Sherwood@.discussions.microsoft.com) writes:
> I was able to resolve it by using the code below. The reason it
> initially failed was due to the fact that I was using it in one of the
> conditions of a CASE statement. The other condition had a different
> format and apparently that executed first. At any rate, the code below
> seems to be working.
> Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
> Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
> Convert(varchar(15),day(ebm.Hire_Date))
Hire someone in December, and you will get a nasty surprise.
This does work:
dateadd(MONTH, 1, ebm.Hire_date)
And this is what you should use.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Convert datetime to char string

Hi all,
CREATE TABLE Result (
ValueString char(8),
ValueDate datetime
)
Sample data from ValueDate column in british (103) format:
29/1/2005
1/12/2004
1/1/2003
How to convert to ValueString respectively
20050129
20041201
20030101
Thanks
ZedI'd use a nested CONVERT. First use convert into a datetime, adding the type
103 as 3;rd parameter.
Then you use an outer convert into a string, using code 112.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Private Man" <orivate@.yahoo.com.au> wrote in message
news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> Hi all,
> CREATE TABLE Result (
> ValueString char(8),
> ValueDate datetime
> )
> Sample data from ValueDate column in british (103) format:
> 29/1/2005
> 1/12/2004
> 1/1/2003
> How to convert to ValueString respectively
> 20050129
> 20041201
> 20030101
> Thanks
> Zed
>|||UPDATE Result
SET ValueString = CONVERT(CHAR(8), ValueDate , 112)
Jacco Schalkwijk
SQL Server MVP
"Private Man" <orivate@.yahoo.com.au> wrote in message
news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> Hi all,
> CREATE TABLE Result (
> ValueString char(8),
> ValueDate datetime
> )
> Sample data from ValueDate column in british (103) format:
> 29/1/2005
> 1/12/2004
> 1/1/2003
> How to convert to ValueString respectively
> 20050129
> 20041201
> 20030101
> Thanks
> Zed
>|||select convert(char(10), ValueDate, 112) from Result
Enjoy
Peter
"The best minds are not in government. If any were, business would steal
them away."
Ronald Reagan
"Private Man" wrote:

> Hi all,
> CREATE TABLE Result (
> ValueString char(8),
> ValueDate datetime
> )
> Sample data from ValueDate column in british (103) format:
> 29/1/2005
> 1/12/2004
> 1/1/2003
> How to convert to ValueString respectively
> 20050129
> 20041201
> 20030101
> Thanks
> Zed
>
>|||or if i'm going to be exact select convert(char(8), ValueDate, 112) from
Result.
Peter
"I only made one mistake today - got up"
"Peter 'Not Peter The Spate' Nolan" wrote:
> select convert(char(10), ValueDate, 112) from Result
> Enjoy
> Peter
> "The best minds are not in government. If any were, business would steal
> them away."
> Ronald Reagan
>
> "Private Man" wrote:
>|||Thanks
Zed
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ueZEy$VJFHA.3132@.TK2MSFTNGP12.phx.gbl...
> UPDATE Result
> SET ValueString = CONVERT(CHAR(8), ValueDate , 112)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Private Man" <orivate@.yahoo.com.au> wrote in message
> news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>|||My bad. I missed that ValueDate already is datetime. The inner CONVERT isn't
needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:eKTO0$VJFHA.1280@.TK2MSFTNGP09.phx.gbl...
> I'd use a nested CONVERT. First use convert into a datetime, adding the ty
pe 103 as 3;rd
> parameter. Then you use an outer convert into a string, using code 112.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Private Man" <orivate@.yahoo.com.au> wrote in message
> news:42302202$0$22857$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>