SQL SERVER – 2008 – New DataTypes DATE and TIME

One of our project manager asked me why SQL Server does not have only DATE or TIME datatypes? I thought his question is very valid, he is not DBA however he understands the RDBMS concepts very well. I find his question very interesting. I told him that there are ways to do that in SQL Server 2005 and earlier versions. He asked me but if there are DATE and TIME datatypes not DATETIME combined.

This question we all DBA had for many years and we all wanted DATE and TIME separate datatypes then DATETIME combined. Microsoft has incorporated this feature in SQL Server 2008. It supports many new DATETIME datatypes. Today we will see DATE and TIME specifically.

DATE datatype Explanation

SQL SERVER 2005:
DECLARE @Date AS DATETIME
SET @Date = GETDATE()
SELECT @Date OriginalDate, CONVERT(VARCHAR(10),@Date,111) CastDate

ResultSet:
OriginalDate CastDate
———————– ———-
2007-12-22 17:48:14.640 2007/12/22

SQL SERVER 2008:
DECLARE @Date AS DATE
SET @Date = GETDATE()
SELECT @Date OriginalDate

ResultSet:
OriginalDate
———————–
2007-12-22

TIME datatype Explanation

SQL SERVER 2005:
DECLARE @Time AS DATETIME
SET @Time = GETDATE()
SELECT @Time OriginalTime, CONVERT(VARCHAR(10),@Time,108) CastTime

ResultSet:
OriginalTime CastTime
———————– ———-
2007-12-22 17:48:57.200 17:48:57

SQL SERVER 2008:
DECLARE @Time AS TIME
SET @Time = GETDATE()
SELECT @Time OriginalTime

ResultSet:
OriginalTime
———————–
17:48:57.2000000

Reference : Pinal Dave (http://blog.SQLAuthority.com)

12 thoughts on “SQL SERVER – 2008 – New DataTypes DATE and TIME

  1. Thanks for the information. That is interesting to know. But I have never been bordered by a combined datetime data type. Sometimes it cause confuse for people from different environment. Because when people specifiy a date like, 2007-12-22, in the where statement they don’t know it actually means 2007-12-22 00:00:00 for MS SQL.

    Like

  2. Thanks for giving a very interesting and important information. I am working as DBA. Allthough we can get the date and time seperately by using Convert function, there are many occassions we never need the date combined with time. Our .Net development team had to struggle a lot to get rid of time element from date as their projects should get date without convert function. This feature in SQL Server 2008 will definitely reduce the burden of such developers.

    Like

  3. Great article. I have a question, however. I am building a database at the moment and adding columns just about everyday to handle new situations which arise.

    I found this great query which helps document databases. I have two columns defined in one table in particular which are datetime fields. Since we don’t have plans to go to SQL 2008 anytime soon, I want to return the time, rather than the datetime and parse it out in C# (lazy?).

    So I would like to implement that great query with the time conversion above, but the query says syntax error near ‘c’.

    Hopefully this is just a simple no-brainer. I’ll keep banging away at it but perhaps somebody could simply look at it and say ‘Duh, you moron. You need to do…’

    Here we go:

    SELECT
    column_name= case c.name when ‘OnlyBuildIfGreaterThan’ then
    Convert(varchar(10) c.name, 108)
    when ‘OnlyBuildIfLessThan’ then
    Convert(varchar(10) c.name, 108)
    else
    c.name
    end as c.name
    datatype=t.name,
    length=c.length
    FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
    inner join systypes t on c.xtype=t.xtype
    where o.xtype=’U’ and o.name in (
    ‘CancelDataWareHouseBuild’)
    order by o.name,c.colid

    Like

  4. Hi people,

    I was trying to execute a multiple INSERT to… So, I found a good solution, that’s sample, I’m using an Table Identity Column ID_XX on relationchip see:

    – You will test a SELECT using a DECLARE after you INSERT, ex:

    INSERT INTO TAB1 (Atribute_1, Atribute_2)
    VALUES (‘Value 2′, ‘Value 2′);

    — So on TAB1 the ID_XX is equals 1

    declare @ID_XX AS INT SET @ID_XX = (select ID_XX from TAB1 where Atribute_1 = ‘Value 1′ and Atribute_2 = ‘Value 2′)

    IF (@ID_XX) IS NOT NULL

    BEGIN

    INSERT INTO TAB2 (Atribute_1, Atribute_2)
    VALUES(‘1′, @ID_XX);

    Any doubt?
    You may do that a big sequence of insert on many tables using variables

    Att.
    Fábio Passoumidis

    Like

  5. HI

    Above query is throughing the below error
    Please guide on the data types for the date

    Msg 2715, Level 16, State 3, Line 1
    Column or parameter #-1: Cannot find data type DATE.
    Parameter ‘@Date’ has an invalid data type.

    Like

  6. Pingback: SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2 Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – DATE and TIME in SQL Server 2008 Journey to SQL Authority with Pinal Dave

  8. When i am executing the below query in SQL server 2008, i am getting an error message

    DECLARE @CurrentDate AS DATE

    SET @CurrentDate = CAST(GETDATE() AS DATE)

    Error messae:
    Msg 2715, Level 16, State 3, Line 3
    Column, parameter, or variable #1: Cannot find data type DATE.
    Parameter or variable ‘@CurrentDate’ has an invalid data type.

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s