Posts Tagged ‘MsSql’

Calculate Date Difference in Sql

March 4th, 2009 No Comments

To get the difference in date in sql, use the keyword datediff. As of the writing of this article, by providing two sql dates, datediff will return the difference in years, quarter, month, dayofyear, day, week, hour, minute, second, and millisecond.

datediff( datepart, startdate , enddate)

The keyword datediff consists of three parameters. Datepart should be replaced with one of the above bolded keywords. StartDate and EndDate are relatively straightforward.

The MSDN resource can be found at: http://msdn.microsoft.com/en-us/library/aa258269(SQL.80).aspx#

Posted in Sql Tags: , , , , ,

Quickly View MsSql Stored Procedures

January 14th, 2009 No Comments

In developing web applications, I mainly use Microsoft’s Visual Studio 2008 together with SQL Server Management Studio. I however only used SQL Server Management Studio to create new databases and tables while adding Stored Procedures from Visual Studio 2008. As the project took shape, this wasn’t a major problem. However the database continues to grow not only in disk space size, but also in the number of tables and stored procedures. Constantly scrolling up and down the the Object Explorer was beginning to become a headache. I have since been recommended to use sp_help and sp_helptext. Both make reading the stored procedure setup and details easier in nearly every aspect. The downfall is the two commands do not allow changes to be made. Instead, the upside is quick access to viewing the stored procedure.

sp_help storedprocedurename

sp_helptext storedprocedurename

Posted in Sql Tags: , , ,

Sql Select Statement

September 2nd, 2008 No Comments

General Select Format

SELECT commaSeperatedColumnNames FROM tableName

Below, we will go over the basics of selecting from a table in various situations. Below is a standards table with various columns and various rows. This table will be used through the rest of this article.

Table: members

id name age gender state
1 Victor 18 M CA
2 Bill 28 M NY
3 Jill 5 F CA
4 Bob 14 M AL

Select All

SELECT * FROM members

Select Males

SELECT * FROM members WHERE gender = ‘M’

Select Age Between 10 and 20

SELECT * FROM members WHERE age between ‘10′ AND ‘20′

Select Distinct States

SELECT distinct(state) FROM members

Select All Names, But Rename Column To FirstName

SELECT name as FirstName FROM members

Posted in Sql Tags: , , , , , , ,