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)
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.
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.
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
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
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.
i got it fixed
i have table when i entered query in sql 2000.i got error message date data type can not find what can i do
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.
Make sure the compatibility mode is set to 100. Also refer this
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