To get date and time separately from a datetime value, convert it to DATE and TIME. You can also format it as text. This is episode 25 of SQL in Sixty Seconds. The video shows both ways, and the tests below show where each one helps and where it hurts.
Two Kinds of Answer
Text and values look alike on screen. They behave differently later. CONVERT with a style number returns text. A conversion to date or time returns a real value. The script captures the clock once, so every column describes the same instant.
DECLARE @Now datetime = GETDATE();
SELECT @Now AS NowValue,
CONVERT(varchar(8), @Now, 108) AS TimeText,
CONVERT(varchar(10), @Now, 101) AS DateText,
CONVERT(time(0), @Now) AS TimeValue,
CONVERT(date, @Now) AS DateValue;When I ran it, the first column read 2026-10-28 15:06:01.310. The rest followed from it.
| TimeText | DateText | TimeValue | DateValue |
|---|---|---|---|
| 15:06:01 | 10/28/2026 | 15:06:01 | 2026-10-28 |
SQL_VARIANT_PROPERTY confirms the difference. The two text columns have base type varchar. The other two have base types time and date. DATE and TIME exist since SQL Server 2008, so every supported version has them.
Text Dates Sort as Text
Style 101 writes month, day, year. Sorting that text sorts by month first. Run these examples in a scratch database. The first table holds four visits.
CREATE TABLE dbo.Visits (VisitId int IDENTITY(1,1) PRIMARY KEY, VisitedAt datetime NOT NULL);
INSERT dbo.Visits (VisitedAt)
VALUES ('2024-12-31 09:15:00'), ('2025-01-02 14:30:00'), ('2025-11-05 08:00:00'), ('2026-02-14 17:45:00');Now order them by the style 101 text.
SELECT CONVERT(varchar(10), VisitedAt, 101) AS DateText FROM dbo.Visits ORDER BY CONVERT(varchar(10), VisitedAt, 101);
| Ordered by style 101 text | Ordered by style 23 text or by DATE |
|---|---|
| 01/02/2025 | 2024-12-31 |
| 02/14/2026 | 2025-01-02 |
| 11/05/2025 | 2025-11-05 |
| 12/31/2024 | 2026-02-14 |
The left column puts December 2024 last and February 2026 second. The right column is in true date order. Style 23 writes year first, so even its text sorts correctly. A real date always sorts correctly.
Filter a Whole Day
A common need is every row for one day. Old advice says a function on the column forces a scan. For a cast to date that is no longer true. I tested both forms on 200,000 rows with an index on the datetime column. This script builds the table. The chosen day holds 2335 rows.
CREATE TABLE dbo.Events (EventId int IDENTITY(1,1) PRIMARY KEY, EventAt datetime NOT NULL);
WITH n AS (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM sys.all_objects a CROSS JOIN sys.all_objects b)
INSERT dbo.Events (EventAt) SELECT DATEADD(SECOND, rn * 37, '2025-01-01') FROM n;
CREATE INDEX IX_Events_EventAt ON dbo.Events (EventAt);Now run both filters and compare the plans.
SELECT COUNT(*) FROM dbo.Events WHERE CAST(EventAt AS date) = '2025-03-15'; SELECT COUNT(*) FROM dbo.Events WHERE EventAt >= '2025-03-15' AND EventAt < '2025-03-16';
Both returned 2335 and both plans showed an Index Seek. The cast read 15 pages and the range read 10. The range form stays the safer habit, because it works for any function and any type. The cast form is fine for this case.
Pick the Clock First
GETDATE returns a datetime, which keeps about three milliseconds of precision. SYSDATETIME returns a datetime2 with up to seven fraction digits. Cast either one to date and time the same way. The example below keeps three digits.
DECLARE @Now datetime2(3) = SYSDATETIME();
SELECT CAST(@Now AS date) AS DatePart,
CAST(@Now AS time(0)) AS TimeSeconds,
CAST(@Now AS time(3)) AS TimeMilliseconds;My run returned 2026-10-28, 15:06:01 and 15:06:01.312. Choose the precision your report needs and no more. Both functions return the time of the server’s own clock. When users in several regions share a table, store UTC with SYSUTCDATETIME and convert at display time.
Fractions of a second are rounded, not cut, when you convert to time(0). The two methods can disagree for the same instant.
DECLARE @a datetime2(3) = '2026-10-28 15:22:06.562'; SELECT CAST(@a AS time(0)) AS TimeValue, CONVERT(varchar(8), @a, 108) AS TimeText;
The time(0) value was 15:22:07 and the style 108 text was 15:22:06. Pick one rule on purpose, and keep it the same in every report.
Other Handy Styles
Style 112 returns 20261028, which sorts correctly and has no separators. Style 23 returns 2026-10-28. For hours and minutes, CONVERT(varchar(5), @Now, 108) and FORMAT(@Now, 'HH:mm') both returned 14:07 for 14:07:33. A plain CAST(@Now AS time) keeps seven fraction digits, as in 14:07:33.3466667. Ask for time(0) to drop them.
Put the Two Parts Back Together
You cannot add a date to a time. The attempt failed with Msg 8117: Operand data type date is invalid for add operator. Cast both parts to datetime first.
DECLARE @d date = '2026-10-28', @t time(0) = '14:07:33'; SELECT CAST(@d AS datetime) + CAST(@t AS datetime) AS Combined;
The result was 2026-10-28 14:07:33.000.
What I Keep in My Code
Keep the real types until the last step. Convert to text only when you print a report or build a message. Filter and sort on the value. When one query needs several formats, capture the time once in a variable.
Related Reading
- Current Date and Time in SQL Server: GETDATE, SYSDATETIME and More
- Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
- Get Current System Date Time
- Date and Time Functions – EOMONTH() – A Quick Introduction
- DATE and TIME in SQL Server 2008
A date is not a string to format early, it is a value to keep until the last step.
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.
25 Comments. Leave new
It should be VARCHAR(10) in place of VARCHAR(8) in DateOnly for SQL 2000/2005
Correct.
I missed that in video as well.
Sir i have created a Campaign and sent around 5000 email,,whenever a person reads the mail i get the details in my database containing read date and time..Now i want to calculate Average Read Time: x Days y Hours z Minutes…I have used the query select ViewedOn from Campaign_Email_View where CampaignID=18 and got 4198 data…
Good One. I’ve tried it on SQL Server 2005. I should try it on other versions too.
I created a function some time ago which finds the date from datetime (converting a date to a number and then taking the integer) and then can return all sorts of dates based on that. Might be usefule for some:
/*===========================================================================================
* Function to return dates from a given date
* Time is stripped off the given date
* Second parameter determines which date will be returned as follows:
* LDLM = Last Day of Last Month
* FDTM = First Day of This Month
* FDNM = First Day of Next Month
* LDTM = Last Day of This Month
* LDNM = Last day of Next Month
* FDLM = First day of Last Month
*
* The default if a second parameter value is not supplied is Last Day of This Month
* BUT if the value is not supplied the keyword “DEFAULT” must be
* e.g. dbo.DaysOfTheMonth (‘2010-04-14’,DEFAULT)
* Might as well supply the real value (LDTM)!
* (included for demo purposes)
===========================================================================================*/
ALTER FUNCTION
[dbo].[DaysOfTheMonth]
(
@Date Datetime,
@Day CHAR(4) = ‘LDTM’
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@LastDayOfLastMonth DATETIME,
@FirstDayOfLastMonth DATETIME,
@FirstDayOfThisMonth DATETIME,
@FirstDayOfNextMonth DATETIME,
@LastDayOfThisMonth DATETIME,
@LastDayOfNextMonth DATETIME,
@Result DATETIME
SELECT
/*1.*/ @Date = CONVERT(INT,CONVERT(DECIMAL(18,2),@InputDate)),
/*2.*/ @LastDayOfLastMonth = @Date – DAY(@Date),
@FirstDayOfLastMonth = @LastDayOfLastMonth – DAY(@LastDayOfLastMonth) + 1,
/*3.*/ @FirstDayOfThisMonth = @LastDayOfLastMonth + 1,
/*4.*/ @FirstDayOfNextMonth = DATEADD(m,1,@FirstDayOfThisMonth),
/*5.*/ @LastDayOfThisMonth = @FirstDayOfNextMonth – 1,
/*6.*/ @LastDayOfNextMonth = DATEADD(m,1,@FirstDayOfNextMonth)-1
/*
1. First, strip off the time
2. Last day of last month is the given date minus the days
3. First day of this month is the last day of last month plus one day
4. First day of next month is the first day of this month plus one month
5. Last day of this month is the first day of next month minus one day
6. Last day of next month is the first day of next month plus one month minus one day
(This can’t be last day of this month plus one month i.e. 28th Feb + 1 month = 28th Mar)
*/
/*Now set the result according to the second parameter supplied*/
SET @Result =
Case
WHEN @Day = ‘LDLM’ THEN @LastDayOfLastMonth
WHEN @Day = ‘FDTM’ THEN @FirstDayOfThisMonth
WHEN @Day = ‘FDNM’ THEN @FirstDayOfNextMonth
WHEN @Day = ‘LDTM’ THEN @LastDayOfThisMonth
WHEN @Day = ‘LDNM’ THEN @LastDayOfNextMonth
WHEN @Day = ‘FDLM’ THEN @FirstDayOfLastMonth
ELSE
NULL
END
/*and return the result*/
RETURN @Result
END
The last section of this post will have more information
I’ve always used
DATEADD(day, DATEDIFF(day, 0, [datehere]),0)
in SQL Server 2005 because I once heard it’s faster than the Convert method, which presumably converts the date to and from a varchar. Thoughts?
I tried it in sqlserver 2008 R2 but it’s not working in this version….
it’s giving below error
Msg 243, Level 16, State 1, Line 1
Type TIME is not a defined system type.
Msg 243, Level 16, State 1, Line 2
Type DATE is not a defined system type.
Dear Venkat
I tried the below queries in SQLServer 2008 R2 and it is working fine..
select getdate()
select cast(getdate() as date)
select cast(getdate() as time)
select convert(date,getdate())
select convert(time,getdate())
select CONVERT(varchar(10),getdate(),101)
select CONVERT(varchar(10),getdate(),108)
May be this helps..
Let me know if you have any difficulties.
Thank u Dave.
Your site has saved me tons of times ^_^
Hi,
If we are using the getdate() in a script which is used in an application. Will it take the server date or will it take the user’s System date??
i want to display one date column of all the values expect to date below dates
Hi
SQL Server Management Studio Express
I need to write a stored procedure, which has the same result as in Crystal Reports XI.
In Crystal Reports is like that:
@record selection
…..
and ({@Year}={?Year} or {@year}=({?Year}-1))
and date({@dep arr})<date(year(currentdate),month(currentdate),1)
where the formulas are:
@dep arr
if {M.MType} ="Import" then {M.ArrivalDate}
else if {M.MType} ="Export" then {M.DepartureDate}
@Year
if {M.MType} ="Import" then {%yeararr}
else if {M.MType} ="Export" then {%yeardep}
%yeararr
year("M"."ArrivalDate")
%yeardep
year("M"."DepartureDate")
Please help
I want that system take current date dynamically & displays the current months all days dynamically. Is it possible then tell me
Thanks for your valuable post.
It helped me a lot.
Thanks a lot for your post, it was easy to apply and very helpful.
can u please help me how to get a date without month & year from database in oracle?
hai sir i want minus 3 hr back and current time table data display in sql
Can you get date from datetimepicker on winform, I created a S.P where I want SQL server to get getdate() , not current, but the value which is showing on my Winform datetimepicker ?
Thank you for this article, very usefull for me
Hi Pinal
Understand this will be using your current server time. If your server time set to 1pm, GETDATE() it will be 1pm.
is there any method can retrieve real online date and time beside using Server time?
Hi, how do I get the current month’s records in a view in SQL Server 2008 R2?
Dear Mr. Pinal, kindly need your help (I’m sorry for my bad English),
I’m already get result time using SQL Script : CONVERT(VARCHAR(8), DateJob, 108) as ‘Time’, for example Time1 and Time2, but the datatype result of above SQL Script is in varchar.
My Question is I want to get a time from DateJob and then substract the time result (subtract between two times : Time2-Time1), how the correct sql script?
Thanks for your help
I have float value 1.09796836411745 and need output in time 02:21:0463 …
how to get output using sql select ..i.e. select floattotime from table..
What is the correlation between two values? Explicit conversion from data type numeric to time is not allowed.