SQL to find first and last day of a month

How to get the first and last day of any month.

To get the first and last day of any month when given a date we can use the date functions
as below.

Suppose we want the first and last day for the date '04/28/2009', that is for April 2009.

declare @given_date datetime
select @given_date = '04/28/2009'

-- SQL to get the first day of the month
select dateadd(dd,-(day(dateadd(mm,1,@given_date))-1),dateadd(mm,0,@given_date))

-- SQL to get the last day of the month
select dateadd(dd, -day(dateadd(mm,1,@given_date)), dateadd(mm,1,@given_date))

To get next or previous month's first or last day change the highlighted number accordingly.



--SQL to get the first day of the second next month
select dateadd(dd,-(day(dateadd(mm,1,@given_date))-1),dateadd(mm,2,@given_date))

-- SQL to get the last day of the second next month
select dateadd(dd, -day(dateadd(mm,1,@given_date)), dateadd(mm,3,@given_date))

Read Full Post...

How to get underlying table information of a proxy table ?

How to check proxy table information ?

To know underlying server, database and table information of a proxy table we can
query sysattributes table from within local database.

The query can be as below.

select object_type, object_cinfo, char_value from sysattributes where object_type = 'OD'

OD stands for 'Object Definition' , no clue as of now why 'OD' though :( , will get it some where
down the line... no rush :)

Read Full Post...

How does a view works ?

How does a sybase view works internally?
How does a view perform as against a direct SQL?

While trying to understand the performance aspect of a view, this is what i came across in Sybase infocenter documentation.

A view can be derived from one or more underlying tables. Only the definition of the view is stored int e database (syscomments table), and not the view data.

When we use a view in a SQL statement, ASE combines the statement with this stored definition
to translate the statement to query underlying tables. This process is called as view resolution.

Consider the following view definition

create view hiprice
as select *
from titles
where price > $15
and advance > $5000

Now, if we use this view like below,

select title, type
from hiprice
where type = "popular_comp"



Internally, ASE combines the query with "hiprice" view's definition, converting the query to:

select title, type
from titles
where price > $15
and advance > $5000
and type = "popular_comp"

Note that it is not only "resolving" the 'where' clause but also the selected column list.

This shows that performance wise it should work as good as any other SQL statement, and should not have any considerable extra overhead.

Read Full Post...

How to estimate table size

Once you create a table, you can estimate the table size using command sp_estspace.

To estimate the amount of space required by a table and its indexes:
1. Create the table.
2. Create all indexes on the table.
3. Run sp_estspace, giving the table name, the estimated number of rows for the table, and the optional arguments, as needed.



You do not need to insert data into the tables. sp_estspace uses information in the system tables--not the size of the data in the tables--to calculate the size of tables and indexes.

Example :
sp_estspace TableA, 50000

where TableA is table name and 50000 is estimated rows in the table.

Further details can be found in Sybase Manuals

Read Full Post...

How to copy whole directory tree

To copy a whole directory structure you can use -r option of cp
It copies all the files in a directory and its subdirectories.

#-- Copy every thing from /homeDir/yourDir to /homeDir/tempDir/

cp -r /homeDir/yourDir/* /homeDir/tempDir/



Note:
You can use other supported wild cards ( instead of * ) to be more "selective" while copying.

#-- Copy every file/directory, which starts from 'S', from /homeDir/yourDir to
#-- /homeDir/tempDir/

cp -r /homeDir/yourDir/[S]* /homeDir/tempDir/

Read Full Post...

rollback SQL changes on database

How to rollback SQL changes done on a database?
Well... it's a tip to be used in all your future SQL adventures with sensitive database.

Sometimes, you run a query which does some thing undesired and you want to 'rollback' your changes. But Sybase does allow to 'rollback' only when there is a transaction.
The tip is very simple, but very much neglected by developers.

Whenever you execute delete/update/insert SQLs, use transaction.
This gives you a chance to control the changes done to database.


e.g.

Suppose you want to delete data from yourTable where col_1 = 100

--#Execute a wrong SQL.
delete from yourTable where col_1 = 10

You can not rollback this, as there is no transaction.

--#Now, try this...
begin tran
delete from yourTable where col_1 = 10

Now, if you check the table, all the data where col_1 = 10, is deleted.
But, this will not be a permanent change, unless you execute 'commit tran'

--#Do a rollback
rollback tran

The changes will be 'rollback'.

--#Now run the correct query
delete from yourTable where col_1 = 100

Now, if you check the table, all the data where col_1 = 100, is deleted,
this is what we want.

--#At this time you can execute ...
commit tran

This will make the changes permanent.

Read Full Post...