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
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
I have DateTime column in my Trans table such as this one ‘2015-01-16 12:12:01.000’
I want to pass in only datetime = ‘2015-01-16’
when I use this sql it pulls 0 rows
select top 10 * from Trans with (nolock)
where datetime = ‘2015-05-14’
any help on this please.
I have DateTime column in my Trans table such as this one ‘2015-01-16 12:12:01.000′
I want to pass in only datetime = ‘2015-01-16′
when I use this sql it pulls 0 rows
select top 10 * from Trans with (nolock)
where datetime = ‘2015-05-16′
any help on this please.
sorry about two wrong information above:
I have DateTime column in my Trans table such as this one ‘2015-01-16 12:12:01.000′
I want to pass in only datetime = ‘2015-01-16′
when I use this sql it pulls 0 rows
select top 10 * from Trans with (nolock)
where datetime = ‘2015-01-16′
any help on this please.
Syed – ‘2015-01-16 12:12:01.000′ and ‘2015-01-16′ is not same that’s why zero rows. Please use convert.
select VALUE FROM TABLE WHERE DATE=DTPICKER1.VALUE DOESNT RETURN A VALUE
The left,11 changes the format of the date to conform to LocalLongDate
selection criteria in both date format yyyy-MM-dd or yyyy-dd-MM in sql server
SELECT CONVERT(VARCHAR(10),GETDATE(),120)
SELECT CONVERT(VARCHAR(10),GETDATE(),110)
— =============================================
— Author: Frans Habekotté
— Create date: 16-12-2008
— Description: FRH_DateSetTimeZero
–select getdate(),dbo.FRH_DateSetTimeZero(getdate())
— =============================================
CREATE FUNCTION FRH_DateSetTimeZero
(
— Add the parameters for the function here
@MyDate datetime
)
RETURNS datetime
AS
BEGIN
— Declare the return variable here
DECLARE @DateWithoutTime datetime
— Add the T-SQL statements to compute the return value here
SELECT @DateWithoutTime = cast(cast(cast(@MyDate as float) as int) as datetime )
— Return the result of the function
RETURN @DateWithoutTime
END
— GO
GO
for the SQL Server 2014 or later, it is simplest to use:
select cast(GETDATE() as date)
111- Means yyyy-mmm-dd
We are Using Left function in that statement i.e select left(getdate(),11)
So the result is mmm-dd-yyyy
You can use Format in this case it also gives the same result :
select format(getdate(),’yyyy-MM-dd’)
Great tip.