I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution.
SQL Server 2000/2005
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO
SQL Server 2008
SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO
I hope the above solution is clear to you all.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
This one is nice and SQL Server 2008 data types Time and Date is also useful
And if you dont want those ‘nanoseconds’ in SQL Server 2008, you can specify a precision along with the TIME data type.
SELECT
CONVERT(TIME(0),GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO
/*
HourMinuteSecond DateOnly
—————- ———-
13:27:26 2009-08-06
*/
Well
Is it good practice to compare date(s) using these operators > , < , <= , … ?
or any other and smart way to do it …
I am using in this way
…
WHERE CONVERT(nvarchar(8), dbo.deposit_m.do_deposition, 112) BETWEEN @fdate AND @tdate
…
please give some suggestions
Thanks again
This is very good datatype of sql server 08 .
I was trying to separate data & time finally MS done it.
Thanks for this great demo.
It’s always good to review the basics.
Thanx Pinal.
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly will return: 08/06/2009
But for SQL 2005 the date in year shows only first two digits not the complete year and i have a doubt in the script
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
what about the parameters 108 and 101 what they actually do?
Thanks for nice post again.
What’s difference in 2008 and 2005/2000 at this point of view?
Because I ran the same script in both 2005 and 2008. Gave me same result.
Nice tips for sql2008,
but i think sir,
Varchar conversion format is much useful then time conversion in sql2008.
@Kandregula
Please see the help file for CONVERT. There is a table explaining all the date format arguments.
K.K.B,
It is not a good idea to use
“WHERE CONVERT(nvarchar(8), dbo.deposit_m.do_deposition, 112) BETWEEN @fdate AND @tdate”.
SQL Query optimizer will not be able to create the optimum plan for this query because of the CONVERT() function applied around the column “deposit_m.do_deposition”. Any index that exists on the column will not be used.
Hey, I am a starter.
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
It showed 07/14/20
I think to see date correctly, it shud be
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
And I tried
SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO
on SQL Server 2008, Express edition. It compiled successfully but on executing it gave me error:
Msg 243, Level 16, State 1, Line 1
Type TIME is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Any feedback is appreciated.
Thanks.
Geetika,
It looks like the server instance on which you are executing the query is not SQL Server 2008. To verify this, try executing “SELECT @@VERSION” and the results will tell you whether it is SQL SErver 2008 or not.
A related tip that I use in many stored procs when dealing with dates:
When I receive a date as an input argument and I want to be sure that date does not have a time associated with it, I just re-assign the date variable using CONVERT, as follows:
(Assuming @Date is the name of the input argument)
SET @Date = CONVERT(varchar(20), @Date, 101)
That one line strips off any time component (actually leaving it set to 00:00:00) while retaining the date component.
**********
Hey, I am a starter.
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
It showed 07/14/20
**********
Change to:
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
Thanks Jacob, yeah the sql server instance turned out to be SQL Server 2005 Enterprise edition.
@Speednet,
A better option is to use the DATEADD() function. In your example a datetime value is converted to VARCHAR and then converted back to DATETIME. A DATETIME value is stored internally as a number. The following example demonstrates a more optimized way of removing the TIME part from a DATETIME value.
DECLARE @d DATETIME
SELECT @d = GETDATE()
SELECT @d
/*
———————–
2009-08-08 09:56:38.793
*/
– Remove the time part
SELECT @d = DATEADD(d, 0, DATEDIFF(d, 0, @d ))
SELECT @d
/*
———————–
2009-08-08 00:00:00.000
*/
hello sir , my question is to inserting the different date format in datetime datatype only not to converting to varchar,
ie i need dd-mm-yyyy format of data in datetime datatype ….pls share ur knowledge !!!!
@lohith,
DATETIME values are stored internally as numbers and hence the question of storing the data in a certain format does not exist.
The format comes only when you parse a date string or when you display a date string. The parsing can be managed by using ‘SET DATEFORMAT’ and display can be managed by using CONVERT() function with appropriate style flags.
Here is an example of SET DATEFORMAT
SET DATEFORMAT DMY
SELECT CAST(‘31-12-2009′ AS DATETIME)
/*
———————–
2009-12-31 00:00:00.000
*/
SET DATEFORMAT MDY
SELECT CAST(‘12-31-2009′ AS DATETIME)
/*
———————–
2009-12-31 00:00:00.000
*/
Dear Pinal,
Congratulations for your great work,
Could you please tell me how to extract hour and minute only from a datetime.
ie I dont want seconds.
Thank You
Vivek
Dear Pinal,
I got the answer.
SELECT CONVERT(VARCHAR(5),GETDATE(),108) AS HourMinuteSecond
Thank You
Regards
Vivek
I want 10.30 PM not 22:30
Hi Vasu,
You can display time in AM/PM format as:
DECLARE @dt DATETIME
SELECT @dt = ‘2009-11-08 20:00′
SELECT CONVERT(VARCHAR(5),@dt,108) + ‘ ‘ + RIGHT(CONVERT(VARCHAR,@dt, 109),2)
Thanks,
Tejas
Hi,
Ignore my last comment
Please check it out: http://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/
Thanks,
Tejas
Would wish to be pointed to write an sql statement that return hourly records of a given day.
Looking for 24 records for every hours of the day?
Yes so as to return the hourly count
Try this:
DECLARE @dates TABLE (dt DateTime)
INSERT INTO @dates (dt) SELECT ‘2009-10-01′
SELECT
DATEADD(hour, number, dt) AS HourPart
FROM @dates
CROSS JOIN master..spt_values s
WHERE type = ‘p’ AND number between 0 AND 23
/*
HourPart
———————–
2009-10-01 00:00:00.000
2009-10-01 01:00:00.000
2009-10-01 02:00:00.000
2009-10-01 03:00:00.000
2009-10-01 04:00:00.000
2009-10-01 05:00:00.000
2009-10-01 06:00:00.000
2009-10-01 07:00:00.000
2009-10-01 08:00:00.000
2009-10-01 09:00:00.000
2009-10-01 10:00:00.000
2009-10-01 11:00:00.000
2009-10-01 12:00:00.000
2009-10-01 13:00:00.000
2009-10-01 14:00:00.000
2009-10-01 15:00:00.000
2009-10-01 16:00:00.000
2009-10-01 17:00:00.000
2009-10-01 18:00:00.000
2009-10-01 19:00:00.000
2009-10-01 20:00:00.000
2009-10-01 21:00:00.000
2009-10-01 22:00:00.000
2009-10-01 23:00:00.000
*/
http://syntaxhelp.com/SQLServer/Breaking-a-date-by-hour-into-24-parts
Hello all – I am a newbie,
can someone tell me how do I modify this to remove the ‘AM’ from the result
SELECT CONVERT(VARCHAR(10), DATEADD(d,+1,GETDATE()),101)+ ‘ ‘ + RIGHT (CONVERT(VARCHAR, GETDATE(), 100),7)