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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script
Next Post
SQLAuthority News – Jobs, Search, Best Articles, Homepage

Related Posts

10 Comments. Leave new

  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Fábio Passoumidis
    February 26, 2008 8:41 pm

    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

    Reply
  • 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.

    Reply
  • i got it fixed

    Reply
  • i have table when i entered query in sql 2000.i got error message date data type can not find what can i do

    Reply
  • 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.

    Reply
  • Here is my q

    Use MSDB
    Select Database_Name, MAX(backup_start_date) as ‘Most Recent Backup Date’, Type as ‘Backup Type’ from Backupset
    where database_name = ‘mydatabase’
    group by database_name, Type

    mydatabase 2017-03-30 23:59:55.000 D
    mydatabase 2017-03-31 11:00:00.000 I

    Date output comes in future date how can correct it

    I’m using sql 2008

    Reply

Leave a Reply

Menu