Feeds:
Posts
Comments

Archive for September, 2012

How to get hour, minute & second from date time

Posted in Solutions, SQL SERVER, tagged , on September 30, 2012| Leave a Comment »

I have been asked this question “How to get hour, minute and second from date time?” from a developer last week.

Let me explain it with a simple example.

Declare @DateTime As Datetime
Set @DateTime = Getdate()
Select @DateTime as [DateTime]

Select DatePart(Hour,@Datetime) as [Hour]
Select DatePart(Minute,@Datetime) as [Minutes]
Select DatePart(Second,@Datetime) as [Seconds]

--Result
DateTime
-----------------------
2012-09-25 18:12:45.420

(1 row(s) affected)

Hour
-----------
18

(1 row(s) affected)

Minute
-----------
12

(1 row(s) affected)

Second
-----------
45

(1 row(s) affected)

Read Full Post »

Generating INSERT statements in SQL Server 2012

Posted in Solutions, SQL SERVER, tagged , on September 30, 2012| 6 Comments »

Recently, I wrote an article on “Generating Insert statements, data & schema using SQL Server Publishing Wizard”. It is a very nice tool to generate insert into statement from table data.

In SQL server 2012, the feature of this tool plus additional features are provided. Lets proceed step by step to generate insert into statement from table data in SQL server 2012.

Step 1 :

In the first step, you need to right click on Databases >> Tasks >> Generate Scripts…

Step 2 :

This is the welcome screen. If you don’t need this screen again and again you can check on “Do not show this page again”.

Step 3 :

In this step, you need to generate the script for one object or all objects available in the database. You can further drill down if you need to generate any table. For example :

Setup 4 :

The next step is to define the script location, clip board or in a new window. In the same step, if you click on Advanced button, it will give you further control on scripting.

This is the advance screen, in this screen you can choose whether you need data, schema or data and schema scripts.’

But if you need only Insert into statement from table data, you must select data only. By default it is schema only.

It also allows you to select the SQL version.

Step 5 :

In this step it will display the summary of your selection. If you need you can go back and change the settings.

Step 6 :

In this step, it will display the result.

Read Full Post »

Generating Insert statements, data & schema using SQL Server Publishing Wizard

Posted in Solutions, SQL SERVER, tagged , , , on September 25, 2012| 3 Comments »

Few months back, I published an article namely How to generate Insert Statements from Table Data using SQL Server. In this article, I have developed a tool that can help you to generate insert into statement from table data.

Today, we will discuss one more tool namely “Microsoft SQL Server Database Publishing Wizard” that will help you not only to generate the insert into statement but also you can generate script for all objects in SQL server. You can download this tool from here

Lets proceed step by step to generate it.

Step 1 :

Run this utility and this will be the welcome screen.

Step 2 :

Once you click on next, you need to give the server name and its credentials. You can also provide it as a connection string.

Step 3 :
In this step you need to select the database. There is no multi selection option, so you need to select one database at a time.
Also, if you check “script all objects in the selected database” then in the next step it will not give you the option to select the objects.

Step 4 :
If you did not check “script all objects in the selected database” then this tool will allow you to select multiple object of the SQL server. ‘Select All’ option is also available.

Step 5 :
In the previous step, I selected the ‘Tables’ so in this step, I need to define which table(s) should be scripted.

Step 6 :
In this step , you can define the path where the script should be generated or you can define the server where the script should be deployed.

Step 7 :
In this step, you need to define your requirement. Select one of the options ‘Data only’ or ‘Schema only’ or ‘Schema & data’.

Step 8 :
In this step, you can see the summary of your selection. If it is not correct, you can go back and change the selection.

Step 9 :
In this step, you can view the result of process, whether it is executed successfully or not.

Step 10 :
Now, the script has been generated successfully and you can get the file from your defined location.
For Example:

/****** Object:  Table [HumanResources].[Department]    Script Date: 09/24/2012 10:48:11 ******/
DELETE FROM [HumanResources].[Department]
GO
/****** Object:  Table [HumanResources].[Department]    Script Date: 09/24/2012 10:48:11 ******/
SET IDENTITY_INSERT [HumanResources].[Department] ON
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (1, N'Engineering', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (2, N'Tool Design', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (6, N'Research and Development', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (7, N'Production', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (8, N'Production Control', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (9, N'Human Resources', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (10, N'Finance', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (11, N'Information Services', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (12, N'Document Control', N'Quality Assurance', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (13, N'Quality Assurance', N'Quality Assurance', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (15, N'Shipping and Receiving', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate])
VALUES (16, N'Executive', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))

SET IDENTITY_INSERT [HumanResources].[Department] OFF

Read Full Post »

SQL Server 2012 – String Function – FORMAT

Posted in Functions, SQL SERVER, tagged , , , , , on September 19, 2012| 19 Comments »

Format function is one the important functions in SQL Server 2012. This function is very useful to format data in a specified format.

Today, we will discuss each and every aspect of this function. Also we will discuss the need/importance of this function in SQL server.

By using this function, we do not need to do a lot of scripting to format the data.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

FORMAT ( value, format [, culture ] )

Parameters :

@value : Data to be converted in other format. (Mandatory)
@format : To define a format in nvarchar datatype. (Mandatory)
@culture : To define a culture. By default, it picks up culture from session. (Optional)

Purpose :
This function converts value from one format to another format by using culture. If culture is not available it will pick up the culture from the session. It will return value either in nvarchar data type or NULL.

Let me explain this with simple examples.

Example-1 : FORMAT – DATE WITH DEFAULT CULTURE

DECLARE @Date AS datetime=Getdate()

Select @Date AS [DATE]
SELECT FORMAT (@Date, 'dd/MM/yyyy') as [Result (dd/MM/yyyy)]
SELECT FORMAT (@Date, 'MM/dd/yyyy') as [Result (MM/dd/yyyy)]
SELECT FORMAT (@Date, 'yyyy/MM/dd') as [Result (yyyy/MM/dd)]

Example-2 : FORMAT – TIME WITH DEFAULT CULTURE

DECLARE @TIME AS DATETIME=Getdate()

Select @TIME AS [TIME]
SELECT FORMAT (@TIME, 'h:mm:ss tt') as [Result (h:mm:ss tt)]
SELECT FORMAT (@TIME, 'hh:mm:ss tt') as [Result (hh:mm:ss tt)]
SELECT FORMAT (@TIME, 'h:mm:ss') as [Result (h:mm:ss)]
SELECT FORMAT (@TIME, 'hh:mm:ss') as [Result (hh:mm:ss)]

Example-3 : FORMAT – NUMBER WITH DEFAULT CULTURE

DECLARE @NUMBER AS NUMERIC(18,4)=12345.789

Select @NUMBER as [NUMBER]
SELECT FORMAT (@NUMBER, '####0.00') as [Result (####0.00)]
SELECT FORMAT (@NUMBER, '####0.000') as [Result (####0.000)]
SELECT FORMAT (@NUMBER, '##,##0.00') as [Result (##,##0.00)]
SELECT FORMAT (@NUMBER, '#') as [Result (#)]

Example-4 : FORMAT – CURRENCY WITH DEFAULT CULTURE

DECLARE @CURRENCY MONEY = '24500.50';
SELECT FORMAT (@CURRENCY, 'C') AS  [RESULT]

Example-5 : FORMAT – DATE & TIME WITH MULTIPLE CULTURE

DECLARE @Date AS datetime=Getdate()

Select @Date as [DATE]
SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')
AS [English Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','de-DE')
AS [German Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ja-JP')
AS [Japanese Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ar-SA')
AS [Arabic Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ur-PK')
AS [Urdu Culture]

Example-6 : FORMAT – CURRENCY WITH MULTIPLE CULTURE

DECLARE @CURRENCY MONEY = '24500.50';
SELECT FORMAT (@CURRENCY, 'C','en-US' ) AS [English Culture]
SELECT FORMAT (@CURRENCY, 'C','de-DE' ) AS [German Culture]
SELECT FORMAT (@CURRENCY, 'C','ja-JP' ) AS [Japanese Culture]
SELECT FORMAT (@CURRENCY, 'C','ar-SA' ) AS [Arabic Culture]
SELECT FORMAT (@CURRENCY, 'C', 'ur-PK' ) AS [Urdu Culture]

Example-7 : FORMAT – VALUE BY USING LANGUAGE INSTEAD OF CULTURE

SET LANGUAGE 'ENGLISH'

SELECT FORMAT (245000, 'C') AS [Currency]
SELECT FORMAT (GETDATE(),'D') AS [DATE]
SELECT FORMAT (24500.282, '#,###,###0.00') AS [Currency]

Reference : MSDN

Read Full Post »

SQL Server 2012 – String Function – CONCAT

Posted in Functions, SQL SERVER, tagged , , , on September 11, 2012| 6 Comments »

SQL Server 2012 has introduced a new string function namely “CONCAT”. It is a very handy & important function to deal with the string concatenation.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Parameters :
@string_value1 : First string value to concatenate with other strings.
@string_value2 : Second string value to concatenate with other strings.
It will go up to (n) Numbers of strings.

Purpose :
It concatenates two or more than two strings and returns as a single string value. Minimum we need to pass two strings to concatenate otherwise it will generate an error. It implicitly converts parameters to string. If The parameters are NULL it treats it as empty string of varchar(1).

Let me explain this with simple examples.

Example-1 : CONCAT – String values

Select Concat('SQL Server ','2012') as [Result]

Result
---------------
SQL Server 2012

(1 row(s) affected)

Example-2 : CONCAT – Integer values
In this example, “CONCAT” function will implicitly convert integer data type to string data type and concatenate it.

Select CONCAT(1,2) as [Result]

Result
------------------------
12

(1 row(s) affected)

Example-3 : CONCAT – Numeric values
In this example, “CONCAT” function will implicitly convert numeric data type to string data type and concatenate it.

Select CONCAT(12.34,56.78) as [Result]

Result
----------------------------------------------------------------------------------
12.3456.78

(1 row(s) affected)

Example-4 : CONCAT – Less than 2 strings 

Select Concat('SQL Server ') as [Result]

Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Example-5 : CONCAT – Passed NULL as parameter
It converts NULL to empty string of varchar(1) and return it.

Select Concat('SQL Server ',NULL) as [Result]

Result
---------------
SQL Server

(1 row(s) affected)

Example-6 : CONCAT – Passed NULL Values from Database
Before in SQL server 2012, we had to use ISNULL to convert NULL to empty string & concatenate.
But this function resolves this issue. It automatically converts NULL to empty string. Lets use Adventure database to test it.

Select top 10 [FirstName] ,  [MiddleName] , [LastName]
,[FirstName] +  [MiddleName] + [LastName] as [Full Name]
from [Person].[Person]

In the above example, due to the NULL values in the middle name column SQL failed to concatenate it and return NULL as [Full Name]

Lets try to do the same with “CONCAT” function.

Select top 10 [FirstName] ,  [MiddleName] , [LastName]
,Concat([FirstName] , [MiddleName] , [LastName]) as [Full Name]
from [Person].[Person]

In the above example, CONCAT function replaced NULL with emprty string and concatenates the FULL Name successfully.

Reference : MSDN

Read Full Post »

How to get First day of a month in SQL SERVER 2012 (FOMONTH)

Posted in Solutions, SQL SERVER, tagged on September 10, 2012| Leave a Comment »

“How to calculate the first date of the current, previous & next month” ? I have seen this question many times in different blogs and there are multiple approaches that we have been following to find it.

For Example

--SQL Server 2005/2008
DECLARE @DATE DATETIME
SET @DATE='2012-09-10'

--First date of the Previous Month
SELECT CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)-1,0),120) AS [Previous Month]

--First date of the Current Month
SELECT CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE),0),120) AS [Current Month]

--First date of the Next Month
SELECT CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0),120) AS [Next Month]

Previous Month
--------------
2012-08-01

(1 row(s) affected)

Current Month
-------------
2012-09-01

(1 row(s) affected)

Next Month
----------
2012-10-01

(1 row(s) affected)

In SQL Server 2012, we can calculate it with the help of a new built in function namely EOMONTH.

Example-1 :
In this example, I calculated the last date of the previous month and later added one more day to it to make the first day of the current month.

Declare @Date as varchar(10) ='2012-09-10'

--First date of the Current Month
Select DateAdd(day,1,EOMONTH(Getdate(),-1)) as [Current Month]

Current Month
-------------
2012-09-01

(1 row(s) affected)

Example-2 : FOMONTH
In this example, we will create a function FOMONTH and will use it same as EOMONTH in SQL server 2012.

CREATE Function [dbo].[FOMONTH]
(@start_date date, @month_to_add int=0)
Returns Date
AS
BEGIN
RETURN DATEADD(DAY,1,EOMONTH(@start_date,@month_to_add-1))
END
GO
--First day of the Previous Month
Select dbo.[FOMONTH] (Getdate(),-1)  AS [Previous Month]

--First day of the Current Month
Select dbo.[FOMONTH] ('2012-09-10',0) AS [Current Month]

--First day of the Next Month
Select dbo.[FOMONTH] (Getdate(),1)  AS [Next Month]

--First day of the Current Month WITH DEFAULT VALUE
Select dbo.[FOMONTH] (Getdate(),DEFAULT)  AS [Current Month]

Previous Month
--------------
2012-08-01

(1 row(s) affected)

Current Month
-------------
2012-09-01

(1 row(s) affected)

Next Month
----------
2012-10-01

(1 row(s) affected)

Current Month
-------------
2012-09-01

(1 row(s) affected)

Read Full Post »

SQL SERVER 2012 -Date and Time Function – EOMONTH

Posted in Functions, SQL SERVER, tagged , , , , , on September 10, 2012| 3 Comments »

“How to calculate the last date of the current, previous & next month” ? I have seen this question many times in different blogs and there are multiple approaches that we have been following to find it.
For Example :

--SQL Server 2005/2008
DECLARE @DATE DATETIME
SET @DATE='2012-09-10'

--Last date of the Previous Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE),0)),120) AS [Previous Month]

--Last date of the Current Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)),120) AS [Current Month]

--Last date of the Next Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+2,0)),120) AS [Next Month]

Previous Month
--------------
2012-08-31

(1 row(s) affected)

Current Month
-------------
2012-09-30

(1 row(s) affected)

Next Month
-----------
2012-10-31

(1 row(s) affected)

In SQL Server 2012, the solution came as a new built in function namely EOMONTH. Lets discuss this function syntax, parameters, purpose and examples in detail.

Syntax :

EOMONTH ( start_date [, month_to_add ] )

Parameters :
@start_date : A valid Date in varchar or date time data type (Mandatory)
@month_to_add : A valid integer for month range from 0-9999 (Optional). By default it is zero.

Purpose :
This function requires a valid date (Either in Datetime or varchar data type) as a parameter and returns last date of that month. There is an optional parameter called “@month_to_add”. By using this optional parameter, we can get the last date of other months also. It could be previous, next or any month.

Let me explain this with simple examples.

Example-1 : EOMONTH – valid start date as Date data type

Declare @Date as Date ='2012-09-01'

--Last date of the Previous Month
Select EOMONTH (@Date,-1) as [Previous Month]

--Last date of the current Month
Select EOMONTH (@Date) as [Current Month]

--Last date of the Next Month
Select EOMONTH (@Date,1) as [Next Month]

Previous Month
--------------
2012-08-31

(1 row(s) affected)

Current Month
-------------
2012-09-30

(1 row(s) affected)

Next Month
----------
2012-10-31

(1 row(s) affected)

Example-2 : EOMONTH – valid start date as varchar data type

Declare @Date as varchar(10) ='2012-09-01'

--Last date of the current Month
Select EOMONTH (@Date) as [Current Month]

Current Month
-------------
2012-09-30

(1 row(s) affected)

Example-3 : EOMONTH – Last Date of each month in the Current Year 

Declare @Date as Date ='2012-01-01'

--Last date of the Jan Month
Select EOMONTH (@Date) as [Jan]

--Last date of the Feb Month
Select EOMONTH (@Date,1) as [Feb]

--Last date of the Mar Month
Select EOMONTH (@Date,2) as [Mar]

--Last date of the Apr Month
Select EOMONTH (@Date,3) as [Apr]

--Last date of the May Month
Select EOMONTH (@Date,4) as [May]

--Last date of the Jun Month
Select EOMONTH (@Date,5) as [Jun]

--Last date of the Jul Month
Select EOMONTH (@Date,6) as [Jul]

--Last date of the Aug Month
Select EOMONTH (@Date,7) as [Aug]

--Last date of the Sep Month
Select EOMONTH (@Date,8) as [Sep]

--Last date of the Oct Month
Select EOMONTH (@Date,9) as [Oct]

--Last date of the Nov Month
Select EOMONTH (@Date,10) as [Nov]

--Last date of the Dec Month
Select EOMONTH (@Date,11) as [Dec]

Jan
----------
2012-01-31

(1 row(s) affected)

Feb
----------
2012-02-29

(1 row(s) affected)

Mar
----------
2012-03-31

(1 row(s) affected)

Apr
----------
2012-04-30

(1 row(s) affected)

May
----------
2012-05-31

(1 row(s) affected)

Jun
----------
2012-06-30

(1 row(s) affected)

Jul
----------
2012-07-31

(1 row(s) affected)

Aug
----------
2012-08-31

(1 row(s) affected)

Sep
----------
2012-09-30

(1 row(s) affected)

Oct
----------
2012-10-31

(1 row(s) affected)

Nov
----------
2012-11-30

(1 row(s) affected)

Dec
----------
2012-12-31

(1 row(s) affected)

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Date and Time Function – DATETIME2FROMPARTS

Posted in Functions, SQL SERVER, tagged , , , on September 7, 2012| Leave a Comment »

In my previous Posts, I discussed about DATETIMEFROMPARTS. Today, we will discuss one more function introduced in SQL SERVER 2012 namely “DATETIME2FROMPARTS“.

Let me explain its syntax, parameters, purpose and examples in detail.

Syntax :

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31. (Depends upon the total number of days in a month)
@Hour : A valid integer for Hour range from 0-23.
@Minutes : A valid integer for Minutes range from 0-59.
@Seconds: A valid integer for Seconds range from 0-59.
@Fractions : A valid integer for Fractions range from 0-9999999.
@Precision : A valid integer for Precision range from 0-7.

Purpose :
This function requires year, month, day, hour, minute, seconds, fractions & precision as a parameter (All parameters are mandatory) and returns a valid datetime2 as a result. If we pass any invalid date and time parts, it will generate an error. Also if we pass NULL values to any of its parameters except Precision parameter, it returns NULL value. If we pass NULL value to Precision parameter it will generate an error.
Let me explain this with simple examples.

Example-1 : DATETIME2FROMPARTS

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Select DATETIME2FROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,2)
as [RESULT]
--RESULT
2012-12-31 23:59:59.50

Example-2 : DATETIME2FROMPARTS- With Invalid Date and Time Parts
It will generate an error because only valid date and time parts are allowed as a parameter.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=25 --- Hour cannot be 25
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Select DATETIME2FROMPARTS (@Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,2)
as [RESULT]
--RESULT

Msg 289, Level 16, State 5, Line 23
Cannot construct data type datetime2, some of the arguments have values which are not valid.

Example-3 : DATETIME2FROMPARTS- With NULL Date and Time Parts except Precision parameter
If we pass NULL value to any of its parameter except Precision parameter, it will return NULL value

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=NULL ---Pass Seconds as NULL
Declare @Fraction as int=50
Select DATETIME2FROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,2)
as [RESULT]
--RESULT

Example-4 : DATETIME2FROMPARTS- @Precision as NULL value
If we pass NULL value to precision parameter of this function, it will generate an error.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=50
Declare @Fraction as int=50
Select DATETIME2FROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,NULL)
as [RESULT]
--Result

Msg 10760, Level 16, State 1, Line 23
Scale argument is not valid. Valid expressions for data type datetime2 scale argument are integer constants and integer constant expressions.

Example-5 : DATETIME2FROMPARTS- With Null and Invalid parameters
If we pass NULL and Invalid value to its parameter, it will return NULL as a result.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=25 ---Hour cannot exceed 23
Declare @Minute as int=59
Declare @Second as int=NULL ---Second parameter as NULL
Declare @Fraction as int=50
Select DATETIME2FROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,2)
as [Result]
--Result
NULL

Example-6: DATETIME2FROMPARTS- With missing Fraction if precision is defined
If precision is zero , we cannot define the fraction as well. It should be zero.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Select DATETIME2FROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,0) as [RESULT]
as [Result]
--Result

Msg 289, Level 16, State 5, Line 8
Cannot construct data type datetime2, some of the arguments have values which are not valid.

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Date and Time Function – SMALLDATETIMEFROMPARTS

Posted in Functions, SQL SERVER, tagged , , , on September 7, 2012| Leave a Comment »

In my previous Posts, I discussed about DATETIMEFROMPARTS function in SQL SERVER 2012. Today we will discuss one more important function introduced in SQL SERVER 2012 namely “SMALLDATETIMEFROMPARTS“.

Let me explain its syntax, parameters, purpose and examples in detail.

Syntax :

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31. (Depends upon the total number of days in a month)
@hour : A valid integer for the hour range from 0-23
@Minute : A valid integer for the minute range from 0-59
Purpose :
This function requires year, month, day, hour, minute as a parameter (All parameters are mandatory) and return small date & time as a result. If we pass any invalid date and time parts, it will generate an error. Also if we pass NULL date and time parts to this function it will return NULL value.
Let me explain this with simple examples.

Example-1 : SMALLDATETIMEFROMPARTS

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = 23
Declare @Minute as smallint =58
Select SMALLDATETIMEFROMPARTS( @Year,@Month,@Day,@hour,@Minute)
as [RESULT]
--RESULT
2012-12-31 23:58:00

Example-2 : SMALLDATETIMEFROMPARTS- With Invalid Date and Time Parts
It will generate an error because only valid date and time parts are allowed as a parameter.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = 24 --- Not a valid hour
Declare @Minute as smallint =58
Select SMALLDATETIMEFROMPARTS( @Year,@Month,@Day,@hour,@Minute)
as [RESULT]
--RESULT

Msg 289, Level 16, State 4, Line 6
Cannot construct data type smalldatetime, some of the arguments have values which are not valid.

Example-3 : SMALLDATETIMEFROMPARTS- With NULL Date and Time Parts
It will return NULL even if only one part is NULL.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = NULL --- Lets Make hour as a NULL
Declare @Minute as smallint =58
Select SMALLDATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minute)
as [RESULT]
--RESULT

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Date and Time Function – DATETIMEFROMPARTS()

Posted in Functions, SQL SERVER, tagged , , , on September 6, 2012| 2 Comments »

In my previous Posts, I discussed about DATEFROMPARTS and TIMEFROMPARTS function in SQL SERVER 2012. Today we will discuss one more function introduced in SQL SERVER 2012 namely “DATETIMEFROMPARTS“.

Let me explain its syntax, parameters, purpose and examples in detail.

Syntax :

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31. (Depends upon the total number of days in a month)
@hour : A valid integer for the hour range from 0-23
@Minutes : A valid integer for the minute range from 0-59
@Seconds : A valid integer for the second range from 0-59
@milliseconds : A valid integer for the millisecond range from 0-999.

Purpose :
This function requires year, month, day, hour, minute, seconds & milliseconds as a parameter (All parameters are mandatory) and return date and time as a result. If we pass any invalid date and time parts, it will generate an error. Also if we pass NULL date and time parts to this function it will return NULL value.
Let me explain this with simple examples.

Example-1 : DATETIMEFROMPARTS 

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = 23
Declare @Minutes as smallint =58
Declare @Seconds as smallint =55
Declare @milliseconds as smallint =990
Select DATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minutes,@Seconds,@milliseconds)
as [RESULT]
--RESULT
2012-12-31 23:58:55.990

Example-2 : DATETIMEFROMPARTS – With Invalid Date and Time Parts
It will generate an error becuase only valid date and time parts are allowed as a parameter.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = 24 --- Not a valid hour
Declare @Minutes as smallint =58
Declare @Seconds as smallint =55
Declare @milliseconds as smallint =990
Select DATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minutes,@Seconds,@milliseconds) as [RESULT]
--RESULT

Msg 289, Level 16, State 3, Line 26
Cannot construct data type datetime, some of the arguments have values which are not valid.

Example-3 : DATETIMEFROMPARTS – With NULL Date and Time Parts
It will return NULL even if only one part is NULL.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = NULL --- Lets Make hour as a NULL
Declare @Minutes as smallint =58
Declare @Seconds as smallint =55
Declare @milliseconds as smallint =990
Select DATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minutes,@Seconds,@milliseconds)
as [RESULT]
--RESULT

Reference : MSDN

Read Full Post »

Older Posts »