Tuesday, March 16, 2010

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































































































































































































Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1

HH:MIAM (or PM)		 Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +


milliseconds		 SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical

(with milliseconds)		 SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM










































































































































Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]

SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]

 99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]

 1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]		 08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9)
AS [Month DD, YYYY]		 July 04, 2006 1
Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS
[Month YYYY]
 February 2006 1
DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS
[DD Month]		 11 September 1
Month DD 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS
[Month DD]		 September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' '
+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]		 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' '
+ CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]		 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]		 12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]		 92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS
[Mon-YY]		 Sep-02 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS
[Mon-YYYY]		 Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] 25-Dec-2005 1




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
Posted by at No comments:

Tuesday, March 9, 2010

You can force the DB offline and drop connections

"sp_who" is using to find number of connection to data base

You can force the DB offline and drop connections with :

EXEC sp_dboption N'yourDatabase', N'offline', N'true'

Or

ALTER DATABASE [yourDatabase] SET OFFLINE WITH
ROLLBACK AFTER 60 SECONDS

OR

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End

in reference to: ahamed travel agencies cochin - Google Search (view on Google Sidewiki)
Posted by at No comments:

Sunday, March 7, 2010

OUTER JOIN

There are three different Outer Join methods
LEFT OUTER JOIN.
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it return NULL values
http://www.pinaldave.com/bimg/march09ug/left%20join.jpgs .

in reference to:

"This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values."
- SQL SERVER – Introduction to JOINs – Basic of JOINs « Journey to SQL Authority with Pinal Dave (view on Google Sidewiki)

Posted by at No comments:

INNER JOIN

Posted by at No comments:

Count number of tables in a SQL Server database

USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

in reference to:

"USE YOURDBNAMESELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'"
- Count number of tables in a SQL Server database | Sql Server Blog (view on Google Sidewiki)

Posted by at No comments:

Saturday, March 6, 2010

SQL Important QUERIES

SQL Important QUERIES
Posted by at No comments:
Subscribe to: Posts (Atom)