JAJAH Development Blog

Blogs by JAJAH Developers

Archive for the ‘sql’ Category

Date and Time manipulations in SQL

Wednesday, December 12th, 2007
Data types

in SQL Server, data types for representing date and time are datetime and smalldatetime.

Not like the variety of structures .NET Framework 2.0 and .NET Framework 3.0 where DateTime, TimeSpan, Calendar and other structures are being used for storing dates and time intervals, in SQL we can use only datetime and smalldatetime.

The difference between datetime and smalldatetime is the range of dates and the accuracy of minutes and seconds
- datetime: January 1, 1753 - December 31, 9999, with accuracy of one three-hundredth of a second
- smalldatetime: January 1, 1900 - June 6, 2079, with accuracy to the minute

So, let’s get started…

Converting datetime into string (nchar, nvarchar, char, varchar)

using the CONVERT method we can convert a datetime into other expression in lots of formats

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
samples:

Initiate datetime:
 declare @date datetime
set @date = getdate()

Sample 1: get the date in U.S standard

query:
 select convert(varchar,@date,101)

result:
 ’12/12/2007’

Sample 2: get the date and time in ODBC canonical (with milliseconds) standard

query:
 select convert(varchar,@date,121)

result:
 ’2007-12-12 16:31:20.967’

Sample 3: get the time in hh:mi format

query:
 select convert(varchar(5),@date,108)

result:
 ’16:39’

 

useful functions when dealing with datetime

GETDATE:
 Returns the current system date and time in the SQL Server standard internal format for datetime values.

Syntax for GETDATE:
GETDATE ( )

Sample:

query:

SELECT GETDATE()

result:

‘2007-12-12 17:07:39.733’

DATEADD:

Returns a new datetime value based on adding an interval to the specified date.

Syntax for DATEADD: DATEADD (datepart , number, date )

Sample:

query:

SELECT DATEADD(month, 1, ‘08/30/2006′)

result:

‘2006-09-30 00:00:00.000’

DATEDIFF:

Returns the number of date and time boundaries crossed between two specified dates.

Syntax for DATEDIFF: DATEDIFF ( datepart , startdate , enddate )

Sample:

query:

SELECT DATEDIFF(day, ‘08/30/2006′, ‘09/30/2006′)

result:

‘31′

DATENAME:

Returns a character string representing the specified datepart of the specified date.

Syntax for DATENAME:
DATENAME ( datepart ,date )

Sample:

Query:

SELECT DATENAME(month,‘1995-10-30 12:15:32.123′)

result:

‘October’

 

DATEPART:

Returns an integer that represents the specified datepart of the specified date.

Syntax for DATEPART:
DATEPART ( datepart , date )

Sample:

Query:

SELECT DATEPART(month, ‘08/30/2006′)

result:

‘8’

Posted in sql | Comments Off

Jajah is the VoIP player that brought you web-activated telephony.