Grabbing DATENAME from date only returns January

why is the below only giving me January?

CAST(DATENAME(Month, datepart(month, ph.systemmonth)) AS varchar(15))

What did you expect ?

-Jens Suessmeyer.

http://www.sqlserver2005.de|||

I solved it:

Results:

0000002 MA 0000002 25 5.00 January 1 2005
0000002 MA 0000002 25 20.00 January 1 2005
0000002 MA 0000002 25 43.00 January 1 2005
0000002 MA 0000002 25 1264.20 January 6 2005
0000002 MA 0000002 25 1344.47 January 6 2005
0000002 MA 0000002 25 210.23 January 10 2005
0000002 MA 0000002 25 211.25 January 10 2005

Expected Results:

0000002 MA 0000002 25 5.00 January 1 2005
0000002 MA 0000002 25 20.00 January 1 2005
0000002 MA 0000002 25 43.00 January 1 2005
0000002 MA 0000002 25 1264.20 June 6 2005
0000002 MA 0000002 25 1344.47 June 6 2005
0000002 MA 0000002 25 210.23 October 10 2005
0000002 MA 0000002 25 211.25 October 10 2005

Resolution:

CAST(DATENAME(Month, '2006/' + Convert(varchar(2), ph.systemmonth)) + '/1') AS varchar(15)) as SystemMonth

for a real date field (which systemmonth was not in this case...since it only contained the month) you can do this:

CAST(DATENAME(Month, '2006/' + Convert(varchar(2), datepart(mm,getdate())) + '/1') AS varchar(15)) as SystemMonth,

Got it!

Thanks Tibor...
ALTER procedure admin_ConvertUnix2Dos
as
declare @.dir varchar(256)
declare @.FileName varchar(256)
declare @.Convert varchar(512)
declare @.Exec varchar(512)
create table #tmp
(FileName varchar(256))
set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /B
'
insert into #tmp exec master..xp_cmdshell @.dir
declare MyCur cursor for
select FileName from #tmp
open MyCur
fetch next from MyCur into @.FileName
while @.@.fetch_status = 0
begin
set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B
',@.FileName+'"')
set @.Convert = replace(@.Convert,'dir','')
exec master..xp_cmdshell @.Convert
fetch next from MyCur into @.FileName
end
close MyCur
deallocate MyCur
"ChrisR" <noemail@.bla.com> wrote in message
news:e7b%23CC3oFHA.2080@.TK2MSFTNGP14.phx.gbl...
> I've been messing with the quotes and am getting nowhere quickly. Any
> ideas?
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eYhBM12oFHA.3828@.TK2MSFTNGP12.phx.gbl...
>You removed the double quotes around the path of the EXE file? I think the p
roblem is that when you
have two sets of double quotes (as in the first version), you need to enclos
e the hole shebang in
double quotes:
""c:\Unix2Dos\Unix2Dos.exe" "C:\Documents and Settings\chris.rose\My
Documents\FTP\CABHLDRLSACTNCONSTANTS.TAB;1""
Not needed now as you don't have spaced etx in path to Unix2Dos.exe, but mig
ht be worth knowing for
next time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:uAmBsL3oFHA.3996@.TK2MSFTNGP12.phx.gbl...[c
olor=darkred]
> Thanks Tibor...
>
> ALTER procedure admin_ConvertUnix2Dos
> as
> declare @.dir varchar(256)
> declare @.FileName varchar(256)
> declare @.Convert varchar(512)
> declare @.Exec varchar(512)
> create table #tmp
> (FileName varchar(256))
> set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /
B '
> insert into #tmp exec master..xp_cmdshell @.dir
> declare MyCur cursor for
> select FileName from #tmp
> open MyCur
> fetch next from MyCur into @.FileName
> while @.@.fetch_status = 0
> begin
> set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B ',@.FileName+
'"')
> set @.Convert = replace(@.Convert,'dir','')
> exec master..xp_cmdshell @.Convert
> fetch next from MyCur into @.FileName
> end
> close MyCur
> deallocate MyCur
>
> "ChrisR" <noemail@.bla.com> wrote in message news:e7b%23CC3oFHA.2080@.TK2MSF
TNGP14.phx.gbl...
>[/color]

Good SQL Query

Folks, i've got a table with a column; ACCOUNT VARCHAR(30). All the values numeric though. (leave abt the datatype yet).
The column is clustered indexed.

SELECT * FROM MYTABLE WHERE LEFT(ACCOUNT,3)='123'
execution plan shows CLUSTERED INDEX SCAN.

SELECT * FROM MYTABLE WHERE ACCOUNT LIKE '123%'
execution plan shows CLUSTERED INDEX SEEK.

How, why. Why doesn't the optimizer works good for the first query?

Howdy!Can you refraze the question. Can it be built with the query anylzer?|||LEFT is seen by the optimizer as similar to UPPER. In short, the query plan looks at the function result as an unknown value, and thus the table scan.|||LEFT is seen by the optimizer as similar to UPPER. In short, the query plan looks at the function result as an unknown value, and thus the table scan.well, that's pretty stupid of it, eh :)

oh, i don't mean in the general sense, i am forever telling people not to do stuff like

... where year(transdate) = year(getdate())

i mean specifically in the case of the LEFT function|||Can you refraze the question. Can it be built with the query anylzer?

create table mytable (account varchar(10))

go
create clustered index myindex on mytable(account)
go
declare @.v int
set @.v=1
while @.v<9000
begin
insert mytable select 'abcdefgh'
set @.v=@.v+1
end

-- table scan
select * from mytable where left(account,3)='abc'

-- index seek
select * from mytable where account like 'abc%'

drop table mytable|||is there an index on that column at all ?|||It's likely that the scan occurs because left has to retrieve the whole thing while the like only retrieves the first three characters. The clustered index is going to organize that column by varchar physically on the disk, so the like statement will retrieve two indexes and everything in between, versus the left which will have to check every record "in between".

Does that make sense ?

Cheers,
-Kilka|||It's NonSargable

http://www.sql-server-performance.com/sql_server_performance_audit8.asp

Global variable in stored procedure

Hi I'm having problems using a global variable that has been declared as a varchar in a stored procedure. The stored procedure is recursive, and after calling itself I'm no longer able to access the global variable, see the sp bellow.

Any help would be great

Thanks

Dan

CREATE PROCEDURE dbo.kb_c_getChildren
(
@.CategoryID uniqueidentifier
)
AS
SET NOCOUNT ON

/* Global variables */
if @.@.nestlevel = 1
begin
declare @.@.CatList varchar (8000)
set @.@.CatList = ''
end

/* Find children */

declare @.child uniqueidentifier
declare children cursor local for
select CatID from Category where Parent_CatID = @.CategoryID

open children

fetch next from children
into @.child

while @.@.fetch_status = 0
begin
set @.@.CatList = @.@.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child)
begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child
end

fetch next from children
into @.child

end

close children
deallocate children

print @.@.CatList

RETURN 1When I looked in Books On Line for Global variables I didn't find anyhting to support what you are trying to do. Would this work?

CREATE PROCEDURE dbo.kb_c_getChildren(
@.CategoryID uniqueidentifier,
@.CatList varchar(8000) = Null OUTPUT)
AS
SET NOCOUNT ON

/* Find children */

declare @.child uniqueidentifier
select @.child = min(CatID)
from Category
where PArent_CatID = @.CategoryID

while (@.child is not null) begin
set @.CatList = @.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child) begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child, @.CatList OUTPUT
end

select @.child = min(CatID)
from Category
where Parent_CatID = @.CategoryID
and CatID > @.child
end

if (@.@.nestlevel = 1)
print @.CatList

RETURN 1

I changed from using a cursor to a simple select and test, this is just a personnal thing for me, and changed your catlist to be an optional output parameter. On the 2nd and subsequent calls to the SP you will past your populated catlist to kb_c_getChildren, modify the contents, and return it to the calling sp. on the last itiration you should fall out of the while loop print the results.|||Hi Paul,

Thanks for the info, couldn't use the select statement as I'm using uniqueidentifers, but the output parameter works a treat.

Thanks again

Dan

Giving Table name as a Variable

Hi guys,

I want to insert record to the table, the table name is a variable. I was tried like this,

declare @.Table_Name as varchar(255)

declare @.MM as varchar(2)

declare @.YY as varchar(4)

set @.MM = month(getdate())

set @.YY = year(getdate())

set @.Table_Name = 'tb_XXXX_' + @.MM + @.YY

INSERT INTO @.Table_Name (column1,....) VALUES (@.Column1,...)

If I print the @.Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message

'Must declare the sclar variable @.Table_Name'

How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.

Arun.

You have to use the dynamic SQL here. Here the sample to use the dynamic SQL – using sp_executesql or exec

Code Snippet

Create Table #TestDynSQL

(

Id int,

Name varchar(100),

DOB Datetime

);

Declare @.P_Id int, @.P_Name varchar(100), @.P_dob datetime

Declare @.SQL as Nvarchar(4000)

Declare @.ParamDecl as Nvarchar(4000)

Set @.SQL = N'Insert Into #TestDynSQL Values(@.id, @.name, @.dob)'

Set @.ParamDecl = N'@.id int, @.name varchar(100), @.DOB datetime'

Set @.P_id=1

Set @.P_Name='Mani'

Set @.P_Dob='1979-07-26'

--Highly recommanded to use the sp_executesql

Exec sp_executesql @.SQL, @.ParamDecl, @.P_id, @.P_name, @.P_dob

--or

--Beware of SQL injection while using the EXEC(STATEMENT)

Set @.SQL = 'Insert Into #TestDynSQL values(' + cast(@.P_Id as varchar) + ',''' + @.P_Name + ''',''' + Cast(@.P_Dob as varchar) + ''')'

Exec (@.SQL)

Select * from #TestDynSQL

|||

Mani gave you a good solution using dynamic SQL.

However, there are some issues about using dynamic SQL that you should be aware. There may be significant performance penalties and there may also be security issues. The following articles are good resources to learn and explore the use (and mis-use) of dynamic SQL.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx