Feeds:
Posts
Comments

Archive for the ‘Datetime’ Category

I came across this query when I was automating one of my services for a customer and it had to run on first Sunday of every month. I already had this solution earlier and utilized it in many places, but as my customer is using SQL Server 2012, I would like to write this script in a different way with less no of codes. So I started scripting and finally developed it using EOMONTH function. (A new function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-05')
INSERT INTO tbl_Sample VALUES (2,'2013-02-01')
INSERT INTO tbl_Sample VALUES (3,'2013-05-05')
INSERT INTO tbl_Sample VALUES (4,'2013-07-20')
INSERT INTO tbl_Sample VALUES (5,'2013-08-28')

GO

SOLUTION 1 : Using EOMONTH
Given below is the script.

USE tempdb
GO
SELECT DATEADD(DAY,8-
 DATEPART(WEEKDAY,DATEADD(DAY,1,EOMONTH([Date])))
,EOMONTH([Date])) AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

firstsundayofthemonth.1.1

SOLUTION 2 : Using Traditional Method
Given below is the script.

USE tempdb
GO
SELECT CONVERT(DATE,DATEADD(DAY,8-
 DATEPART(WEEKDAY,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1,0))
,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1, -1)))
AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

firstsundayofthemonth.1.1

Advertisements

Read Full Post »

I developed a utility in SQL Server 2008 and recently upgraded to SQL Server 2012. The job of this utility is to receive the data from external sources, validate the data and insert the data into respective fields of a table for further processing. I used one of the famous SQL functions ISDATE() to validate the date fields in this utility and it was working perfect. Recently, I began to receive some errors. After debugging, I found that ISDATE() is not compatible with datetime2 datatype.

Let me demonstrate the error before heading towards the solution.

--This script is compatible with SQL Server 2008 and above.
DECLARE @Datetime2 AS DATETIME2
SET @Datetime2=GETDATE()

SELECT ISDATE(@Datetime2)  AS [Validate_Date]
GO
--OUTPUT

Msg 8116, Level 16, State 1, Line 4
Argument data type datetime2 is invalid for argument 1 of isdate function.

isdate_with_datetime2.1.1

Opsssssssssssss, I cannot validate the datetime2 datatype using ISDATE() function. So what is the solution ?

SOLUTION :
I developed the given below solution using TRY_CONVERT() (One of the new conversion functions shipped with SQL Server 2012)

Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @Datetime2 AS DATETIME2
DECLARE @Varchar AS VARCHAR(10)
SET @Datetime2=GETDATE()
SET @Varchar='raresql'

SELECT
IIF(TRY_CONVERT(DATETIME,@Datetime2) is null ,0,1) AS [Validate_Date]
,IIF(TRY_CONVERT(DATETIME,@Varchar) is null ,0,1) AS [Validate_Date]
GO
--OUTPUT

isdate_with_datetime2.1.2

Do share if you came across this issue and resolved it differently.

Read Full Post »

Today is month end (30 September 2013) and I was checking my general checklist for month end closing procedure. I saw one of my scripts that I had written earlier regarding how to check whether a date is month end date or not. So, I thought of re-writing this script even much shorter & efficient than my earlier script using EOMONTH .

Let me create a sample table to demonstrate.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-03-31')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-12-31')
GO

SOLUTION 1 :
Given below is the script that we usually use in SQL Server 2005/2008 to check whether the date is End of month date or not.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT [ID],[Date],
(CASE WHEN
CONVERT(varchar(11)
,DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date])+1, -1),120)=[Date]
THEN 'Month End' ELSE 'Not a Month End' END) As [Result]
FROM tbl_Sample
GO

isdate is a mont end date.1.1

SOLUTION 2 :
Given below is the script, that I developed using new functions (EOMONTH & IIF) shipped in SQL Server 2012 to check whether the date is End of month date or not. Due to these functions, we can reduce the script size and improve the performance.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT [ID],[Date],
IIF(EOMONTH([Date])=[Date],'Month End','Not a Month End')
As [Result]
FROM tbl_Sample
GO
--OUTPUT

isdate is a mont end date.1.2

CONCLUSION :
As you can see, both solutions have the same result set but Solution 2 is recommended because it is using only one function to check whether the date is month end date or not, instead of a lot of date functions.

Read Full Post »

In my earlier article, I wrote a solution how to remove milliseconds from datetime. After that I tried to remove the seconds and milliseconds from datetime using same method but it gave me errors. So I went to the drawing board and started doing some research and finally realized that I can use the same methods with little modifications.
Given below are the two methods that we can use to remove milliseconds and seconds from datetime.

METHOD 1 :
In this method, we will use Convert function to convert date time to varchar and then remove the seconds and milliseconds from it and then convert it back to datetime.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with seconds and millisconds]
,CONVERT(DATETIME,CONVERT(VARCHAR(13),@datetime,120)+ ':00')
AS [Datetime without seconds & millisconds]
GO
--OUTPUT

remove seconds and milliseconds.1.1

METHOD 2 :
In this method, we will first FORMAT the datetime value to a defined format (truncate seconds and milliseconds) and then convert it back to datetime data type using CAST function.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with seconds and millisconds]
,CAST(FORMAT(@datetime,'yyyy-MM-dd HH:0') AS datetime)
AS [Datetime without seconds & millisconds]
GO
--OUTPUT

remove seconds and milliseconds.1.2

Read Full Post »

Recently, I came across a query where I needed to calculate the first and last day of the year. Fortunately, we do have the solution using DATEADD & DATEDIFF function. But the problem is we need to use these functions multiple times to achieve the first and last day of the year. So I went to the drawing board and started reducing the formula and finally I succeeded. Given below is the script to calculate the first and last day of the year using Format function (shipped with SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
SELECT DATEADD(YEAR,-1,FORMAT(Getdate(),'yyyy')) As [Result]
, 'First Day of Previous Year' As [Type]

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(GETDATE(),'yyyy'))
, 'Last Day of Previous Year'

UNION ALL
SELECT FORMAT(GETDATE(),'yyyy')
, 'First Day of Current Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,1,GETDATE()),'yyyy'))
, 'Last Day of Current Year'

UNION ALL
SELECT DATEADD(YEAR,1,FORMAT(Getdate(),'yyyy'))
, 'First Day of Previous Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,2,GETDATE()),'yyyy'))
, 'Last Day of Previous Year'
--OUTPUT

first_day_and_last_day_of_year.1.1

Read Full Post »