Obtain the date of datetime field
-
How can I obtain the only date part of a datetime field?
Thanks for your help
Viky
-
Something like this should do it:
select convert(varchar(10), getDate(), 120)The above returns 2010-06-02 when ran at any time today.
Hope this helps.
-
Oleg Netchaev (6/2/2010)
Something like this should do it:select convert(varchar(10), getDate(), 120)The above returns 2010-06-02 when ran at any time today.
Hope this helps.
For a faster method, check out the "Common Date Routines" link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
I have read the blog by Lynn Pettis in the past, the conversions are definitely very useful. The convert function is simple enough though and should be OK to use for occasional conversion of something like a datetime variable. If there is a query returning gazillion records then it is definitely better to apply Lynn's logic to the values in the column for performance reasons.
Oleg
-
Oleg Netchaev (6/2/2010)
I have read the blog by Lynn Pettis in the past, the conversions are definitely very useful. The convert function is simple enough though and should be OK to use for occasional conversion of something like a datetime variable. If there is a query returning gazillion records then it is definitely better to apply Lynn's logic to the values in the column for performance reasons.Oleg
Only having a couple of rows should never be justification for doing it wrong because the number of rows can change even if it's by someone borrowing the code for a different app.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (6/3/2010)
Only having a couple of rows should never be justification for doing it wrong because the number of rows can change even if it's by someone borrowing the code for a different app.Is it so terribly wrong what I originally answered? There was a simple question:
How can I obtain the only date part of a datetime field?
To which I quickly typed a simple answer:
select convert(varchar(10), getDate(), 120)Convert is a valid T-SQL function, the question was clearly about returning the date portion of the datetime variable, so I assumed that it was specifically asking about returning date portion only without spelling out any time related details. Lynn's post has many very useful statements but because they do return time portion as well (in the form of 00:00:00.000), I figured that it is perfectly acceptable to use the convert, which is a handy function:
select convert(varchar(10), getDate(), 120) -- 2010-06-03 (yyyy-mm-dd format)select convert(varchar(10), getDate(), 101) -- 06/03/2010 (standard US format)
select convert(varchar(10), getDate(), 103) -- 03/06/2010 (standard English format)
Oleg
-
Actually, the op said nothing about a SELECT and you don't know how your answer will be used.
If it's for a SELECT to return a result set to the GUI, the answer should be to do the formatting in the GUI so local time settings and formatting can be used.
If it's for a criteria in a join, the answer should be "don't do it that way because it's not likely an index will be able to be used".
If it's for a conversion in a large batch file the answer should be "don't do it that way because CONVERT is relatively a lot slower that DATEADD/DATEDIFF.
In fact, there's only one reason to ever convert a date to a string that won't impact performance but you don't know that because you didn't take the time to ask. May the SQL god's help the OP if the poor bugger intends to do something totally whacko like actually store the date as a varchar in a table.
So, yeah... what you did actually was terribly wrong... The OP asked for a drink and you handed him a glass of high fructose corn syrup and you told the OP that it's ok to hit the wrong notes on a piano because it's a short song. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (6/4/2010)
Actually, the op said nothing about a SELECT and you don't know how your answer will be used.If it's for a SELECT to return a result set to the GUI, the answer should be to do the formatting in the GUI so local time settings and formatting can be used.
If it's for a criteria in a join, the answer should be "don't do it that way because it's not likely an index will be able to be used".
If it's for a conversion in a large batch file the answer should be "don't do it that way because CONVERT is relatively a lot slower that DATEADD/DATEDIFF.
In fact, there's only one reason to ever convert a date to a string that won't impact performance but you don't know that because you didn't take the time to ask. May the SQL god's help the OP if the poor bugger intends to do something totally whacko like actually store the date as a varchar in a table.
So, yeah... what you did actually was terribly wrong... The OP asked for a drink and you handed him a glass of high fructose corn syrup and you told the OP that it's ok to hit the wrong notes on a piano because it's a short song. 😉
Which leads us back to what really should have ben the first question asked of the OP, "what are you trying to do?"
-
I found this an interesting discussion, particularly as this is something I've had to do a few times in the past.
We always try to keep the datetime as a datetime and let the GUI format it, as the database shouldn't need to know about the users datetime formats, etc.
I'm not excusing the "convert(varchar" method (if nothing but for the obvious index issues and that you don't get a date back), but I am interested in any performance tests you may have in a straight output, rather than in joins, etc, particularly as in the tests we ran some years ago (which I have just re-run to confirm the results) we found that:
convert(datetime, convert(nvarchar, @dt_date,103), 103)is marginally faster than:
dateadd(day, datediff(day, 0, @dt_date), 0)Although, when I say marginally, it really is marginal. Over several million runs we see between 0.005ms to 0.01ms advantage to using a double convert over the date add/diff method.
Regards,
Ian Wilkinson
-
sgtwilko (6/5/2010)
I found this an interesting discussion, particularly as this is something I've had to do a few times in the past.We always try to keep the datetime as a datetime and let the GUI format it, as the database shouldn't need to know about the users datetime formats, etc.
I'm not excusing the "convert(varchar" method (if nothing but for the obvious index issues and that you don't get a date back), but I am interested in any performance tests you may have in a straight output, rather than in joins, etc, particularly as in the tests we ran some years ago (which I have just re-run to confirm the results) we found that:
convert(datetime, convert(nvarchar, @dt_date,103), 103)is marginally faster than:
dateadd(day, datediff(day, 0, @dt_date), 0)Although, when I say marginally, it really is marginal. Over several million runs we see between 0.005ms to 0.01ms advantage to using a double convert over the date add/diff method.
Regards,
Ian Wilkinson
I'd like it very much if you'd post the performance testing you used because your findings just don't match mine. If you're testing returned results to the display, then I can see such a thing happening because the display is the "great equalizer" when it comes to run times.
The following code takes the display totally out of the picture and measures only the time it takes to do the conversions. It first builds a million rows of test data and then does the conversions as separate batches.
--===== Setup a million row test table in a nice safe place that everyone hasUSE TempDB;
SELECT TOP 1000000
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM Master.sys.ALL_Columns t1,
Master.sys.ALL_Columns t2; --Lack of join criteria makes this a CROSS-JOIN
GO
--===== DATEADD/DATEDIFF
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== DOUBLE CONVERT
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, CONVERT(NVARCHAR, SomeDate,103), 103)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== Housekeeping
DROP TABLE dbo.JBMTest;
GO
Here're the results captured by SQL Server Profiler...
The way I look at it is the DATEADD/DATEDIFF method is nearly 3 times faster than the Double CONVERT method. My question would be, why would anyone intentionally use the slower of the two? 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (6/4/2010)
So, yeah... what you did actually was terribly wrong... The OP asked for a drink and you handed him a glass of high fructose corn syrup and you told the OP that it's ok to hit the wrong notes on a piano because it's a short song. 😉Well, at least I am glad that you picked me as a subject of your criticism. Compare this to this post: http://www.sqlservercentral.com/Forums/Topic931902-338-1.aspx where someone named Shabba asks:
I would like the results of this simple query to show just the 'Created Date' in dd/mm/yyyy format
and someone named Steve suggests CONVERT(char(10), crdate, 103) as 'Created Date' receiving
Cheers Steve. Easy when you know how! Thanks
reply.
It already happened in the past when someone asked about returning the time portion only from the difference of 2 datetime column values and I supplied similar answer. I can repeat myself by saying that I am totally agree with you about the delegating all formatting activities to GUI, but sometimes it just so happens that when someone is tasked with a throw-away-do-it-now report including the date difference in hh:mm:ss format with query results pane serving as the GUI then formatting must take place in the query itself.
Oleg
-
Heh... can't cover the world... just one post at a time. And please don't think I'm picking on you. Despite how I sound (my "tone of voice" in emails is absolutely dreadful and my apologies), I'm just trying to teach a better way.
Shabba's request was different, though. He actually wanted a display. If he had a GUI, I would have told him to do the display conversion in the GUI.
As a side bar, we still don't know what the OP on this thread actually wants. I'm also looking forward to the performance testing that you good folks did. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There is no way I can possible dislike when my post is commented by you Jeff. When I wrote that I liked that you picked on my post, I actually meant it. My introduction to T-SQL began from reading Ken Henderson's (may he rest in peace) T-SQL book for version 7, and it went downhill from there. Though it was a number of years ago, I know that there is much more to learn, so your comments are much appreciated.
Oleg
-
Oleg Netchaev (6/7/2010)
... When I wrote that I liked that you picked on my post, I actually meant it....Oleg, I'm glad you clarified this... I thought you were saying it sarcastically! 😉
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
sistemas_casinomnes (6/2/2010)
How can I obtain the only date part of a datetime field?If you find yourself needing the date-only portion frequently, it might make sense to pre-compute it.
You can minimise the overhead by using an indexed computed column.
Until you add the index, the computed column requires no storage.
Example follows.
CREATE TABLE
#JustAnExample
(
record_id INTEGER IDENTITY PRIMARY KEY,
some_date DATETIME NOT NULL,
just_the_date AS
DATEADD(DAY, DATEDIFF(DAY, 0, some_date), 0)
);
GO
INSERT #JustAnExample (some_date)
SELECT TOP (100000)
DATEADD(SECOND, 0 - ABS(CHECKSUM(NEWID())), CURRENT_TIMESTAMP)
FROM master.sys.all_columns AC1,
master.sys.all_columns AC2,
master.sys.all_columns AC3;
GO
CREATE INDEX nc1 ON #JustAnExample (just_the_date);
GO
SELECT *
FROM #JustAnExample
WHERE just_the_date = '20100601';
GO
DROP TABLE #JustAnExample;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply