SQL LEARNERS

Tuesday, 29 May 2012

DateTime Formats

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY ¹ HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM ¹
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 ¹	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 ¹
DD Mon YYYY ¹	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 ¹
Mon DD, YY ¹	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 ¹
Mon DD, YYYY ¹	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 ¹
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) ¹	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM ¹
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) ¹	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 ¹
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 ¹	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM ¹
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Send Html Format Table in a Mail

CREATE TABLE #EqualData
(
Centerid [int],
Centername [VARCHAR] (50),
Warehouse FLOAT,
BulkLoad FLOAT,
Difference FLOAT,
Transactiondate datetime
)

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

Select Centerid,CAst(Warehouse as varchar) as Warehouse ,Centername,
cast(BulkLoad as varchar) as BulkLoad,Replace(Cast(Difference as Varchar),'-','') as Difference ,
Transactiondate FROM #EqualData

SET @xml = CAST(( SELECT [Centerid] AS 'td','',[Centername] AS 'td','',
[Warehouse] AS 'td','', BulkLoad AS 'td','', Difference as 'td','', Transactiondate as 'td'
FROM #EqualData ORDER BY Centerid
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>BulkLoad Difference</H3>
<table border = 1>
<tr>
<th> Centerid </th> <th> Centername </th> <th> Warehouse </th> <th> BulkLoad </th> <th> Difference </th> <th> Transactiondate </th> </tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'test',
@recipients='V.Vinoth@medallit.in' ,
@subject ='Amount Difference in BulkLoad' ,
@body = @body,
@body_format ='HTML' ;

Thursday, 19 April 2012

To Find Columnname in all the table in Database

SELECT c.name AS ColName, t.name AS TableName FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name='CENTERID'

Saturday, 4 February 2012

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

To KILL All the process in the current databases

ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE mydb SET MULTI_USER
GO

Tuesday, 20 December 2011

Concat string Example using CTE

WITH cte(Centerid,PatientId,patientName,RefDocName,PhoneNo ,visitid,VisitDate,StudyName,StudyState) as (
Select
       P.CenterId,
       p.externalID as PatientId,
        P.FirstName as patientName,
        R.FirstName as RefDocName,
        P.Phone1 as PhoneNo,
        V.visitid,
        V.VisitDate,
        bm.billingname as StudyName,
        CASE when s.StudyState=4 THEN 'Inprogress'
             when s.StudyState=3 THEN 'Completed' End as StudyState
from TblPatient

as P JOIN TblVisits as V On V.Patientid=P.patientid and V.CenterId=P.CenterId
JOIN TblStudy as S On S.Visitid=V.visitid and S.Centerid=V.Centerid
JOIN TblBillingMaster as BM On BM.Billingid=S.Studytypeid and Bm.CenterId=S.CenterId
JOIN tblReferralDoctor as R ON R.ReferalID=v.ReferalID and R.CenterId=V.CenterId

Where P.Obsolete=0 and V.Obsolete=0 AND S.StudyState In(3,4) and bm.ModalityTypeID!=12 )



SELECT CenterId,PatientId,patientName,RefDocName,PhoneNo,visitid,VisitDate,StudyState as Status, STUFF((SELECT ',' + b.StudyName from cte b Where a.Centerid=B.Centerid and a.visitid=b.visitid FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,1, '') AS StudyName
FROM cte a
    GROUP BY    a.CenterId,a.PatientId,a.patientName,a.RefDocName,a.PhoneNo,a.visitid,a.VisitDate,a.StudyState

Thursday, 15 December 2011

Delete Duplicate Rows Example

Delete A FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY PatientId ORDER BY LASTMODIFIEDDATE DESC) RANK1,PatientId
FROM TblPatient WHERE CenterId = 10 ) A WHERE RANK1 > 1