::ask4sql::

Date/Time Conversions Using SQL Server

2007-03-05T07:44:00.000-08:00

Hello friends

here is the list of all sql date time formats,

DATE FORMATS
Format #	Query (current date: 12/30/2006)	Sample
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06.12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006.12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30

TIME FORMATS
8 or 108	select convert(varchar, getdate(), 8)	00:38:54
9 or 109	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
14 or 114	select convert(varchar, getdate(), 14)	00:38:54:840

Enjoy!

How to get lenght of data in Text,NText and Image columns

2007-02-27T22:23:00.000-08:00

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types .

So to solve this problem
SQL Server has a DATALENGTH() function.

For e.g.

SELECT name, LEN(packagedata) FROM dbo.sysdtspackages

this query will give error.

the correct query is

SELECT name, DATALENGTH(packagedata) FROM dbo.sysdtspackages

Enjoy!

SQL server Identity Column information

2007-02-25T21:40:00.000-08:00

Hi,

SQL Server 2000 has three functions that return IDENTITY information. The result of each of these three functions is dependent on three factors:

1. The session scope (which connection produced the IDENTITY value?)

2. The table scope (which table produced the IDENTITY value?)

3. The statement scope (where is the statement that produced the IDENTITY value?)

(SQL Statements that are contained in the same batch, stored procedure, or trigger are considered to be in the same scope.
So, if I call an INSERT that fires a trigger, I have two different scopes: scope 1 is inside the batch that called the INSERT, and scope 2 is inside the trigger.)

1. SELECT @@IDENTITY
This is everyone's favorite function, unchanged from earlier versions of SQL Server. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

2. SELECT IDENT_CURRENT ('tablename')
This new function returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

3. SELECT SCOPE_IDENTITY ()
This new function returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless Of the table that produced the value.

Thank you

How to debug sql stored procedures?

2007-02-25T01:30:00.000-08:00

Hello,

i have found a nice link for answer.

http://www.15seconds.com/issue/050106.htm

hope you will find the answer.

Thank you

Remove Noise words from Full-text indexing in SQL server

2007-02-22T20:42:00.000-08:00

How to remove noise words from SQL server?

• Check the language of your sql using following syntax

select @@languagee.g. result : us_english

• Now here you have “English language” so…• Remove required word from following files…

1. the noise.* files under your \WINNT\System32 directory

2. noise.enu (US_English) and noise.eng (UK_English) noise word files under your SQL Server default folder of \FTDATA\SQLServer\Config.

• After making these changes and before saving the file changes, you must stop the “Microsoft Search“ service, before you can save the FTDATA noise word files. When your modifications are completed, you must run a Full Population.

Enjoy.

Function to split string in SQL

2007-02-22T01:55:00.000-08:00

Hello

I have found one interesting function using which you can split the text with some character like ',' or any other character.

CREATE function [dbo].[Split] (@String nvarchar(4000), @Delimiter char(1))
Returns @Results Table (Items nvarchar(4000))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)

Select @Index = 1
If @String Is NULL Return

While @Index != 0
Begin
Select @Index = CharIndex(@Delimiter, @String)
If @Index <> 0

Select @Slice = left(@String, @Index - 1)

else

Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break

End
Return
End

Now how to use this function

select * from dbo.Split('a,b,c,d',',')

this will give following result

Items
-----
a
b
c
d

How this function can make your life easy!

Suppose if you want to insert more than one row in a table depending on above function result

you can just do as follows

insert into Table_Name (Column_Name)
select * from dbo.Split('a,b,c,d',',')

Enjoy