|
declare @tbldate table(stdid int, sdate date, eddate date)
insert into @tbldate
values
(100, '20100102', '20100505'),
(101, '20100203', '20100302'
)
i want my result set as
check if day in date is 01 if not then make day as 01 and increment month my 1. i have the solution but thats in 2 temporary table and i want full solution in a single table.
|
|
|
|
|
You mistake is that you are treating a date as a string - lesson 1: A DATE IS NOT A STRING. - learn this lesson now and it will save you a lot of pain in the future.
You can use DATEADD within a loop. You still need to generate the sequential numbers (@day) 1-100, then you can do something like
DECLARE
@Day INT
SET @Day = 1
WHILE @Day < 101
BEGIN
SELECT DATEADD(d,@Day,GETDATE())
SET @Day = @Day+1
END
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
dear i also can do this but i by taking variable u can pass single value but i want to achieve this by query.
|
|
|
|
|
scottichrosaviakosmos wrote: achieve this by query
I don't think it can be done!
The only work around I can think of is to create a UDF (function) which returns a table. Basically move the code into a function, have it return aresult set and use the result set to join to some thing else. I have one called GenDates and use it like
Select * from Gendates(StartDate, Days)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How about "case when" ?
case when
cast(right(@dates,2) as int) > 1
then
convert(char(8),dateadd(month,1,cast(left(@dates,6) + '01' as datetime(8))),112)
else
@dates
end
|
|
|
|
|
Hello,
I have scoured the web, found MANY apps that let you do this, however I am in need of a free solution. I have product sheets in an xls file that need to be put in a database, and I cannot type a query to insert hundreds of thousands of items into a db manually.
Is there any free, easy way to insert Excel OR CSV File into MySQL OR MSSQL Database?
I appreciate any help at all, thank you!
|
|
|
|
|
The main problem will be that Excel is unstructured data, if all your sheets are well formed and are all EXACTLY the same the you can write a program with 3 parts:
Folder scan that identifies each xls file
importer that reads each xls into a data table (either cell by cell or using Jet)
DAL to write the data table to the database.
Expecting a "free" tool to do this is a little much.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SSIS. It's free if you have SQL Server 5005 2005 Developer or above. You can do it in minutes if you have it.
Edit: Fixed typo
SG
Aham Brahmasmi!
|
|
|
|
|
For MYSQL I use the LOAD DATA SQL.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
|
|
|
|
|
I have a query of the form
select * from (
select ...
,...
,...
,a.value_1 open_value_1
,to_number(NULL) close_value_1
from table a
where a.date_col = date_1
union all
select ...
,...
,...
,to_number(NULL) open_value_1
,b.value_1 close_value_1
from table b
where b.date_col = date_2
);
If the list of columns represented by the ... is considered a primary key, for the situations where the primary keys are the same I end up with multiple rows
AA BBB CCC open_value_1
AA BBB CCC close_value_1
How would I tailor my query to give me only one row instead of two rows. Like this
AA BBB CCC open_value_1 close_value_1
Thanks for any suggestions.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Look into Pivot queries. It looks like that may be what you want.
|
|
|
|
|
|
SELECT ...,...,...,MAX(open_value_1),MAX(close_value_1) FROM
(
--YOUR SELECT QUERY
)A
GROUP BY ...,...,...
|
|
|
|
|
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Perfect. Thanks very much. I knew I needed some grouping to happen and couldn't think of putting some function like max together. It works exactly as I needed it to.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
you are welcome
|
|
|
|
|
Hi,
I have one column in one table in sql server like
SUK
-----
abc 111 bbb 1212
pqr 222 ccc 2323
i have to split this in to 4 columns
how to do this,,,please help
DOnt update the table ,only for viewing. in a storedprocedure i want this
Rakesh
|
|
|
|
|
SUK is a good name for that column - as in it SUKs having an awful structure. If you needed those values as discrete columns they should have been discrete to start with!
Anyway, a partial solution for you; if you can guarantee the startindex and length of each part it's not too bad:
;WITH SukTable ([Suk]) AS
(
SELECT 'abc 111 bbb 1212'
UNION SELECT 'pqr 222 ccc 2323'
)
SELECT
SUBSTRING(SUK,1,3) AS Field1,
SUBSTRING(SUK,5,3) AS Field2,
SUBSTRING(SUK,9,3) AS Field3,
SUBSTRING(SUK,12,5) AS Field4
FROM SukTable
Otherwise, assuming the parts are separated by a single space, its possible but becomes a dog very quickly - here's a solution for the first 2 parts, already becoming unweildly:
;WITH SukTable ([Suk]) AS
(
SELECT 'abc 111 bbb 1212'
UNION SELECT 'pqr 222 ccc 2323'
)
SELECT
LEFT(SUK,CHARINDEX(' ',Suk,0)-1) AS Field1,
SUBSTRING(SUK,CHARINDEX(' ',Suk,0),CHARINDEX(' ',Suk,CHARINDEX(' ',Suk,0))-CHARINDEX(' ',Suk,0)) AS Field2
FROM SukTable
Edit: Another option is something like this: http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql[^]
Fix your data model, before it's too late.
|
|
|
|
|
IT IS AUTOMATICALLY EXPANDING TABLE,VALUES MAY CHANGE,ONLY COMMON THING IS ONLY THE SPACE BETWEEN THEM.SO ON THAT WAY .......
|
|
|
|
|
1) DONT SHOUT!!!!
2) I have no idea what you just told me
|
|
|
|
|
Do it after you fetch the data into your code.
|
|
|
|
|
Here i am giving a scalar function to active this..,
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitGETPosFunc](@String varchar(8000),
@Delimiter char(1),
@POSITION INT)
RETURNS varchar(1000)
WITH EXECUTE AS CALLER
AS
begin
declare @idx int;
DECLARE @CHARIDX INT;
DECLARE @INPUTSTR VARCHAR(8000);
declare @OUTPUT varchar(1000);
SET @INPUTSTR=@String;
SET @CHARIDX =0;
SET @idx=0;
if (substring(@String,1,1)='S')
begin
while @idx < @POSITION
begin
SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR);
SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1)
SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR));
SET @idx=@idx+1;
end
end
else
set @OUTPUT=@String;
return @OUTPUT;
end
@String = original string
@Delimiter = delemeter (in your case space)
@POSITION INT = position of the word(starts from 1)
function will return the word in the specified location
Thanks & Regards
Rajesh B
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
while executing ssis package to execute an access macro and end up by getting error "The script threw an exception: Retrieving the COM class factory for component with CLSID {73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9} failed due to the following error: 80080005."
i googled a lot and all i have found is to edit the access application properties in componant services.
any ideas?
|
|
|
|
|
This looks like a Permission exception. Try to give your aspnet user permission and the "Administrator" user of the machine you are running the package.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I have a recursive problem.
I have a query that select some columns but one of these colums should be a value that is recursive.
I have a documentname and based on this name I can retrieve the the folderstructure, but this folderstructure is recursive. How can i create 1 query that returns the documentName and the full folderstructure (which is found if the foldername is '').
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|