Feeds:
Posts
Comments

Archive for September, 2012

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 »

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 »

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 »

In my previous post, I discussed about DATEFROMPARTS. Today we will discuss TIMEFROMPARTS(). In earlier version of SQL SERVER, there was no built in function to convert time parts (hours, minutes & seconds) to time. Lets have a look, how we were doing in earlier version of SQL server.

For Example :

--SQL Server 2008
Declare @Hour as int=4
Declare @Minute as int=20
Declare @Second as int=30
Declare @Fraction as int=55

Select Convert(time,Convert(varchar(4), @Hour)
+ ':' +
Convert(varchar(2), @Minute)
+ ':' +
Convert(varchar(2), @Second)
+ ':' +
Convert(varchar(2), @Fraction))
--Result
--04:20:30.0550000

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

Syntax :

    TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Parameters :
@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 a valid hour, minute, seconds, fractions & precision as a parameter and returns a valid time. If we pass any invalid time parts to this function it will return 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 : TIMEFROMPARTS – With Valid time parts

Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,2) as [Result]
--Result
23:59:59.50

Example-2 : TIMEFROMPARTS – With Valid & Invalid precision

Declare @Hour as int=23, @Minute as int=59, @Second as int=59,@Fraction as int
Set @Fraction=2 -- If fractions is 2 and precision is 1
--, It means the fractions will be 2/10 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,1) as [Result]

Set @Fraction=20 -- If fractions is 20 and precision is 2
--, It means the fractions will be 20/100 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,2) as [Result]

Set @Fraction=200 -- If fractions is 200 and precision is 3
--, It means the fractions will be 200/1000 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,3) as [Result]

Set @Fraction=2000 -- If fractions is 2000 and precision is 4
--, It means the fractions will be 2000/10000 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,4) as [Result]

Set @Fraction=20000 -- If fractions is 20000 and precision is 5
--, It means the fractions will be 20000/100000 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,5) as [Result]

Set @Fraction=200000 -- If fractions is 200000 and precision is 6
--, It means the fractions will be 200000/1000000 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,6) as [Result]

Set @Fraction=2000000
-- If fractions is 2000000 and precision is 7
--, It means the fractions will be 2000000/10000000 of a second.
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,7) as [Result]

Set @Fraction=2000000
--It will generate an error because precision can be 7 maximum
--, It means the fractions will be 2000000/10000000 of a second.
--Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,8) as [Result]
--Result

If precision is 8 then it will generate this error.

Msg 1002, Level 16, State 2, Line 33
Line 33: Specified scale 8 is invalid.

Example-3 : TIMEFROMPARTS – With Invalid time parts
If we provide an invalid time part to this function, it will generate an error because only valid time parts are allowed as a parameter.

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 TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,2) as [Result]
--Result

Msg 289, Level 16, State 2, Line 24
Cannot construct data type time, some of the arguments have values which are not valid.

Example-4 : TIMEFROMPARTS- NULL as a parameter except Precision parameter
If we pass NULL value to any of its parameter except Precision parameter, It will return NULL value .

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 TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,2) as [Result]
--Result

Example-5 : TIMEFROMPARTS- @Precision as NULL value
If we pass NULL value to precision parameter of this function, It will generate an error.

Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=50
Declare @Fraction as int=50
Select TIMEFROMPARTS(@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 time scale argument are integer constants and integer constant expressions.

Example-6 : TIMEFROMPARTS- With Null and Invalid parameters
If we pass NULL and Invalid value to its parameter, It will return NULL as a result.

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 TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,2)
as [Result]
--Result
NULL

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

Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,0)
as [Result]
--Result

Msg 289, Level 16, State 2, Line 24
Cannot construct data type time, some of the arguments have values which are not valid.

Example-8: TIMEFROMPARTS- With missing parameters
As I have mentioned above, all parameters(5) are mandatory for TIMEFROMPARTS function. Lets try to pass less than 5 parameters.

Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Select TIMEFROMPARTS(@Hour,@Minute,@Fraction,2) as [Result]
--Result

Msg 10760, Level 16, State 1, Line 23
The timefromparts function requires 5 argument(s).

Reference : MSDN

Read Full Post »

« Newer Posts