Wednesday, November 23, 2011

Calculate difference of Time only between two dates while discarding difference of dates in SQL Server/ extract time and calculate difference between two dates in SQL Server


DECLARE @DateTimeOne DATETIME
DECLARE @DateTimeTwo DATETIME

SET @DateTimeOne = '1900-01-01 10:41:09.250'
SET @DateTimeTwo = GETDATE()


--hour = hh
--minute = mi, n
--second = ss, s
--millisecond = ms


SELECT  DATEDIFF(mi,SUBSTRING(CONVERT(VARCHAR,@DateTimeOne, 121), 12, 12)
,SUBSTRING(CONVERT(VARCHAR,@DateTimeTwo, 121), 12, 12) ) MinuteDifferenceinDate

Friday, June 24, 2011

Working with Email Address in SQL Server ( Validate Email Address )

at times working with email addresses in sql server becomes difficult so
here is a UDF you can use to validate the email address

It will validate mentioned below list

No Spaces are allowed
email cant start with '@'
email cant end at '.'
@must be in email and only once
domain name should be at the end and must be at least two characters
email cant habe patterns like '..' or '.@'








CREATE FUNCTION [dbo].[validateEmailAddress]
(
@EmailAddress nVARCHAR(4000)
)
RETURNS TINYINT AS
BEGIN


DECLARE @Result TINYINT
SELECT @Result =
CASE WHEN
CHARINDEX(' ',LTRIM(RTRIM(@EmailAddress))) = 0
AND LEFT(LTRIM(@EmailAddress),1) <> '@'
AND RIGHT(RTRIM(@EmailAddress),1) <> '.'
AND CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 1
AND LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 3
AND (CHARINDEX('.@',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0)
THEN 1 ELSE  0 END

RETURN @Result

END



Example:- 

it will return 1 for correct email address and 0 for incorrect 
:
SELECT  dbo.validateEmailAddress('myemail@mydomain.com')  -- Correct

SELECT  dbo.validateEmailAddress('@myemail@m  domain.com')  -- Incorrect 


Tuesday, January 11, 2011

Best Practices for using DATETIME column in sql server 2005 and 2008

Best Practices for using DATETIME column in sql server 2005 and 2008

In production environment we have seen that there are certain times when you make a good query by applying best practices but you don’t get good results at the time of sorting.
This might happen because of DATETIME columns which are mostly used for sorting purpose and indexes for these columns do not give you appropriate results.
Mentioned below is tip for using DATETIME column which can enhance the speed of your query while maintaining same number of indexes.
This technique requires mentioned below changes in your table: - 
  1. One Persisted Computed Column (BIGINT) which will store DATETIME in integer format by removing special symbols from DATETIME
  2. This computed column will be added in the covering index
  3. Query will use this column instead of DATETIME column



Example :
/* Create Sample Table */
CREATE TABLE [dbo].[MyTable](
      [C1] [int] IDENTITY(1,1) NOT NULL,
      [C2] [DATETIME] NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
      [C1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Data Insert */
SET IDENTITY_INSERT [dbo].[MyTable] ON
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (1, CAST(0x00009E5E00000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (2, CAST(0x00009E5F00000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (3, CAST(0x00009E6000000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (4, CAST(0x00009E6100000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (5, CAST(0x00009E7D00000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (6, CAST(0x00009E7E00000000 AS DATETIME))
SET IDENTITY_INSERT [dbo].[MyTable] OFF

/* Query */
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
SET         @D1 = GETDATE()-10
SET         @D2 = GETDATE()
SELECT      C1
            ,C2
FROM  MyTable
WHERE C2 BETWEEN @D1 AND @D2

/* Change in Table Add New Column that will store DATETIME in Integer Format */
ALTER TABLE MyTable
ADD [C3]  AS (CONVERT([bigint],CONVERT([varchar],[C2],(112))+replace(CONVERT([varchar],[C2],(114)),':',''),0)) PERSISTED

/* Existing Index for query */
CREATE NONCLUSTERED INDEX [Index_For_C2] ON MyTable
(
      [C2] ASC,
      [C1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
 IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/* Add New Column to Existing Index for query */
CREATE NONCLUSTERED INDEX [Index_For_C2] ON MyTable
(
      [C3] ASC,
      [C2] ASC,
      [C1] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/*Updated Query using Integer Column instead of DATETIME having same result set*/
DECLARE @D1 BIGINT
DECLARE @D2 BIGINT
SET         @D1 = (CONVERT([bigint],CONVERT([varchar],GETDATE()-10,(112))+replace(CONVERT([varchar],GETDATE()-10,(114)),':',''),0))
SET         @D2 = (CONVERT([bigint],CONVERT([varchar],GETDATE(),(112))+replace(CONVERT([varchar],GETDATE(),(114)),':',''),0))
SELECT      C1
            ,C2
FROM  MyTable
WHERE C3 BETWEEN @D1 AND @D2


The above query will now use the new updated index and will be much faster as it will get records by using an integer column instead of DATETIME column.