HI everyne,
I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.
CAST(RIGHT(datafield,2)+LEFT(datafield,4) AS datetime)
|||Thanks Motley. It works!hi All ,
I am getting one param for a SP as list of states from the Front End as :
@.states = 'NY,NJ,CA,Fl,MA' . Now i have to convert this param in the form :
@.states_for_SP = 'NY','NJ','CA','Fl','MA' . Is there any efficient way to do it except using REPLACE function. As this portion in our SP is taking a lot of time in converting in the desired form.
Plz suggest to do this.
Thanks.
I don't understand why a simple replace, e.g.
Code Snippet
declare @.a varchar(100)
set @.a = '''NY,NJ,CA,Fl,MA'''
declare @.b varchar(100)
set @.b = replace(@.a, ',', ''',''')
select @.a, @.b
Should be slow. Is this similar to what you're trying to do?
Greg.
|||
Mohit,
This is a slightly different approach.
Using the function below you can convert the list into a table and then join the table into your query.
Code Snippet
IFEXISTS(
SELECT*FROMsys.objects
WHEREobject_id=OBJECT_ID(N'[dbo].[list2set]')
ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROPFUNCTION [dbo].[list2set];
GO
CREATEFUNCTION dbo.list2set( @.list nvarchar(max), @.delim nvarchar(10))
RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))
AS
BEGIN
IFlen(@.list)<1 RETURN;
DECLARE @.xList XML;
-- no validity tests are performed, depending on input this could fail
SET @.xList =Convert(XML,''+REPLACE(@.list, @.delim,'')+'')
INSERTINTO @.resultset
SELECT data.listitem.value('.','nvarchar(max)')as item
FROM @.xList.nodes('/list/item') data(listitem)
RETURN
END
GO
You'd then use it as such:
Code Snippet
SELECT adr.state
FROM Address adr
innerjoin dbo.list2set(@.states, N',') sel
on adr.state = sel.item
|||
here the code..
Code Snippet
Create Table #Numbers(
Number Int
);
Declare @.I as int;
Set @.I = 1
While @.I<100
Begin
Insert Into #Numbers values(@.I);
Set @.I = @.I + 1;
End
Declare @.states varchar(100)
Set @.states = 'NY,NJ,CA,Fl,MA'
Declare @.StatesTable Table
(
State Varchar(100)
)
Insert Into @.StatesTable
Select Substring(',' + @.states + ',', Number, CharIndex(',',',' + @.states + ',',Number) - Number)
From
#Numbers
Where
Number<=Len(',' + @.states + ',')
And Substring(',' + @.states + ',',Number-1 ,1) = ','
--As you wise for concatination..
Set @.states = ''
Select @.states = @.states + ',''' + State + '''' From @.StatesTable
Select Substring(@.states,2,8000)
--Now You can use this @.StatesTable on any query for IN operator..
--Select * From SomeTable Where States in (Select State From @.StatesTable)
Hello,
I just developed a working solution to the same problem a few minutes ago. My problem was that the footer in the table was appearing in random places depending on the number of rows returned. I can fit 7 rows, so I needed to pad out additional rows to equal 7. So, I had to add additional data to the SQL resultset to do this. After my stored procedure produced a resultset, I immediately invoked this function to add more rows before returning to the report. Hope this helps. If anyone has an elegant solution, please post, I would like to implement it!
ALTER FUNCTION [dbo].[fCreateBlankTblRows]
(
@.nbrRows //my stored procedure figured out how many rows were returned and how many more were needed.
)
RETURNS
@.my_temp_tbl TABLE
(
id int,
id2 int,
xyx int
)
AS
BEGIN
Declare @.loopCount int;
set @.loopCount = 0;
WHILE (@.loopCount < @.nbrRows)
Begin
INSERT INTO @.my_temp_tbl
(id, id2, xyz)
VALUES (null,null,null);
set @.loopCount = @.loopCount + 1;
End;
RETURN
END
