Removing Time from DateTime column in Sql Server 2005

Well this is most common question I answered many times on Sql Server MSDN forums.

How to remove Time from Datetime column in Sql Server 2005 and 2000?

Actually this is one of the biggest drawbacks in Sql server till the Sql Server 2008 happened; that we don’t have only Date datatype. In Sql server 2000 and 2005 we have Datetime datatype, which always come with time part attached with Date. Thankfully in Sql Server 2008 we have simple Date datatype.

Let it be, we’ll see how to remove Time part while querying the Datetime column. Actually it is quite easy with simple CONVERT function. The Sql syntax for it is as follows –

SELECT CONVERT ( varchar(12), DateColumn[, Style] ) as Date

FROM TableName.

Ahh, actually I wanted to create a chart out of it, but with Blogger you can’t create one, so I ended up writing this long list. Here I used getdate function; you can put the Datetime Column name in place of getdate() of your table in following examples.

• Format: mm/dd/yy
  SELECT CONVERT( Varchar(12), GetDate(),1)
  Output: 12/27/08

• Format: mm/dd/yyyy
  SELECT CONVERT(Varchar(12),GetDate(),101)
  Output: 12/27/2008

• Format: yy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),2)
  Output: 08.12.27

• Format: yyyy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),102)
  Output: 2008.12.27

• Format: dd/mm/yy
  SELECT CONVERT(Varchar(12),GetDate(), 3)
  Output: 27/12/08

• Format: dd/mm/yyyy
  SELECT CONVERT(Varchar(12),GetDate(), 103)
  Output: 27/12/2008

• Format: dd.mm.yy
  SELECT CONVERT(Varchar(12),GetDate(), 4)
  Output: 27.12.08

• Format: dd.mm.yyyy
  SELECT CONVERT(Varchar(12),GetDate(),104)
  Output: 27.12.2008

• Format: dd-mm-yy
  SELECT CONVERT(Varchar(12),GetDate(), 5)
  Output: 27-12-08

• Format: dd-mm-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),105)
  Output: 27-12-2008

• Format: dd mon yy
  SELECT CONVERT(Varchar(12),GetDate(), 6)
  Output: 27 Dec 08

• Format: dd mon yyyy
  SELECT CONVERT(Varchar(12),GetDate(),106)
  Output: 27 Dec 2008

• Format: mon dd, yy
  SELECT CONVERT(Varchar(12),GetDate(),7)
  Output: Dec 27, 08

• Format: mon dd, yyyy
  SELECT CONVERT(Varchar(12),GetDate(),107)
  Output: Dec 27, 2008

• Format: mm-dd-yy
  SELECT CONVERT(Varchar(12),GetDate(),10)
  Output: 12-27-08

• Format: mm-dd-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),110)
  Output: 12-27-2008

• Format: yy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),11)
  Output: 08/12/27

• Format: yyyy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),111)
  Output: 2008/12/27

• Format: yymmdd
  SELECT CONVERT(Varchar(12),GetDate(),12)
  Output: 081227

• Format: yyyymmdd
  SELECT CONVERT(Varchar(12),GetDate(),112)
  Output: 20081227
  
  Well I already wrote one blog entry on how to convert date in required format.
  Date Formats In SQL SERVER.
  
  For more on CONVERT see
  CAST and CONVERT (Transact-SQL) Books Online
  
  - Mangal Pardeshi.

Linked Server With DBF Files

In my previous post I wrote about Linked Server with Excel. Now we will see how to create a linked server for DBF files. That is accessing and querying DBF files from SQL Server Management Studio.

First I’ll show you how to set up the Linked Server from Management Studio.

Open the Management Studio and navigate to Server Objects and then to Linked Server. Right click on the Linked Servers node and select “Create New Linked Server”.
Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognize to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. And select the provider “Microsoft Jet 4.0 OLE DB Provider” from the list.
For “Product Name” enter “Microsoft Jet”
In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\DBF Project\Data”.
For the “Provider String” enter “dBASE 5.0”

Then go to the security page. Here you have 2 options.
Either select “Be made without using a security context”
OR
Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.
In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. Well I don’t know the exact answer why this needs; I tried searching on Net but couldn’t find the answer, but this how it works.

And say “ok”, now your Linked Server DBF files is ready. Just expand the Linked Server and then now created Linked server, after expanding the Default you will see Tables and Views. Expand the Tables node to see a list of table objects for the folder.

Now you can query the DBF files just like any other table from management studio. Now open the new query window to write a query on DBF files. Just remember you have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName].

So your select query will be like
SELECT * FROM [Linked_Server_Name]…[TableName]

e.g.
SELECT * FROM DbfLinked…Items.

Here DbfLinked is the linked server name I gave and “Items” is the table name. Observe that there are 3 dots between linked server name and table name.

We can use almost any select statement to retrieve our dbf data. For example JOINS with other tables in Sql Server or with other DBF tables, WHERE conditions, GROUP BY, HAVING, ORDER BY, even the UPDATE, DELETE or for that matter any valid Sql statement you normally do with tables.


Same Linked Server you can create using Sql Statement.
-- To create Linked Server With DBF Files
EXEC master.dbo.sp_addlinkedserver
@server = 'DbfLinked',
@srvproduct = 'Microsoft Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\DBF Project\Data',
@provstr = 'dBASE 5.0'


/* Now use either of two secuirty Context*/
-- To select security Context “Be made without using a security context”

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL

-- Or using “Be made using this security context”
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = ''


- Mangal Pardeshi.

How To Enable Xp_CmdShell in Sql Server?

Today I will show you how to enable the Xp_CmdShell extended stored procedure in Sql Server 2005 and 2008.
Well normal error message you'll get when Xp_CmdShell is not enabled on your Sql Server and you to try execute some Windows commands using Xp_CmdShell is

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


You can enable the Xp_CmdShell using 2 ways, either by executing T-sql statements or from
"Surface Area Configuration Manager". We'll see both of them.


A. Enable Xp_Cmdshell from Management Studio.
For enabling Xp_CmdShell from Management Studio you need to execute following code.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

B. Enable the Xp_CmdShell from "Surface Area Configuration Manager."

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Features.
3. Click on xp_cmdshell and tick on checkbox of "Enable the xp_cmdshell".



- Mangal Pardeshi.

How to configure SQL Server 2005 to allow remote connections

By default, SQL Server 2005 does not allow remote connections. To configure SQL Server 2005 to allow remote connections, we need to follow the steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service. (If you are using Named Instance or Sql Express.)
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
  
  So we will see how to achieve above 3 points one by one.

A] How to Enable remote connections for SQL 2005?
1. Click on Start >> Programs >> Microsoft SQL Server 2005 >> Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

B] How to Enable the SQL Server Browser service?

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections.

1. Click on Start >>Programs >> Microsoft SQL Server 2005 >> Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.

Note: When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

C] How to Create exceptions in Windows Firewall?

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK. Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

Note: The path may be different depending on where SQL Server 2005 is installed.

The source - http://support.microsoft.com/kb/914277

- Mangal Pardeshi.

Check Constraint to allow alphabets only in Sql Server

How to create a check constraint to allow alphabets i.e. A-Z characters only or alphabets + numbers with no special characters in Sql Server?
I think many of us have faced this problem sometimes. Here is the scenario, you have column with varchar/char datatype and you don’t want user to enter any numbers or any Special characters like @, #, $, *. And sometimes characters + numbers with no special characters. And we don’t know any efficient way to handle and we end up writing a long NOT LIKE check constraint.
So let’s see how to write a simple CHECK CONSTARINT for allowing only alphabets and numbers.

First I’ll show you how to write a WHERE conditions for such different cases, so you will get an idea.
Let’s create 1 temporary table and populate it with some data

CREATE TABLE #temp
(ID varchar(50))
GO

INSERT INTO #TEMP SELECT
'Mangal' UNION SELECT
'Mangal Pardeshi' UNION SELECT
'Mangal19' UNION SELECT
'19' UNION SELECT
'@Mangal***' UNION SELECT
'@#$%^&*' UNION SELECT
'Mangal19**'
GO

Now our sample table #temp is ready.
SELECT * FROM #TEMP

-- Output --

@#$%^&*
@Mangal***
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
--------------

Case 1 : We just want to get rows with only Alphabets (A-Z characters) with no numbers and Special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z]%'

-- Output --
Mangal
--------------

Now if you notice here ‘Mangal Pardeshi’ din’t get selected as it contains a SPACE in between. To avoid that we need include a single space in our condtion '%[^A-Z]%'. Now we will put '%[^A-Z ]%'.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z ]%'

-- Output --
Mangal
Mangal Pardeshi
--------------

Case 2 : Now we want select all the rows with alphabets + numbers or any of the both with no special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9 ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
--------------
Case 3 : Now we want characters + numbers or characters or numbers or a special character *. So I just add a * in our condition.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9* ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
---------------
Same way you can inculde any special character. You just need to add that character into our original condition.

Now writing a Check Constraint is very simple.

For allowing Alphabets only

ALTER TABLE TableName ADD CONSTRAINT Only_Characters CHECK ColumnName NOT LIKE '%[^A-Z ]%'
**Remember to add extra space.

For alphabets + Numbers

ALTER TABLE TableName ADD CONSTRAINT Only_Characters_And_Numebrs CHECK ColumnName NOT LIKE '%[^A-Z0-9 ]%'


Hopefuly this post will help many of you.


- Mangal Pardeshi.

How to create a time dimension in SQL Server

In this post I’ll help you creating a Time Dimesnion as it is very much required if you are working on Sql Server Analysis Services. Until now you need to write a complex Sql procedure running into loops for creating a large Time Dimension. But with Sql Server 2005 and Common Table Expressions (CTE) it is very easy to create your own Time Dimension. A time Dimension is also called as Calendar Table by many.
When I started working on SSAS 2005 I also searched for a good Time Dimension, but now I have created my own Time Dimension with following Script.

Following script creates a table named TimeDiemension and populates it from 2000-01-01 to 2020-12-31. You can easily put your required dates.
Even though following query may look little big but believe me, it's nothing but a simple SELECT statement with CTE.
------- TimeDimension -------

WITH Mangal as
(
SELECT Cast ('2000-01-01' as DateTime) Date --Start Date
UNION ALL
SELECT Date + 1
FROM Mangal
WHERE Date + 1 < = '2020-12-31' --End date
)

SELECT Row_Number() OVER (ORDER BY Date) as DateId
,Date
,YEAR (date) as Year
,DatePart ( qq, date) as Quarter
,MONTH (date) as Month_Number_Year
,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as Month_Number_Of_Quarter
,DatePart (wk, Date) as Week_Number_Of_Year
,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as Week_Number_Of_Quarter
,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as Week_Number_Of_Month
,DatePart (dy, date) as Day_Number_Of_Year
,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as Day_Number_Of_Quarter
,DAY (date) as Day_Number_Of_Month
,DatePart (dw, date) as Day_Number_Of_Week
,DateName (mm, date) as Month_Name
,LEFT ( DateName (mm, date), 3) Month_Name_Abbreviation
,DateName (dw, date) as Day_Name
,LEFT (DateName (dw, date), 3) as Day_Name_Abbreviation
,CONVERT(VarChar(10),date,112) as YYYYMMDD
,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]

INTO TimeDimension -- Name of the Table

FROM Mangal

OPTION (MAXRECURSION 0)

---- Script Ends Here ----
Now your TimeDimension is ready. Do a simple
SELECT * FROM TimeDimension
for a check.
Hopefully you will find this script helpful. Any questions and suggestions are welcome.
- Mangal Pardeshi.

Saving the Result of a query to a Text File in Sql Server

After being a part of the Sql Server MSDN forum for a quite a long time, One question I answered many time is “How to save the output of query to a text file in Sql Server?”
So thought of sharing a simple solution with you using BCP utility.

A simple query to save output of query to a text file is goes like this –

EXEC master..XP_CmdShell 'BCP "SELECT * FROM Database.dbo.TableName" queryout "c:\Mangal.txt" -c -T'

If you execute the above query and get the following error –

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Then first you need to enable the xp_cmdshell procedure. To enable the xp_cmdshell execute -

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure Xp_CmdShell,1
GO
RECONFIGURE WITH OVERRIDE
GO

Again back to BCP, Here
-c : Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \n (new line character) as the row terminator.

-T : Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

You can save the file to txt, xls, dbf, xml formats also.

There are so many other options available with BCP command.
e.g.
With –U and –P you can provide the username and password when you are not using the Windows Authentication (Trusted Connection).

For more on BCP utility you can read from Microsoft’s Books Online.
BCP Utility Books Online


Idea for this post was just to provide a simple solution on how to save the result of a query to a file?.

Well there is another way of directly sending the result of query to a file is by setting the “Result To Text” in management studio.
For that do –
Open the Management Studio.
In menu bar click to “Query” >> “Result to” and then select “Result To Text.”

Hopefully many of you find this post helpful.

- Mangal

Linked Server With Excel.

In this part I’ll help you in creating a Linked Server with Excel. Or simply how to import/query Excel in Sql Server.

In Sql Server 2005 you need to enable the 'Ad Hoc Distributed Queries'. For that first execute the following scripts.
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

And now here go
1.
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:\Mangal.xls;Extended Properties=Excel 8.0')...Sheet1$
2.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', Sheet1$)
3.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Here Excel 8.0 means this is applicable to Excel 2002, Excel 2000, or Excel 97 workbook.
Database means path of your excel book and its name.

Now you can do any Sql query with excel that you usualy do with any table. For example you can join excel with other tables, you can insert excel rows in other tables.
Lets say you want to insert first 3 columns of Excel sheet into table Customers.
You can do
INSERT INTO customers (CustId, CustomerName, Address )
SELECT Custid, CustomerName, Address
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Or to join a table with Excel

SELECT A.ColumnName, E.ColumnName
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]' ) E
INNER JOIN Sql_Table A
ON a.id=e.id

- Mangal Pardeshi.

Some Useful Views And SPs in Sql Server.

Here is the good collection of views and Store Procedure which can be very useful.
Applicable for Sql Server 2005 and +

VIEWS : To use all the views you just need to do SELECT * FROM VIEW_NAME
e.g SELECT * FROM sys.databases

• sys.databases : Lists all the databases in Sql Server.
• sys.tables : Lists all the Tables in the database.
• sys.views : Lists all the views in the database.
• sys.procedures : Lists all the Procedures in the database.
• sys.triggers : Lists all the Triggers in the database.

• sys.columns : Lists all the columns of tables in database.
• sys.syscolumns : Lists all the columns in database including of those SP.
• sys.key_constraints : Lists all primary key or unique constraints in database. For primary key TYPE = 'PK' and for unique keys TYPE = 'UQ'
• sys.check_constraints : Lists all the Check Constraints in the database.
• sys.default_constraints : Lists all the Default Constarints in the database.
• sys.foreign_keys : Lists all the Foreign Keys in the database.

• sys.syslogins : Lists all the login names in server.
• sys.sql_logins : Lists all the Sql Authentication Logins in server.
• sys.sysusers : Lists all the users in database.

-- I'll Add some more very soon.

- Mangal Pardeshi.

Sql Server Connection Issues - Msg 18456

Now I'll try to help you understand the connection issues with Sql Server.
Today we will focus on the error

"Msg 18456, Level 14, State 1, Server , Line 1"
"Login failed for user ''."

This error is nothing but an authentication failure that involves a bad password or user name. So whenever you get a similar error be sure that is somthing related to user name or password.
And key to solve the issue is STATE number you get in the error.

• 2 and 5 : Invalid userid.
• 6 : Attempt to use a Windows login name with SQL Authentication.
• 7 : Login disabled and password mismatch.
• 8 : Password mismatch.
• 9 : Invalid password.
• 11 and 12 : Valid login but server access failure.
• 13 : SQL Server service paused.
• 16 : User don't have permission to log into Server.
• 18 : Change password required.

And as it seems you get the error Msg 18456 when you trying to connect Sql server in Sql Authentication.

Some points I like to add....

State 23 : Is one of the very rare errors, normaly happens when you try to connect to server when it is shutting down.

States 11 and 12 : When the Windows user trying to access the server and he doesn't have rights. Chances are you are on Windows Vista :).

- Mangal Pardeshi.

Date Formats In SQL SERVER.

In this post I will try to explain how to convert date in required format.

As we know by default (U.S. English) Sql Server shows dates in mdy.
i.e.
2008-11-22 14:02:12.513
OR
11/22/2008 2:02:00 PM
And so many times you wants the date in dd/mm/yy or dd Mon yyyy or any other format you want.

The function used by sql server to convert the dates in required format is CONVERT. Which I personaly find more easy to use compare to to_date used by Oracle or other fuctions used by other DBMS.

Syntax for the CONVERT is
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Where data_type can be either string datatype (char/ varchar) or datetime.
And Style in nothing but numbers in 1st 2 columns in following table .




1. Now for example If I want the date in "mon dd yyyy"
I will choose the style as 100 (from the above table)

SELECT CONVERT( varchar(50), date_column, 100)
FROM table_name
go;

e.g.
SELECT CONVERT(varchar(50), GETDATE(), 100)
GO
/* output
Nov 22 2008 2:21PM
*/
2. Or if I want mm/dd/yyyy I will select style 101

SELECT CONVERT(varchar(50), GETDATE(), 101)
GO
/* output
11/22/2008
*/

Now if instead of 2008 if I want only 08 I will select style from 1st column instead of column 2 which is style 1

SELECT CONVERT(varchar(50), GETDATE(), 1)
GO
/* output
11/22/08
*/
So if you want the century part in year (2008, 2009)select style from column 2 (100, 101, 102) or if you want only 2 digits of year (08, 09) select style from 1st column (1, 2, 3).

For more on CONVERT see
CAST and CONVERT (Transact-SQL) Books Online


- Mangal Pardeshi.

Difference between ROW_NUMBER, RANK and DENSE_RANK

What is the Difference between ROW_NUMBER, RANK and DENSE_RANK? Which one to use?
This is very common question in the minds of SQL newbie's.
Lets take 1 simple example to understand the difference between 3.

First lets create some sample data :

-- create table CREATE TABLE Salaries ( Names VARCHAR(1), SalarY INT ) GO -- insert data INSERT INTO Salaries SELECT 'A',5000 UNION ALL SELECT 'B',5000 UNION ALL SELECT 'C',3000 UNION ALL SELECT 'D',4000 UNION ALL SELECT 'E',6000 UNION ALL SELECT 'F',10000 GO -- Test the data SELECT Names, Salary FROM Salaries

Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I'll write a query like this :

SELECT names         , salary         ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER         ,rank () OVER (ORDER BY salary DESC) as RANK         ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK FROM salaries

>>Output

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

Interesting Names in the result are employee A, B and D. 
Row_number assign different number to them.
Rank and Dense_rank both assign same rank to A and B.
But interesting thing is what RANK and DENSE_RANK assign to next row?
Rank assign 5 to the next row, while dense_rank assign 4.

The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

Mangal Pardeshi

SQL MVP

Oracle Database Software

Download from following links
Oracle Database 11g Release 1 (11.1.0.6.0)

Oracle Database 10g Release 1

Oracle9i Database

Mangal Pardeshi

SQL MVP

Microsoft SQL Server Trial Download Links

Download the SQL Server 2008/2005 180-day Trial Software.
Click on the respective links.

Microsoft® SQL Server® 2008 Enterprise Evaluation: Trial

Microsoft SQL Server 2005 Enterprise Evaluation Edition: Trial



Mangal Pardeshi

SQL MVP