copy of existing table and data

Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>
Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy db table from one db to another

how do I copy a table, structure only from one database to another? I select copy in the table view of one database, but then paste is not available to me in table view of the other database. I am transitioning two db tables from a development web server to the production server, in order to go live... I don't want to have to type all of the design in the new table dialog!

Please help quickly!From Enterprise Manager if you left-click the tablename and under the menu option All Tasks you will see anoth menu option called Emport Data... . This will allow you to export the data and the table structure (if the destination is SQL Server).|||the quickest way to do it is to go into enterprise manager, and click (single click) on the table that must have its structure copied. press Ctrl-C (ie copy), open query analyser, select the database in which the new table must be created and press Ctrl-V (ie paste). The sql code for creating the table should now appear.

This works in SQL Server 7. I have not tried it in 2000.|||For Generating SQL Script:
1. You can select whatever tables you want in Enterprise manager
2. Right click and then in "All Tasks" select "Generate SQL Script"

This is the correct way of generating SQL Script for any objects.

Have fun.

Varad01

copy database structure only

Yes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:

> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...[vbcol=seagreen]
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
>|||Is there a way to copy a database without all the data? I want to end up
with the same database but "empty".
Thanks|||Yes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:

> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>|||Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...[vbcol=seagreen]
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
>

Copy All tables

Hey I'm trying to simply copy all tables from one database to another on a
nightly basis.
If i select the database, choose export, and go through all the steps,
choosing the starting database, the destination database, the values to back
up and scheduling it, it all seems fine. But the job always fails. If i
select only a couple of tables, the job runs fine? anyone have any ideas
why this would be? Or advice on another way to backup the tables nightly?
Thanks
James,
I'm not sure if you're using replication or DTS for this but either way it
sounds like a locking issue. You may need to prevent users accessing the
tables when you do the copy eg setting the database to single user mode.
Alternatively, you could use transactional replication to synchronize the
data. The initial snapshot can be taken without requiring a table lock -
allowing concurrent access. This would perhaps be preferable as you won't be
duplicating data transfer each day.
Regards,
Paul Ibison
|||Are you deleteing all the rows in your destination table or appending? It sounds like you aren't. Go back in your package and delete the existing rows before appending.
Looking for a book on SQL Server replication
http://www.nwsu.com/0974973602.html
|||How do i lock the database?
This job is running at 3am, so there shouldn't be any users working, and the
backup seems to only run for about 5 minutes before failing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u6twIeqNEHA.1456@.TK2MSFTNGP09.phx.gbl...
> James,
> I'm not sure if you're using replication or DTS for this but either way it
> sounds like a locking issue. You may need to prevent users accessing the
> tables when you do the copy eg setting the database to single user mode.
> Alternatively, you could use transactional replication to synchronize the
> data. The initial snapshot can be taken without requiring a table lock -
> allowing concurrent access. This would perhaps be preferable as you won't
be
> duplicating data transfer each day.
> Regards,
> Paul Ibison
>
|||James,
you can't lock a database, apart from making it single-user. I need to ask
some questions: Is your job runnung a dts package or replication or sps? Is
the structure of the tables changing each day or constant? How much of the
data is changing each day? How much data is there in the tables? What error
messages are returned? I need a bit more info before committing myself to
definite advice, although I suspect log-shipping or transactional
replication are what you need.
Regards,
Paul Ibison
|||Heres the error message:
Executed as user: CDC-EDB1\SYSTEM. ...Start: Copy SQL Server Objects
DTSRun OnProgress: Copy SQL Server Objects; Scripting objects for Transfer;
PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server
Objects; Scripting Logins; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Scripting Users; PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Enumerating
objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Enumerating objects for Transfer : 0
Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; 0 Percent Completed; PercentComplete =
0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects;
Enumerating objects for Transfer : 10 Percent Completed; PercentComplete =
10; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; 12
Percent Completed; Pe... Process Exit Code 1. The step failed.
Its a DTS package thats running. The values in the tables are changing
daily, I want them copied to another server where we run reports on the
data. This server is old and slow, so I don't want it slowing down the live
server.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaKmLUsNEHA.4060@.TK2MSFTNGP10.phx.gbl...
> James,
> you can't lock a database, apart from making it single-user. I need to ask
> some questions: Is your job runnung a dts package or replication or sps?
Is
> the structure of the tables changing each day or constant? How much of the
> data is changing each day? How much data is there in the tables? What
error
> messages are returned? I need a bit more info before committing myself to
> definite advice, although I suspect log-shipping or transactional
> replication are what you need.
> Regards,
> Paul Ibison
>
|||James,
the error message is not helpful and there is no way with the copy objects task of getting more info. It uses nolock , so locking is not the issue, and it is difficult to tell the exact cause of the problem. If you have constraints on the table and are us
ing row by row logging you can use the transform data task, which will give you a lot more info and possibly help you identify the cause of the problem. This will be more of a hit on your system though. For less of a hit, you can use log shipping, and the
n move the tables to a separate standby server and then copy them, or possibly overwrite the standby database completely if this suits your needs. Finally, there is transactional replication with a remote distributor which would also transfer the table da
ta.
Regards,
Paul Ibison

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK = 13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JY
Jon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegr oups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>
|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a BLOB field in a stored-procedure from

Hello
I try to run the following query:
Update DestTable
set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID = 123)
Where DestTableID = 321
I always get the error message that this is not possible with image data.
Does anyone know a solution on how to copy a BLOB from one table to another?
I tried with WRITETEXT but this does not work either.
Many thanks
Thomas (thomas.steiner@.novaesprit.ch)Can you post your WRITETEXT code please.
Rick
"Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> Hello
> I try to run the following query:
> Update DestTable
> set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID =123)
> Where DestTableID = 321
> I always get the error message that this is not possible with image data.
> Does anyone know a solution on how to copy a BLOB from one table to
another?
> I tried with WRITETEXT but this does not work either.
> Many thanks
> Thomas (thomas.steiner@.novaesprit.ch)|||Here it is:
Declare @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(content)
FROM SambaReports_Test.dbo.Documents
Where DocumentID = 171
WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
RequestDataBody from RequestData where RequestID = 2328)
This results in a 'missing datastream' error. When I put a text-string
instead of the select-statement it works.
Thomas
"Rick Sawtell" wrote:
> Can you post your WRITETEXT code please.
> Rick
>
> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> > Hello
> > I try to run the following query:
> >
> > Update DestTable
> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID => 123)
> > Where DestTableID = 321
> >
> > I always get the error message that this is not possible with image data.
> > Does anyone know a solution on how to copy a BLOB from one table to
> another?
> > I tried with WRITETEXT but this does not work either.
> >
> > Many thanks
> >
> > Thomas (thomas.steiner@.novaesprit.ch)
>
>|||AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
to copy text data between tables. If your data type is ntext, you'll need
to add calculations to allow 2 bytes per character.
DECLARE @.ptrval binary(16),
@.StartIndex int,
@.RequestDataBodyLength int,
@.RequestDataBody varchar(8000)
--clear any existing data and init text pointer
UPDATE Documents
SET content = NULL
WHERE DocumentID = 171
SET @.StartIndex = 0
--get length of data to copy
SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
FROM RequestData
WHERE RequestID = 2328
--get text pointer
SELECT @.ptrval = TEXTPTR(content)
FROM Documents
WHERE DocumentID = 171
--copy data in chunks of 8000
WHILE @.StartIndex < @.RequestDataBodyLength
BEGIN
SELECT @.RequestDataBody = SUBSTRING(RequestDataBody, @.StartIndex, 8000)
FROM RequestData
WHERE RequestID = 2328
UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> Here it is:
> Declare @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(content)
> FROM SambaReports_Test.dbo.Documents
> Where DocumentID = 171
> WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> RequestDataBody from RequestData where RequestID = 2328)
> This results in a 'missing datastream' error. When I put a text-string
> instead of the select-statement it works.
> Thomas
> "Rick Sawtell" wrote:
>> Can you post your WRITETEXT code please.
>> Rick
>>
>> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
>> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
>> > Hello
>> > I try to run the following query:
>> >
>> > Update DestTable
>> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
>> > =>> 123)
>> > Where DestTableID = 321
>> >
>> > I always get the error message that this is not possible with image
>> > data.
>> > Does anyone know a solution on how to copy a BLOB from one table to
>> another?
>> > I tried with WRITETEXT but this does not work either.
>> >
>> > Many thanks
>> >
>> > Thomas (thomas.steiner@.novaesprit.ch)
>>|||Hi Dan
It works fine! Thanks a lot.
However I had to change the line:
SUBSTRING(RequestDataBody, @.StartIndex, 8000)
to
SUBSTRING(RequestDataBody, @.StartIndex+1, 8000)
Best regards
Thomas
"Dan Guzman" wrote:
> AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
> to copy text data between tables. If your data type is ntext, you'll need
> to add calculations to allow 2 bytes per character.
>
> DECLARE @.ptrval binary(16),
> @.StartIndex int,
> @.RequestDataBodyLength int,
> @.RequestDataBody varchar(8000)
> --clear any existing data and init text pointer
> UPDATE Documents
> SET content = NULL
> WHERE DocumentID = 171
> SET @.StartIndex = 0
> --get length of data to copy
> SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
> FROM RequestData
> WHERE RequestID = 2328
> --get text pointer
> SELECT @.ptrval = TEXTPTR(content)
> FROM Documents
> WHERE DocumentID = 171
> --copy data in chunks of 8000
> WHILE @.StartIndex < @.RequestDataBodyLength
> BEGIN
> SELECT @.RequestDataBody => SUBSTRING(RequestDataBody, @.StartIndex, 8000)
> FROM RequestData
> WHERE RequestID = 2328
> UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
> SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
> news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> > Here it is:
> >
> > Declare @.ptrval binary(16)
> >
> > SELECT @.ptrval = TEXTPTR(content)
> > FROM SambaReports_Test.dbo.Documents
> > Where DocumentID = 171
> > WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> > RequestDataBody from RequestData where RequestID = 2328)
> >
> > This results in a 'missing datastream' error. When I put a text-string
> > instead of the select-statement it works.
> >
> > Thomas
> >
> > "Rick Sawtell" wrote:
> >
> >> Can you post your WRITETEXT code please.
> >>
> >> Rick
> >>
> >>
> >> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> >> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> >> > Hello
> >> > I try to run the following query:
> >> >
> >> > Update DestTable
> >> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
> >> > => >> 123)
> >> > Where DestTableID = 321
> >> >
> >> > I always get the error message that this is not possible with image
> >> > data.
> >> > Does anyone know a solution on how to copy a BLOB from one table to
> >> another?
> >> > I tried with WRITETEXT but this does not work either.
> >> >
> >> > Many thanks
> >> >
> >> > Thomas (thomas.steiner@.novaesprit.ch)
> >>
> >>
> >>
>
>

Converting varchar to DateTime

Hi,

I wanted to convert the varchar to date time and here is what i am doing

DECLARE @.dt VARCHAR(20)

SET @.dt = '20070111' -- YYYYMMDD format

select CONVERT(datetime, @.dt, 120)

This works perfectly fine and the result would be- 2007-01-11 00:00:00.000

But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

"Conversion failed when converting datetime from character string."

Can any one please let me know how do we achieve this?

~Mohan

YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

|||

This is the Convert sintax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:mis.

Then, ?Does because a string with the format yyyymmdd can be converted to string?:

The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

I recommend to you, to pass strings in the yyyy-mm-dd hh:mis format to be recognized by the CONVERT or CAST functions in SQL server.

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.
You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
|
|||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.
|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegr oups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:
>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
--
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting text to bit issues

I need to convert a text column to a bit in a select query.
Backing up, in case there's a better way to do this:
I have a varchar column that contains one of six letters. There are two
that interest me (Q and A). I want my vb.net application to update changes
back to this column.
For my application, Q=0 and A=1
The name of the column is 'Status'
I tried:
Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
.. didn't work. Any help?
UsarianTry,
Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
also:
convert(bit, ascii(upper(colA)) - ascii('Q'))
AMB
"Usarian Skiff" wrote:

> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> ... didn't work. Any help?
> Usarian
>
>|||Usarian Skiff wrote:
> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> .. didn't work. Any help?
> Usarian
>
cast((case when status = 'q' then 0 else 1 end) as bit)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Stinkin Yeah!
Thanks!
Usarian Skiff
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87BB3703-E7B8-4946-B0BF-681FBF548A61@.microsoft.com...
> Try,
> Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
> also:
> convert(bit, ascii(upper(colA)) - ascii('Q'))
>
> AMB
>
> "Usarian Skiff" wrote:
>
changes

Converting string to datetime

Hi

What's wrong about

SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')
AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A

It looks like you are trying to use the aggregate function sum() and the [item no_] column is not in an aggregate function or group by clause

|||

here it is,

Code Snippet

SELECT

SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')

AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,

[Item No_]

FROM table A

Group By

[Item No_]

|||

Still can't! It show error massage" wrong parameter".

|||

Please provide the entire procedure code and the error message in its entirity.|||

Hi

I use this query on reporting service. After perview will show "Conversion failed when converting to varchar value"/04/01" to date type int.

|||

You are attempting to concatenate the varchar value '/04/01' to the integer value @.FY.

You need to first cast the variable @.FY as a char()/varchar().

However, this could be avoided IF you used an actual datetime value for the fiscal year instead an integer value. This may be an excellent opportunity to finally explore using the 'Calendar' table you've most likely heard about before. I recommend reviewing this article to see if there is utility in the concept for you.

(From this example, I suspect that there are other places in your code where you are attempting to handle 'date' issues in 'creative' and non-functional methods.)

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

Hai,

As Arnie said, you need to first convert @.FY value to varchar type.

And, also, in your query, first date in Between is > second date which always returns the Expr1 to 0.

you can try this, sample, query:

DECLARE @.FY int

DECLARE @.TableA Table([PODate] datetime, [ItemNo_] int, Quantity int)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/01',1, 200)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/03',1, 100)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/05',1, 50)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/10',2, 30)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/22',2, 40)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/04/01',3, 60)

SET @.FY = 2007

SELECT

SUM(CASE WHEN [PODate] BETWEEN CONVERT(Datetime, CONVERT(VarChar(4),(@.FY-1)) + '/03/31') AND

CONVERT(Datetime, CONVERT(VarChar(4),@.FY) + '/04/01')

THEN Quantity ELSE 0

END) AS Expr1,

[ItemNo_]

FROM @.TableA

GROUP BY [ItemNo_]

Hope this will work, Please clarify If'm wrong.

Regards,

Y.Kiran Kumar.

converting stored procedure

CREATE PROCEDURE procCreateBasket

@.ShopperID int,

@.BasketID int OUTPUT

AS

INSERT INTO Basket(ShopperID)

VALUES (@.ShopperID)

SELECT @.BasketID = @.@.IDENTITY
I don't want to use this as a stored procedure I want to convert it as a string an use in that way in my asp.net application how can I do that?

Simply just take out the procedure declaration. For example, your string will be everything after the AS keyword.|||Adding to what master4eva said, you should be using SCOPE_IDENTITY()instead of @.@.IDENTITY. The former returns a more accurate value of theId just inserted as compared to @.@.IDENTITY. Check out books on line for more info.

Converting Select query to Update

Hi,
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:

> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP

converting SELECT output to string

I'm looking for some good hints and tips for reprogrammin an old VB module I just found.

Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.

E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".

So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @.input... the trick is, I don't know how to do arrays in TSQL as in VB.

In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.

Any good examples on how to do this in a sql-server stored proc?

Thanks,

Trin

P.S. This is what I have pieced together this far:

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)

DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO

Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,--u have to intialize ur variable 'instrument' before appending other values.

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)
set instrument =''
DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO|||Yeap, got it... and I just added a small substring addendum to cut off the trailing dash.

Mind boggling NULLs are..

Is it possible to call such a procedure within a select statement?

E.g. SELECT number, (EXEC findsnames number), city FROM names|||Just dump your cursor:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO|||u cannot call procedure from select statement,
instead of procedure , create a function,call that function from select statment.

blindman,
Poster wants to append record into a string,and ur query will not do that job.
select @.instrument returns NULL.|||Corrected code:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO

Copy/paste this to try it out:create table #Names([MCPS Kode] varchar(50), number int)
insert into #Names([MCPS Kode], number)
select 'Joe', 1
UNION
select 'James', 1
UNION
select 'Frank', 2
UNION
select 'Janis', 1
UNION
select 'Freda', 2
UNION
select 'Jeff', 1
UNION
select 'Fred', 2
UNION
select 'Foster', 2
UNION
select 'Jodi', 1

---------------------
declare @.instrument varchar(500) --varchar(50) seemed awfully short...
declare @.number int
set @.number = 1

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM #names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
---------------------

drop table #Names

Output:James-Janis-Jeff-Jodi-Joe

Yes, it can be converted into a function if the user wishes.

Converting Rrom Access To Sql Syntax

Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..

Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):

select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end

Case Statement might be the solution but i could not do it.

Your input will be appreciated

Thank you

you rightly said that CASE Statement is the right choice...

check this....
select ID, FName, LName,

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

Madhu

|||Thank you for you respond. This won't really work for one simple reason.

It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'

Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

But it is not equal to this

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

This does not work i can't have two things happening after "Then"

Do you think i can convertt this to sql Syntax?

thanks

Converting Rrom Access Syntax To Sql Syntax

Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..

Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):

select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end

Case Statement might be the solution but i could not do it.

Your input will be appreciated

Thank you

you rightly said that CASE Statement is the right choice...

check this....
select ID, FName, LName,

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

Madhu

|||Thank you for you respond. This won't really work for one simple reason.

It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'

Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

But it is not equal to this

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

This does not work i can't have two things happening after "Then"

Do you think i can convertt this to sql Syntax?

thanks