i get an error when i use COUNT(DISTINCT colum_name)

hello,

i have a working stored procedure

SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS'NrOfComments'FROM Comments WHERE PictureID = @.PictureID

witch returns among others the number of comments for a picture

i need to select the number of distinct users who commented that user too, so i added this at SELECT statement

, COUNT(DISTINCT UserName) AS'Expr1'

i get that error:

"Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause."

what should i do, to select the number of distinct users who commented on a specific picture?

here are the table rows, if you need that

CommentID -- UserName -- PictureID -- DateCommented

please help me, thank you

You cannot get both the distinct users and the total number of comments for a picture in the same query. To use the COUNT function, you need to use GROUP BY.

For instance, to get the total number of comments for a given PictureID you would do:

SELECT PictureID,COUNT(*)FROM CommentsWHERE PictureID = @.PictureIDGROUP BY PictureID

If you would like to get the number of comments each user has made for a given PictureID you would do:

SELECT PictureID, UserName,COUNT(*)FROM CommentsWHERE PictureID = @.PictureIDGROUP BY PictureID, UserName

You will have to do two queries to get the data you want - one for the summary, and one for the actual rows.|||

ok, i understand...

i have one last question

Is possible to have two SELECT statements in the same stored procedure?

thank you for replays

|||

Basically, you can only return the result from one SELECT statement but it is possible to build this query as a UNION or JOIN with values that you need. However, this can be quite complex, and I would recommend you to split it into two procedures in this case.

|||

yes, two select statements are not possbile, but select in select works very good for what i was needed

SELECT CommentID, UserName, PictureID,(SelectCOUNT(CommentID)FROM UserPictures)AS 'Comments'
FROM UserPictures

thanks for replyes, the post is solved

|||

Ouch. I don't mean to scare you, but this will in fact count the total number of comments for every row in your table. Doing the same calculation over and over again, it will give you the same result for every row. Is this really what you want? It is ok as long as you are aware that there may be performance issues with this. It might be an easy solution, (and working) if you dont have zillions of rows in your table ;-)

I can't work out. Duplicate row

Dear all,
I am not be able to delete a row in the following table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSysRights]
GO
CREATE TABLE [dbo].[tblSysRights] (
[sinRightID] [smallint] IDENTITY (1, 1) NOT NULL ,
[sinUserID] [smallint] NOT NULL ,
[sinApplicationID] [smallint] NOT NULL ,
[sinType] [smallint] NULL
) ON [PRIMARY]
GO
No indexes. No constraints. No relationships. What on earth hell is happenin
g?
SELECT *
FROM tblSysRights
WHERE (sinUserID = 61) AND (sinApplicationID = 163)
725 61 163 null
725 61 163 null
I obtain a warning which tell me:
"key column information is insufficient. too many rows affected"
Thanks in advance and best regards,Set RowCount 1
Delete
FROM tblSysRights
WHERE (sinUserID = 61) AND (sinApplicationID = 163)
Set RowCount 0
Madhivanan|||Enric,

> I am not be able to delete a row in the following table:
Can you be more specific?
From where and how are you trying delete the row?
AMB
"Enric" wrote:

> Dear all,
> I am not be able to delete a row in the following table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblSysRights]
> GO
> CREATE TABLE [dbo].[tblSysRights] (
> [sinRightID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [sinUserID] [smallint] NOT NULL ,
> [sinApplicationID] [smallint] NOT NULL ,
> [sinType] [smallint] NULL
> ) ON [PRIMARY]
> GO
> No indexes. No constraints. No relationships. What on earth hell is happen
ing?
> SELECT *
> FROM tblSysRights
> WHERE (sinUserID = 61) AND (sinApplicationID = 163)
> 725 61 163 null
> 725 61 163 null
> I obtain a warning which tell me:
> "key column information is insufficient. too many rows affected"
> Thanks in advance and best regards,
>
>|||Thanks for that Madhinavan.
Done successfully.
Thanks Alejandro, too.
Regards,
"Alejandro Mesa" wrote:
> Enric,
>
> Can you be more specific?
> From where and how are you trying delete the row?
>
> AMB
>
> "Enric" wrote:
>|||Just to add a couple of points. For elimination of duplicates, refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Use keys in all your tables, and you will avoid a lot of such headaches. On
a related note, a couple of comments:
* You seem to have an identity column which is constrained to be unique, but
apparently it seems to be violated with SET IDENTITY_INSERT ON setting.
Check where the "hole" is.
* Your naming conventions are stale which can be better -- search for some
good guidelines in the archives of this newsgroup.
* Use Query Analyzer for testing your queries, the EM query interface is
buggy and has certain limitations.
* And most importantly, use primary keys in your tables. In relational
database, a value is identified using <table, column, key> triplet. With
duplicates, such identification becomes impossible and relational operations
cannot be used beneficially. The impossibility of logically deleting one row
out of duplicates is often cited as a prime illustration of the consequence
of allowing duplicates by SQL. ( SQL language inventors and its flag bearers
should take the credit, for even standardizing them ! )
Anith|||Thanks for that Anith.
That table was defined without identity fields or key. Just a mess-up.
Best Regards,
"Anith Sen" wrote:

> Just to add a couple of points. For elimination of duplicates, refer to:
> http://support.microsoft.com/defaul...b;EN-US;q139444
> Use keys in all your tables, and you will avoid a lot of such headaches. O
n
> a related note, a couple of comments:
> * You seem to have an identity column which is constrained to be unique, b
ut
> apparently it seems to be violated with SET IDENTITY_INSERT ON setting.
> Check where the "hole" is.
> * Your naming conventions are stale which can be better -- search for some
> good guidelines in the archives of this newsgroup.
> * Use Query Analyzer for testing your queries, the EM query interface is
> buggy and has certain limitations.
> * And most importantly, use primary keys in your tables. In relational
> database, a value is identified using <table, column, key> triplet. With
> duplicates, such identification becomes impossible and relational operatio
ns
> cannot be used beneficially. The impossibility of logically deleting one r
ow
> out of duplicates is often cited as a prime illustration of the consequenc
e
> of allowing duplicates by SQL. ( SQL language inventors and its flag beare
rs
> should take the credit, for even standardizing them ! )
> --
> Anith
>
>

I can't add a user in "Security/Logins"

Under Security - Logins, right-click the user and hit properties. (IN
SQL 2000)Go to the database access tab, select the db you want to grant
access to then give the rights below. By default, you only grant
public.
natasha wrote:
> Good morning everyone,
> I've a user listed in "Security/Logins".
> If I right/click a database, Properties, "Permission" tab, I can't "Add" i
t
> to database's "Users or roles": if I "Browse" for users, it isn't listed.
How
> can I add?Yes, and in SQL 2005 "database access" tab has been substituted by "User
mapping" tab. But, owner of the database is dbo, and differently from other
DBs, I can't substitute, even if I am the super-non-plus-ultra-admin.
Is there any way to force a "take ownership" of the database?
"PSPDBA" ha scritto:

> Under Security - Logins, right-click the user and hit properties. (IN
> SQL 2000)Go to the database access tab, select the db you want to grant
> access to then give the rights below. By default, you only grant
> public.
> natasha wrote:
>|||Good morning everyone,
I've a user listed in "Security/Logins".
If I right/click a database, Properties, "Permission" tab, I can't "Add" it
to database's "Users or roles": if I "Browse" for users, it isn't listed. Ho
w
can I add?|||Under Security - Logins, right-click the user and hit properties. (IN
SQL 2000)Go to the database access tab, select the db you want to grant
access to then give the rights below. By default, you only grant
public.
natasha wrote:
> Good morning everyone,
> I've a user listed in "Security/Logins".
> If I right/click a database, Properties, "Permission" tab, I can't "Add" i
t
> to database's "Users or roles": if I "Browse" for users, it isn't listed.
How
> can I add?|||Yes, and in SQL 2005 "database access" tab has been substituted by "User
mapping" tab. But, owner of the database is dbo, and differently from other
DBs, I can't substitute, even if I am the super-non-plus-ultra-admin.
Is there any way to force a "take ownership" of the database?
"PSPDBA" ha scritto:

> Under Security - Logins, right-click the user and hit properties. (IN
> SQL 2000)Go to the database access tab, select the db you want to grant
> access to then give the rights below. By default, you only grant
> public.
> natasha wrote:
>|||Have you tried using the following stored procedure on the database where yo
u
wnat the user to have access to:
EXEC sp_adduser 'loginname', 'username'
See if that works or give any error?
Lucas
"natasha" wrote:
[vbcol=seagreen]
> Yes, and in SQL 2005 "database access" tab has been substituted by "User
> mapping" tab. But, owner of the database is dbo, and differently from othe
r
> DBs, I can't substitute, even if I am the super-non-plus-ultra-admin.
> Is there any way to force a "take ownership" of the database?
>
> "PSPDBA" ha scritto:
>|||Have you tried using the following stored procedure on the database where yo
u
wnat the user to have access to:
EXEC sp_adduser 'loginname', 'username'
See if that works or give any error?
Lucas
"natasha" wrote:
[vbcol=seagreen]
> Yes, and in SQL 2005 "database access" tab has been substituted by "User
> mapping" tab. But, owner of the database is dbo, and differently from othe
r
> DBs, I can't substitute, even if I am the super-non-plus-ultra-admin.
> Is there any way to force a "take ownership" of the database?
>
> "PSPDBA" ha scritto:
>|||I tried to, and I've quite a curious error:
Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.
"Lucas Kartawidjaja" ha scritto:
[vbcol=seagreen]
> Have you tried using the following stored procedure on the database where
you
> wnat the user to have access to:
> EXEC sp_adduser 'loginname', 'username'
> See if that works or give any error?
> Lucas
> "natasha" wrote:
>|||> Is there any way to force a "take ownership" of the database?
sp_changedbowner
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"natasha" <natasha@.discussions.microsoft.com> wrote in message
news:FBFC25FE-C6C8-4B8E-A769-419169EF1DDB@.microsoft.com...[vbcol=seagreen]
> Yes, and in SQL 2005 "database access" tab has been substituted by "User
> mapping" tab. But, owner of the database is dbo, and differently from othe
r
> DBs, I can't substitute, even if I am the super-non-plus-ultra-admin.
> Is there any way to force a "take ownership" of the database?
>
> "PSPDBA" ha scritto:
>|||I tried to, and I've quite a curious error:
Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.
"Lucas Kartawidjaja" ha scritto:
[vbcol=seagreen]
> Have you tried using the following stored procedure on the database where
you
> wnat the user to have access to:
> EXEC sp_adduser 'loginname', 'username'
> See if that works or give any error?
> Lucas
> "natasha" wrote:
>sql

I cannot transfer column DEFAULT values from old table to new table

Hi all,
SQL 2000
I am doing :
SELECT *
INTO NewTable
FROM OldTable
WHERE 1 =0
The NewTable has the same :
column names, Data Type and Length BUT the column DEFAULT VALUES
don't get transferred , I always have te reenter them manually.
Does any one have a quick solution? Thanks a millionDefaults is a type of constraint, and those are not carried over with SELECT
INTO. For this, you
have to script the table...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<placidite1@.yahoo.com> wrote in message news:1178732983.525139.93780@.u30g2000hsc.googlegroup
s.com...
> Hi all,
> SQL 2000
> I am doing :
> SELECT *
> INTO NewTable
> FROM OldTable
> WHERE 1 =0
> The NewTable has the same :
> column names, Data Type and Length BUT the column DEFAULT VALUES
> don't get transferred , I always have te reenter them manually.
> Does any one have a quick solution? Thanks a million
>|||Thanks Mr Tibor Karaszi
I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
formation
and it doesn't carry over the Default Constraint either...I have the
CREATE TABLE script, but
no default values.
Is there any tool within SQL 2000 that will do it (ie carry over the
default values)?
Thanks a million.
On May 9, 1:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Defaults is a type of constraint, and those are not carried over with SELE
CT INTO. For this, you
> have to script thetable...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphtt
p://sqlblog.com/blogs/tibor_karaszi
>
> <placidi...@.yahoo.com> wrote in messagenews:1178732983.525139.93780@.u30g20
00hsc.googlegroups.com...
>

>
> - Show quoted text -|||> I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
> formation
You need to configure this to include constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<placidite1@.yahoo.com> wrote in message
news:1178748995.880148.288630@.e65g2000hsc.googlegroups.com...
> Thanks Mr Tibor Karaszi
> I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
> formation
> and it doesn't carry over the Default Constraint either...I have the
> CREATE TABLE script, but
> no default values.
> Is there any tool within SQL 2000 that will do it (ie carry over the
> default values)?
> Thanks a million.
>
> On May 9, 1:54 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>

I cannot transfer column DEFAULT values from old table to new table

Hi all,
SQL 2000
I am doing :
SELECT *
INTO NewTable
FROM OldTable
WHERE 1 =0
The NewTable has the same :
column names, Data Type and Length BUT the column DEFAULT VALUES
don't get transferred , I always have te reenter them manually.
Does any one have a quick solution? Thanks a millionDefaults is a type of constraint, and those are not carried over with SELECT INTO. For this, you
have to script the table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<placidite1@.yahoo.com> wrote in message news:1178732983.525139.93780@.u30g2000hsc.googlegroups.com...
> Hi all,
> SQL 2000
> I am doing :
> SELECT *
> INTO NewTable
> FROM OldTable
> WHERE 1 =0
> The NewTable has the same :
> column names, Data Type and Length BUT the column DEFAULT VALUES
> don't get transferred , I always have te reenter them manually.
> Does any one have a quick solution? Thanks a million
>|||Thanks Mr Tibor Karaszi
I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
formation
and it doesn't carry over the Default Constraint either...I have the
CREATE TABLE script, but
no default values.
Is there any tool within SQL 2000 that will do it (ie carry over the
default values)?
Thanks a million.
On May 9, 1:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Defaults is a type of constraint, and those are not carried over with SELECT INTO. For this, you
> have to script thetable...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <placidi...@.yahoo.com> wrote in messagenews:1178732983.525139.93780@.u30g2000hsc.googlegroups.com...
> > Hi all,
>
> >SQL2000
> > I am doing :
> > SELECT *
> > INTO NewTable
> > FROM OldTable
> > WHERE 1 =0
> > The NewTable has the same :
> >columnnames, Data Type and Length BUT thecolumnDEFAULTVALUES
> > don't get transferred , I always have te reenter them manually.
> > Does any one have a quick solution? Thanks a million- Hide quoted text -
> - Show quoted text -|||> I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
> formation
You need to configure this to include constraints.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<placidite1@.yahoo.com> wrote in message
news:1178748995.880148.288630@.e65g2000hsc.googlegroups.com...
> Thanks Mr Tibor Karaszi
> I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
> formation
> and it doesn't carry over the Default Constraint either...I have the
> CREATE TABLE script, but
> no default values.
> Is there any tool within SQL 2000 that will do it (ie carry over the
> default values)?
> Thanks a million.
>
> On May 9, 1:54 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Defaults is a type of constraint, and those are not carried over with SELECT INTO. For this, you
>> have to script thetable...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>>
>> <placidi...@.yahoo.com> wrote in
>> messagenews:1178732983.525139.93780@.u30g2000hsc.googlegroups.com...
>> > Hi all,
>> >SQL2000
>> > I am doing :
>> > SELECT *
>> > INTO NewTable
>> > FROM OldTable
>> > WHERE 1 =0
>> > The NewTable has the same :
>> >columnnames, Data Type and Length BUT thecolumnDEFAULTVALUES
>> > don't get transferred , I always have te reenter them manually.
>> > Does any one have a quick solution? Thanks a million- Hide quoted text -
>> - Show quoted text -
>

I cannot transfer column DEFAULT values from old table to new table

Hi all,
SQL 2000
I am doing :
SELECT *
INTO NewTable
FROM OldTable
WHERE 1 =0
The NewTable has the same :
column names, Data Type and Length BUT the column DEFAULT VALUES
don't get transferred , I always have te reenter them manually.
Does any one have a quick solution? Thanks a million
Defaults is a type of constraint, and those are not carried over with SELECT INTO. For this, you
have to script the table...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<placidite1@.yahoo.com> wrote in message news:1178732983.525139.93780@.u30g2000hsc.googlegro ups.com...
> Hi all,
> SQL 2000
> I am doing :
> SELECT *
> INTO NewTable
> FROM OldTable
> WHERE 1 =0
> The NewTable has the same :
> column names, Data Type and Length BUT the column DEFAULT VALUES
> don't get transferred , I always have te reenter them manually.
> Does any one have a quick solution? Thanks a million
>
|||Thanks Mr Tibor Karaszi
I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
formation
and it doesn't carry over the Default Constraint either...I have the
CREATE TABLE script, but
no default values.
Is there any tool within SQL 2000 that will do it (ie carry over the
default values)?
Thanks a million.
On May 9, 1:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Defaults is a type of constraint, and those are not carried over with SELECT INTO. For this, you
> have to script thetable...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <placidi...@.yahoo.com> wrote in messagenews:1178732983.525139.93780@.u30g2000hsc.go oglegroups.com...
>

>
> - Show quoted text -
|||> I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
> formation
You need to configure this to include constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<placidite1@.yahoo.com> wrote in message
news:1178748995.880148.288630@.e65g2000hsc.googlegr oups.com...
> Thanks Mr Tibor Karaszi
> I tried the SQL 2000 -> All Tasks -> Generate SQL Script on the table
> formation
> and it doesn't carry over the Default Constraint either...I have the
> CREATE TABLE script, but
> no default values.
> Is there any tool within SQL 2000 that will do it (ie carry over the
> default values)?
> Thanks a million.
>
> On May 9, 1:54 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>

i can not make query for any information in arabic language

HI,

i work on Sql Developer Edition when i select for any Company name in arabic it does not get any data ,why? although my data are inserted by arabic language,please help me asap,thank you

and my code like this

use today

select CatID from dbo.tbl_Categories
where CatName like '%???????%'

Try to set the default collation to "Arabic_CL_AS"

|||

hi,

please tell me from where can i set that? or if i use specific syntax please send it to me,thank you.

|||try this:

alter table tbl_Categoriesalter column CatNamevarchar(100) COLLATE Arabic_CI_AI

Note the collation provided for this column. Collation is used by SQL Server to store data for different languages. Have a look at COLLATE or COLLATION in BOL. You can use

select *from ::fn_helpcollations()

to check all the installed collations on your server.

Hope this will help.

|||

How to change default collate in SQL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36707

|||

Hi,

by the way i finished from added may data to database in arabic language i made import from Excel file but the problem is when i make query on arabic data i recieved no row selected how is?,thank you

|||

hi,

i still face the problem, by the way i finished from added my data to database in arabic language i made import from Excel file but the problem is when i make query on arabic data i recieved no row selected how is?,help me,thank you

|||

First of all, you can run the alter table query I provided earlier any time whether your table contains data or not. One more thing is that you can use collation at the time of writing select query too, the syntax is below:

select *from <table name>where <column name>like'<value>' collate Arabic_CI_AI

Now the comparison will be made using the Arabic_CI_AI collate for this select. As I've told earlier there are so many collates to choose from ( even for Arabic ), which differ in some way or another. Choose the collate you want to use and either alter the table permanently or at the time of selecting the values. Just go and have a look at BOL for COLLATE.

Hope this will help

|||

hi,

i altered my table with Arabic_CI_AI after that i write your syntax but still no new :S,thanks

|||

How can I reach all the possible collation options for Arabic?

This is my SQL statement which returns NO VALUES:

SELECT ArtistID, Name
FROM dbo.Artist
WHERE (Name LIKE '%?%' COLLATE Arabic_CI_AI)

I also tried

SELECT ArtistID, Name
FROM dbo.Artist
WHERE (Name LIKE '%?%' COLLATE Arabic_CL_AS)

as was suggested in one of the forum replies, but it gives an error message saying :
"Invalid Collation" with an error source: " .NetSqlClient Data Provider"

Thanks.

hw to add text in a 'select' statement

Hi Friends,
Could you tell me how to add text in a select statement.
For eg:
SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
+(datepart(dd,CURRENT_TIMESTAMP) + '-' +
datepart(yyyy,CURRENT_TIMESTAMP))AS
"CURRENT_DATE"
..............ofcourse,this is not correct but it reflects my idea.
Thanks & Regards
Dutt
Seems you got the general idea. The problem is that DATEPART returns some integer value and you
cannot concatenate that with a string (the string will be converted to an int which will fail). So
you would have to do a CAST around each DATEPART call to make it some char or varchar. But check
first if there already exists a format for the CONVERT function that has the formatting you desire
(check the 3:rd parameter in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1140021331.883831.135940@.g14g2000cwa.googlegr oups.com...
> Hi Friends,
> Could you tell me how to add text in a select statement.
> For eg:
> SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
> +(datepart(dd,CURRENT_TIMESTAMP) + '-' +
> datepart(yyyy,CURRENT_TIMESTAMP))AS
> "CURRENT_DATE"
> .............ofcourse,this is not correct but it reflects my idea.
> Thanks & Regards
> Dutt
>
|||Thanks Tibor,
Its really useful link....It worked .

hw to add text in a 'select' statement

Hi Friends,
Could you tell me how to add text in a select statement.
For eg:
SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
+(datepart(dd,CURRENT_TIMESTAMP) + '-' +
datepart(yyyy,CURRENT_TIMESTAMP))AS
"CURRENT_DATE"
.............ofcourse,this is not correct but it reflects my idea.
Thanks & Regards
DuttSeems you got the general idea. The problem is that DATEPART returns some in
teger value and you
cannot concatenate that with a string (the string will be converted to an in
t which will fail). So
you would have to do a CAST around each DATEPART call to make it some char o
r varchar. But check
first if there already exists a format for the CONVERT function that has the
formatting you desire
(check the 3:rd parameter in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1140021331.883831.135940@.g14g2000cwa.googlegroups.com...
> Hi Friends,
> Could you tell me how to add text in a select statement.
> For eg:
> SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
> +(datepart(dd,CURRENT_TIMESTAMP) + '-' +
> datepart(yyyy,CURRENT_TIMESTAMP))AS
> "CURRENT_DATE"
> .............ofcourse,this is not correct but it reflects my idea.
> Thanks & Regards
> Dutt
>|||Thanks Tibor,
Its really useful link....It worked .

hw to add text in a 'select' statement

Hi Friends,
Could you tell me how to add text in a select statement.
For eg:
SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
+(datepart(dd,CURRENT_TIMESTAMP) + '-' +
datepart(yyyy,CURRENT_TIMESTAMP))AS
"CURRENT_DATE"
.............ofcourse,this is not correct but it reflects my idea.
Thanks & Regards
DuttSeems you got the general idea. The problem is that DATEPART returns some integer value and you
cannot concatenate that with a string (the string will be converted to an int which will fail). So
you would have to do a CAST around each DATEPART call to make it some char or varchar. But check
first if there already exists a format for the CONVERT function that has the formatting you desire
(check the 3:rd parameter in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1140021331.883831.135940@.g14g2000cwa.googlegroups.com...
> Hi Friends,
> Could you tell me how to add text in a select statement.
> For eg:
> SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
> +(datepart(dd,CURRENT_TIMESTAMP) + '-' +
> datepart(yyyy,CURRENT_TIMESTAMP))AS
> "CURRENT_DATE"
> .............ofcourse,this is not correct but it reflects my idea.
> Thanks & Regards
> Dutt
>|||Thanks Tibor,
Its really useful link....It worked .