Just a week ago, my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with the date.
SELECT GETDATE()
ResultSet: 2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions. The method he suggested was to use
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggests many formats are displayed on MSDN.
Some claims that using CONVERT is slower than using DATE functions, but it is extremely negligible. I prefer to use CONVERT.
Here is the video discussing the same concepts.
Let me know what is your favorite method to select only date part from datetime. I would like to know what are the different methods and if any other method is interesting I will be happy to blog about that in the future with due credit.
Here is Part 2 of the blog post: SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2.
In SQL Server 2008 we also have a new datatype of Date and Time, which can be very handy if you are using SQL Server 2008 or later versions. You can read about that in this blog post SQL SERVER – DATE and TIME in SQL Server 2008.
Reference: Pinal Dave (https://blog.sqlauthority.com)
405 Comments. Leave new
Thank you, for a quick tip.
Not really agree.
The best would be
CONVERT (date, GETDATE())
https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-2017
You may be interested to know more methods
if the date is stored as varchar datatype and i need to extract only “year” from that date,could anyone help me out in this issue?
It depends on the format. If it is YYYYMMDD you can use extract first four digits, if it is dd/mm/yyyy , extract the last four digits etc
very helpful Thank You……….
thanks :)
Thank you so much Sir, it help me lots
Sir, I am raghu i am having problem with this query
Q. Display the orderID of the top five orders based on the Total amount due in the year 2001.
hint:You can extract the Year part from a date using the Datepart Function
i am not getting the proper output
i am using SSMS 2005, Database Adworks please reply ASAP.
Say big thank you for your help!!
Guys check this in SQL
Question:”print (getdate ())”
Solution:Mar 9 2013 5:56AM
this is helpful however, I bumped into problems when I used convert instead of dateadd function to extract the datepart. when you use this column to search for a specific date, the converted datetime column will yield incorrect results since it still has the timepart in it only not visible. in cases like this, it is advisable to use the dateadd function instead as you will be sure that it will set the time part to 00:00:00.000 hence you will be able to use this to look for a specific date only.
thank you
I’m very joy in this site.
thank you
I’m very joy in this site.
what is 111,104 etc at the end.
It is the style of the format. 101 is mm-dd–yyyy, 103 is dd-mm-yyyy , etc
I still don’t understand.. I have 3 dates in my table configured as
10/10/2013 1:11:00 PM
I need to extract each piece separately but this doesn’t work
select datepart (mm, session_start) startMM, datepart(dd, session_start) startDD, datepart(yy,session_start) startYY,
datepart (HH, session_start) startTimeHour, datepart(MI, session_start) startTimeMin,
datepart(SS, session_start) startTimeSec, datepart (AM, session_start)
What am I doing wrong? Please help.
What did you mean by “this doesn’t work”? Is the datatype of the column DATETIME?
sorry if its not relevant
I want to generate date from 2010-01-01 to 2013-12-31 then find GenerateDate(Which will be 2010-01-01 for first data), Year (Which will be 2010 for 2010-01-01). Month(Which will be 01 for 2010-01-01), Day(Which will be 01 for 2010-01-01), Week(Which will be 1 for 2010-01-01), Quarter (Which will be 01 for 2010-01-01) then I want to create a Date_Table and insert all data in the SQL table. There will be a auto increment filed (ID) also. Here GenerateDate will be Primary Key Please help me.
Hi All,
I have created a stored procedure like below.
CREATE PROCEDURE [dbo].[SP_CONSULTATION]
@time time,
@time1 time,
@AGE numeric(3, 0)=5,
@AMOUNT numeric(6, 2) OUTPUT
AS
BEGIN
SET @time= LEFT(DATEADD(mi, 10, (CONVERT(TIME(0),GETDATE()))),5)
–SET @time= CONVERT (time, SYSDATETIME())
SET @time1 = (select @time)
select @time1
SET @AMOUNT= CASE
WHEN @time1 ’18:00′ THEN
CASE
WHEN @AGE <= 10 THEN 200
ELSE 500
END
ELSE
CASE
WHEN @AGE = 5 AND @AGE <= 10 THEN 100
ELSE 200
END
END
END
when i execute this stored procedure i got error like this
Msg 201, Level 16, State 4, Procedure SP_CONSULTATION, Line 0
Procedure or function 'SP_CONSULTATION' expects parameter '@time', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
i want the output as to meet the conditions in stored procedure and want to display the amount value. please help me out from this.
Thanks,
Avinash P
if date is inserted in table like ‘Jan 5 1980 12:00AM’
then how can i change the format like this 05/01/1980
1 Always use proper datetime datatypes
2 Leave the formation at the front end application
Jawalikar, as has been said, you should only use these functions for query related issues. Your application is more likely to be portable and break less if you use the end-user application for date formatting. I know this doesn’t answer your question, but as a developer I’ve had to clean up messes by past devs who used SQL for date formatting, it will work, but there are much better ways.
Hi How are you? I need a help in converting BIGINT to DATETIME. Sample BIGINT Value 201411132245 i into 2014-11-13 22:45 (Date Hour Minute). Could you please help me . Thanks in advance…
Here is the way
Want only the date part in the format (dd-mm-yyyy) stored in table as datetime value which contains date and time.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()
SELECT @datetime AS [original], convert(VARCHAR(11), @datetime, 105) AS [short]
GO
–OUTPUT
original short
———————– ———–
2015-03-26 06:43:56.290 26-03-2015