August 16, 2007 at 9:09 am
I have date column in integers in almost all the tables. I want to convert integer to date from all the tables in a database and filter them for the year 1995. I need a count of rows from all the tables in a database for the year 1995.
How can i get this result in a single script.
August 16, 2007 at 9:23 am
hi, are the dates stored in the integer field, ie. which is month day and year ? i.e is christmas day 1995 19961225 or something else ?
Paul
August 16, 2007 at 9:25 am
date is stored in integer like 73459 when i do a convert I get this date 02/13/2002
I want this to be done for total column and show the result where year =1995.
something like select * from emp for the year 1995
August 16, 2007 at 9:33 am
How do you convert 73459 to 02/13/2002
declare @i int
SET @i = 73459
SELECT CONVERT(datetime, @i)
When I run this, I get 02/15/2101
August 16, 2007 at 9:36 am
look up datepart in Books OnLine ... and use that in your where clause, something like the following :-
select convert(datetime, integer_column)
from table
where datepart(yyyy, (datetime, integer_column)) = 1995
August 16, 2007 at 9:36 am
am using a function
return
(convert(char(10),cast(@IntVal - 36161 as datetime),101))
August 16, 2007 at 9:43 am
select
convert(char(10),cast(@IntVal - 36161 as datetime),101)
from your_table
where
datepart(yyyy,convert(char(10),cast(@IntVal - 36161 as datetime),101))=1995
just replace @intval with your column name and it should work
August 16, 2007 at 9:47 am
thanks ripg.. tht works fine but how can i do that for the entire database, most of the tables have the same column.
Actually I want to count the no.of rows returned for the year 1995 from the database and measure the size of those rows.
August 16, 2007 at 9:57 am
I am able to count no of rows for each table for the year 1995 but how can I add all the count in the database ie: for all the talbes
August 16, 2007 at 10:32 am
select count(convert(char(10),cast(date - 36161 as datetime),101))
from
emp
where
datepart(yyyy,convert(char(10),cast(date - 36161 as datetime),101))=1995
I am using the above query to count no of rows, how can i do the same for tha entire database.
I need count of rows in all the tables in DB for the year 1995( year can be found from a date column , most of the tables has table column in DB)
August 16, 2007 at 10:53 am
Either write a cursor to loop through each of your tables, or see if sp_msforeachtable will work in your situation.
August 16, 2007 at 10:56 am
exec
sp_MSforeachtable 'select count(*) as no_of_rows, ''?'' as table_name from ?
where datepart(yyyy,convert(char(10),cast(date - 36161 as datetime),101))=1995'
This query is working fine but it returns no of rows for each table, how can i get total no of rows from all the tables.
that is I want a total of 'no_of_rows'
August 16, 2007 at 11:34 am
Insert the values into a table, and sum them up then, or modify a copy of that stored proc to roll them into a variable, create your own cursor to do the same, etc. Why do I get the feeling I'm missing something here?
August 16, 2007 at 11:50 am
I am trying to insert into temp table and sum the total but no luck..any help on that.
August 16, 2007 at 12:06 pm
OK Now I got the total no of rows in a database.
Now is there a way to find the space used by my total count.
I got a count of 162345 rows(selected rows from the DB). what is the space used by these rows?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply