Selecting the date part and formatting it are different tasks. I keep typed dates for calculations and text for display.
A teammate wanted a value such as 2007/06/10. I showed two approaches. DATEADD/DATEDIFF produces a datetime at midnight. CONVERT with style 111 produces text with slash separators.
DECLARE @Now datetime = '2007-06-10T13:14:15';
SELECT DATEADD(day, DATEDIFF(day, 0, @Now), 0) AS midnight_datetime,
CONVERT(varchar(10), @Now, 111) AS formatted_text,
CAST(@Now AS date) AS date_value;The first result is midnight on June 10. The second is the string 2007/06/10, and the third is a SQL Server date. SSMS chooses how to display that typed date. Its displayed format isn’t stored in the date value.
Choose a type for the date part
The date type is available from SQL Server 2008. Preserve a typed value for comparison, grouping, and application parameters. Format it when presenting it to a reader.
Avoid filtering a large indexed datetime column by converting every row merely to remove its time. A start-inclusive, end-exclusive date range often expresses the intended period more clearly. Compare actual plans rather than assuming either expression is universally faster.
Read the three result types
| Result column | SQL type | Value in this example |
|---|---|---|
midnight_datetime | datetime | June 10, 2007, at midnight |
formatted_text | varchar(10) | 2007/06/10, a character string |
date_value | date | June 10, 2007, with no time component |
The column alias is only a label. It does not determine the result type. If an application expects a calendar value, return a typed temporal value and let its presentation layer choose the display. If a report explicitly needs slash-separated text, the style 111 conversion expresses that requirement.
I also check the caller. A string parameter and a typed parameter can lead to different conversions in the surrounding query. Keep database comparisons separate from the formatting used on a screen or an exported report. The example deliberately shows all three expressions together so that the choice is visible.
Removing a time component does not convert a timestamp between time zones. If the application records UTC but groups events by a local business day, first decide which time zone defines that day. Truncating the original value without that decision can group an event under the wrong calendar day.
Reference: Microsoft’s CAST and CONVERT documentation describes the result conversion and the supported display styles.
Related reading
- SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2
- SQL SERVER – DATE and TIME in SQL Server 2008
Original video
A formatted date is not a different stored date, it is a presentation choice.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.