April 30, 2014

SQL Server Date & Time - Data Types and its Function

For more details: http://msdn.microsoft.com/en-us/library/ms186724.aspx

Date & Time Data Types:

For more detail about data types, you can see in sys.systypes

date:

You can find this on SQL Server 2008, 2008 R2 and 2012. Its default literal format is YYYY-MM-DD

Syntax:

date

For example:

Declare @date date1;
Declare @date date2;

set date1 = '20-09-2012'
set date2 = '20-09-2012 12:40:30'

Select date1
Output: 2012-09-20
Select date2
Output: 2012-09-20

time:

You can find this on SQL Server 2008, 2008 R2 and 2012. This will return time of the day without timezone and is based on 24 hours.

Syntax:

time[(fractional second precision)]

For Example:

Declare @time4 time(4)
Declare @time7 time(7)
Declare @time time

set time4 = '12:45:25.1234'
set time7 = '12:45:25.1234567'
set time = '2012-09-10 12:45:25.123' 

select time4
Output: 12:45:25.1234
select time7
Output: 12:45:25.1234567
select time
Output: 12:45:25.1230000

smalldatetime:

You can find this on SQL Server 2000, 2005, 2008, 2008 R2, 2012. This returns date with time of a day (2 hour format) with seconds always 00 and without fractional seconds. Seconds are rounded to minutes depending upon the value of seconds. The value which is less than 29.998 is rounded to the nearest minutes and the value which is greater than 29.999 is rounded to its nearest minutes.  This will not return fractional seconds.

Syntax:
small datetime

For Example:

Declare @smalldatetime1 smalldatetime = '2012-09-13 12:45:31' -- will round to next minute
Declare @smalldatetime2 smalldatetime = '2012-09-13 12:45:29' -- will not round to next minute

Select @smalldatetime1
Output: 2012-09-13 12:46:00
Select @smalldatetime2
Output: 2012-09-13 12:45:00

datetime:

You can find this on  SQL Server 2000, 2005, 2008, 2008 R2 and 2012. This returns date with time of a day and fractional seconds based on 24 hour.

Syntax:
datetime

For Example:

Declare @datetime1 datetime = '12-23-35'
Declare @time1 time(4) = '11:10:05.1234'
Declare @datetime2 datetime = @time1

Select @datetime1
Output: 2035-12-23 00:00:00.000
Select @datetime2
Output: 1900-01-01 11:10:05.1234

datetime2:

It can be considered as a extension of datetime but this holds large date range along with default precision. You can find this only in SQL Server 2008, 2008 R2 and 2012.

Syntax:
datetime2 [(fractional seconds precision)]

For Example:
Declare @datetime2_1 datetime2(4) = '12-13-25 12:32:10.1234'
Declare @datetime2_2 datetime2 = '12-13-25 11:32:10.1234567'
Declare @datetime2_3 datetime2 = '12-13-25'

Select @datetime2_1
Output: 2025-12-13 12:32:10.1234
Select @datetime2_2
Output: 2025-12-13 11:32:10.1234567
Select @datetime2_3
Output: 2025-12-13 00:00:00.0000000

datetimeoffset:

You can find this on SQl Server 2008, 2008 R2, and 2012. This holds date along with time of a day and contains timezone awareness based on 24 hour clock.

Syntax:
datetimeoffset[(fractional seconds precision)]

For Example:
Declase @datetimeoffset datetimeoffset) = '12-13-25 12:32:10 +05:45'
Declare @time time(3) = @datteimeoffset
Declare @date date = @datetimeoffset

Select @datetimeoffset 
Output: 2025-12-13 12:32:10.0000 +05:45 
Select @time
Output: 12:32:10.000
Select @date
Output: 2025-12-13

Date Time Function:

GETDATE:

It helps to get database system timestamp with fractional seconds as a datetime without time zone offset.

Syntax:
GetDate()

Example:
Select GetDate()

Current_TimeStamp:

Same as GetDate().

GETUTCDATE:

Same as GetDate() but the value represents the current UTC time.

SYSDATETIME

SYSDATETIMEOFFSET

SYSUTCDATETIME

DateName:

It returns a character string for the specified part of the date.

Syntax:
DateName(datepart, date)

For Example:
Declare @date datetime
set @date = '2012-12-30 12:25:10.013'

Select @date
Output: 2012-12-30

Select datename(year, @date) 
Select datename(yy, @date) 
Select datename(yyyy, @date) 
Output: 2012

Select datename(quarter, @date)
Select datename(qq, @date)
Select datename(q, @date)
Output: 4

Select datename(month, @date)
Select datename(mm, @date)
Select datename(m, @date)
Output: December

Select datename(dayofyear, @date)
Select datename(dy, @date)
Select datename(y, @date)
Output: 364

Select datename(day, @date)
Select datename(dd, @date)
Select datename(d, @date)
Output: 30

Select datename(week, @date)
Select datename(ww, @date)
Select datename(wk, @date)
Output: 53

Select datename(weekday, @date)
Select datename(dw, @date)
Select datename(w, @date)
Output: Monday

Select datename(hour, @date)
Select datename(hh, @date)
Output: 12

Select datename(minute, @date)
Select datename(mi, @date)
Select datename(n, @date)
Output: 25

Select datename(second, @date)
Select datename(ss, @date)
Select datename(s, @date)
Output: 10

Select datename(millisecond, @date)
Select datename(ms, @date)
Output: 13

Datepart:

It is similar to datename but it returns as an integer.

Syntax:
datepart( datepart, date)

For Example:
Declare @date datetime
set @date = '2012-12-30 12:25:10.013'

Select @date
Output: 2012-12-30

Select datepart(year, @date) 
Select datepart(yy, @date) 
Select datepart(yyyy, @date) 
Output: 2012

Select datepart(quarter, @date)
Select datepart(qq, @date)
Select datepart(q, @date)
Output: 4

Select datepart(month, @date)
Select datepart(mm, @date)
Select datepart(m, @date)
Output: 12

Select datepart(dayofyear, @date)
Select datepart(dy, @date)
Select datepart(y, @date)
Output: 364

Select datepart(day, @date)
Select datepart(dd, @date)
Select datepart(d, @date)
Output: 30

Select datepart(week, @date)
Select datepart(ww, @date)
Select datepart(wk, @date)
Output: 53

Select datepart(weekday, @date)
Select datepart(dw, @date)
Select datepart(w, @date)
Output: 2

Select datepart(hour, @date)
Select datepart(hh, @date)
Output: 12

Select datepart(minute, @date)
Select datepart(mi, @date)
Select datepart(n, @date)
Output: 25

Select datepart(second, @date)
Select datepart(ss, @date)
Select datepart(s, @date)
Output: 10

Select datepart(millisecond, @date)
Select datepart(ms, @date)
Output: 13

Day

It is similar to Datepart(Day, date) and if date contains only time then it will return value as 1.

Syntax: Day(date)

Month

It is similar to Datepart(Month, date) and if date contains only time then it will return value as 1.

Syntax: Month(date)

Year

It is similar to Datepart(Year, date) and if date contains only time then it will return value as 1900.

Syntax: Year(date)

IsDate

It checks whether the given expression is valid datetime/ date/ time. It returns value as 0 or 1. If the value is true, it returns 1 else 0.

Syntax: Isdate(expression)

For Example:
Select IsDate('2012-12-25 12:30:25.1234567') Returns 1
Select IsDate(NULL) Returns 0

DateAdd

It returns a specified date with the specified number interval added to specified datepart of that date. datepart cannot be User-defined variable or its equivalents.

Syntax:
DateAdd(datepart, number, date)

For Example:

Declare @date datetime
Set @date = '2012-12-30 12:30:15.013'

Select dateadd(year, 1, @date)
Select dateadd(yy, 1, @date)
Select dateadd(yyyy, 1, @date)
Output: 2013-12-30 12:30:15.013

Select dateadd(month, 1, @date)
Select dateadd(mm, 1, @date)
Select dateadd(m, 1, @date)
Output: 2013-01-30 12:30:15.013'

Select dateadd(quarter, 1, @date)
Select dateadd(qq, 1, @date)
Select dateadd(q, 1, @date)
Output: 2013-03-30 12:30:15.013'

DateDiff

It returns difference between startdate and enddate based on datepart.

Syntax:
DateDiff( datepart, startdate, enddate)

For Example:

Declare @date1 datetime = '2012-12-30 12:30:10.123'
Declare @date2 datetime = '2013-05-25 10:15:20.123'

Select datediff (year, @date1, @date2)
Output: 1

April 19, 2014

Difference between SQL Server and PostgreSQL

I had a chance of working with PostgreSQL and SQL Server at the same time to pull bulk data from PostgreSQL to SQL Server using Linked Server and OPENQUERY function of SQL Server for report writing in SSRS. Though I don't have to work much with PostgreSQL, I thought of comparing these two database server to clear my basic understanding of how PostgreSQL differs from SQL Server.
  • Operating System
SQL Server only runs on Windows Desktop/Server whereas PostgreSQL runs on Windows Desktop/Server, Linux, Unix, Mac
  • Licensing
SQL Server is commercial closed source and have to pay to use it whereas PostgreSQL is BSD open source database management system that allows you to do anything for making your own commercial product.
  • Views
SQL Server supports automatically updatable and incexable views whereas for PostgreSQL, you have write rules against views in order to update views.
  • Limit..offset
SQL Server does not have Limit..offset but it supports TOP and ANSI compliant ROW_Number () Over (Order by column_name) whereas PostgreSQL does not support TOP but support Limit..offset and also supports the ANSI compliant Row_Number() over (order by column_name)

  • Concatenation
SQL Server use plus operator for concatenation. Example: Select HouseNo + ', ' + Line1 + ', ' + County as HomeAddress whereas in PostgreSQL query  use pipe sign and previous example becomes Select HouseNo || ', ' || Line1 || ', ' || County as HomeAddress
  • Like Statement
SQL Server by default LIKE statements are not case sensitive whereas in PostgreSQL LIKE statements are case sensitive. To make case insensitive we can use ILIKE  but in some cases we may get some issue because its not ANSI compliant and ODBC does not exp[ose it. Another way to make case insensitive is by simply making both column data and variable (value to compare) to lower case and compare it. You have to use LOWER function. 

In the latest version of PostgresSQL, citext module is integrated which helps to provide case-insensitive character sting type.

According to postgresql.org, "The citext data type allows you to eliminate callse to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the riles of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower in queries. but because it's done transparently by the data typoe, you don't have to remember to do anything special in your queries."

  • Access data from Remote Database
SQL Server can pull data from remote database server using LINKEDSERVER and OPENQUERY or SQL Server Four Part Name whereas PostgreSQL can pull using Dblink but is less elegant and efficient than SQL Server.

April 2, 2014

Union and Union all - SQL Server

I have come across many times use of this two function Union and Union all, so I thought of writing difference between these two operators.

Both Union and Union all combines rows of 2 or more than two tables into one. Main difference between Union and Union all is Union helps to remove duplicate rows. It takes time to execute the query because it has to perform distinct operation But, Union all pulls all the rows from all the tables so it takes less time to execute the query.

Example:

SELECT * FROM TABLE1

ID
1
2
3

SELECT * FROM TABLE2

ID
2
3
4

SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2

ID
1
2
3
4

SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2

ID
1
2
3
2
3
4 

Sorting the result of Union and Union all

SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2
ORDER BY ID

ID
1
2
2
3
3
4