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. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.
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.
Quick Video on the same subject
Here is the video discussing the same concepts.
Additionally, if you want to get current datetime you can use following functions in place of datetime variable.
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced. GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.
Please leave a comment about Hour:Minute Format. I will be happy to read them.
Reference: Pinal Dave (https://blog.sqlauthority.com)
162 Comments. Leave new
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
*/
If i want only the hours and minutes and not the seconds how can i use
SELECT
LEFT(CONVERT(TIME(0),GETDATE()) ,5) AS [HourMinuteSecond]
GO
/*
HourMinuteSecond
————————
10:48
*/
and as an added bonus that converting a date/time value to VarChar does not offer, you can still add this result with time…(above +10 mins below)
SELECT
LEFT(DATEADD(mi, 10, (CONVERT(TIME(0),GETDATE()))),5) AS [HourMinuteSecond]
GO
/*
HourMinuteSecond
————————
10:58
*/
Daniel S. Gurrola II
No Acronyms, Affiliations, Certifications or other Nausea – it’s just…me.
this was very helpful,thanks mate
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
Use
WHERE
dbo.deposit_m.do_deposition >=@fdate AND
dbo.deposit_m.do_deposition <=@tdate
its better to use datediff function for date comparison as
dbo.deposit_m.do_deposition <=@tdate
will fail as @tdate time portion will have 12:00 AM so the query will not bring info for the last day of the condition if deposit_m.do_deposition has time portion.
If the date column has index, usage of datediff function will deny its usage
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
Yes as a varchar and not as a Date
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?
Read about CONVERT function in SQL Server help file
It has examples with explanation
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.
First method converts the dates to VARCHARs whereas the second query is specific to version 2008 that converts to DATE and TIME seperately
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.
So the alternate is
WHERE
dbo.deposit_m.do_deposition >=@fdate AND
dbo.deposit_m.do_deposition <=@tdate
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.
Converting to varchar is not a good practise.Refer this post to know you can effectively remove time part. See the last examples
**********
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