2008-09-22 03:31:33 8 Comments
SELECT GETDATE()
Returns: 2008-09-22 15:24:13.790
I want that date part without the time part: 2008-09-22 00:00:00.000
How can I get that?
Related Questions
Sponsored Content
50 Answered Questions
[SOLVED] How do I get the current date in JavaScript?
- 2009-10-07 11:39:02
- Suresh Chaganti
- 2514837 View
- 2177 Score
- 50 Answer
- Tags: javascript date
47 Answered Questions
[SOLVED] How do I import an SQL file using the command line in MySQL?
- 2013-07-16 00:43:48
- Jaylen
- 3053138 View
- 1851 Score
- 47 Answer
- Tags: mysql sql command-line import
39 Answered Questions
[SOLVED] Add a column with a default value to an existing table in SQL Server
- 2008-09-18 12:30:04
- Mathias
- 2737723 View
- 2666 Score
- 39 Answer
- Tags: sql sql-server sql-server-2005 sql-server-2000
38 Answered Questions
38 Answered Questions
[SOLVED] How do you get a timestamp in JavaScript?
- 2008-10-21 09:29:33
- pupeno
- 2735143 View
- 3855 Score
- 38 Answer
- Tags: javascript date datetime timestamp unix-timestamp
54 Answered Questions
[SOLVED] How to format a JavaScript date
- 2010-08-23 23:28:26
- leora
- 3348926 View
- 1968 Score
- 54 Answer
- Tags: javascript date date-format time-format
30 Answered Questions
[SOLVED] How to check if a column exists in a SQL Server table?
- 2008-09-25 12:34:00
- Maciej
- 1156951 View
- 1798 Score
- 30 Answer
- Tags: sql-server sql-server-2008 tsql sql-server-2012 sql-server-2016
15 Answered Questions
[SOLVED] How to Delete using INNER JOIN with SQL Server?
- 2013-05-10 11:38:21
- nettoon493
- 1276879 View
- 1188 Score
- 15 Answer
- Tags: sql sql-server sql-server-2008 inner-join sql-delete
33 Answered Questions
[SOLVED] How do I UPDATE from a SELECT in SQL Server?
- 2010-02-25 14:36:53
- jamesmhaley
- 4107855 View
- 3554 Score
- 33 Answer
- Tags: sql sql-server tsql select
14 Answered Questions
[SOLVED] DateTime2 vs DateTime in SQL Server
- 2009-08-26 11:45:10
- Mikeon
- 467216 View
- 730 Score
- 14 Answer
- Tags: sql sql-server tsql datetime datetime2
30 comments
@Mohammad Neamul Islam 2019-11-24 08:00:22
In this case, date only, you we are gonna run this query:
SELECT CONVERT(VARCHAR(10), getdate(), 111);
@Stephon Johns 2014-03-26 12:39:38
If you need the result as a
varchar
, you should go throughwhich is already mentioned above.
If you need result in date and time format, you should use any of the queries below
@aku 2008-09-22 03:34:16
On
SQL Server 2008
and higher, you shouldCONVERT
to date:On older versions, you can do the following:
for example
gives me
Pros:
varchar
<->datetime
conversions requiredlocale
As suggested by Michael
Use this variant:
SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
Output:
@eddiegroves 2008-09-22 03:48:09
Is this way better or worse performance wise than using the convert methods other have suggested? Or is it negligible?
@aku 2008-09-22 03:54:04
My method works faster. It doesn't require conversions to varchar and allows efficient date calculations
@Dane 2008-09-22 04:04:41
+1 Looks like this one is 35% faster than the double convert() method commonly used (which I also have used for years). Nice one.
@Cade Roux 2008-09-22 04:11:16
If this is 35% than the CONVERT method, you've got to wonder how much faster a built-in truncate would be - this has to be the most common datetime-related operation I ever do - I'm going to see about switching to this mechanism.
@Jim Birchall 2008-09-24 08:25:57
The only downside I can see to your solution is that unless you know what it is doing it is a bit obtuse. Using the double convert method makes your intentions more obvious to futire code maintainers. BTW I have not downvoted you. I think I'll start using your method too. Thankyou @aku
@ErikE 2010-09-12 23:15:24
+1 You may be interested to see Ricardo C's edited answer (since it is community wiki and factually incorrect, I corrected it). You also got a prop to your question.
@ErikE 2010-09-13 00:51:51
Also don't miss this post showing performance testing results.
@ErikE 2012-08-17 22:03:11
@pilavdzice Setting a datetime to midnight of that day does LEAVE OFF THE TIME. What result are you expecting? The
datetime
data type cannot have no time at all. I think you are confusing data storage with user presentation. If all you want is a way to show a user a string that has no time portion (not zeroes, just blanks) then you simply wantConvert(varchar(30), @Date, 101)
or something similar. See SQL Server Books Online • Cast and Convert for more info.@N t 2013-05-20 14:10:40
Worth noting that this does not extend to aggregating monthly, or yearly data. You have to alter the terms in order for that to work. SELECT DATEADD(mm,DATEDIFF(mm, 0, @YourDate),0) is extensible for mm/yy iirc
@Praveen 2013-06-15 11:13:41
@aku Is there a way to get only Datepart as "2008-09-22" and not "2008-09-22 00:00:00.000" without converting it into VARCHAR. Now I'm using
CONVERT(VARCHAR(10), @dateTime, 101) AS MyDate
@Magnus 2013-06-21 15:08:48
@user1671639 the datetime data type always contains both a date and a time, you can't sensibly store one without the other - unless you're using SQL Server 2008, in which case there are also separate 'date' and 'time' data types. If you use CONVERT() like that, you really want a string for later use, so you'll be stuck doing it like that - although it'd be better if you used date formatting functions instead of cutting the date off - or via
CAST(... AS DATE)
orCONVERT(DATE, ...)
, which has been mentioned quite often on this very page.@Michael 2014-08-14 16:08:29
I recommend changing the answer to
SELECT DATEADD(dd, DATEDIFF(dd, 0, @your_date), 0)
because thendd
can be swapped out for any otherdatepart
keyword to truncate yourdatetime
at an arbitrary level.@karthik kasubha 2019-03-24 17:34:46
select cast(createddate as date) from table where createdate is your datetime column
@mokh223 2018-10-05 08:18:37
@ChrisM 2019-07-29 12:53:59
I think you have copied in 2 select statements but copied the second select inside the first select.
@ChrisM 2019-07-30 10:32:57
If you want the date to show
2008-09-22 00:00:00.000
then you can round it using
This will show the date in the format in the question
@Jithin Joy 2019-05-28 08:34:47
The easiest way would be to use:
SELECT DATE(GETDATE())
@Aubrey Love 2019-04-23 17:56:52
Wow, let me count the ways you can do this. (no pun intended)
In order to get the results you want in this format specifically:
2008-09-22
Here are a few options.
So, I would suggest picking one you are comfortable with and using that method across the board in all your tables.
All these options return the date in the exact same format. Why does SQL Server have such redundancy?
I have no idea, but they do. Maybe somebody smarter than me can answer that question.
Hope this helps someone.
@ankit soni 2019-03-30 11:01:20
you can use like below for different different type of output for date only
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 103))
-----dd/mm/yyyySELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
------mm/dd/yyyySELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))
@karthik kasubha 2019-03-24 17:36:20
createdate is your datetime column , this works for sqlserver
@CAGDAS AYDIN 2018-11-21 11:39:50
My Style
@Nescio 2008-09-22 03:33:29
Try this:
The above statement converts your current format to
YYYY/MM/DD
, please refer to this link to choose your preferable format.@eddiegroves 2008-09-22 03:41:19
This returns '2008/09/22' for me
@Flea 2013-07-10 20:47:39
SELECT CONVERT(VARCHAR(10),GETDATE(),101) is
mm/dd/yyyy
format.@Simon_Weaver 2013-09-14 00:34:57
if you're sorting based on the raw text value (outside of the DB) then the 'japanese' format is better
@Spider 2017-10-21 04:02:23
My common approach to get date without the time part..
@Amar Srivastava 2017-09-08 05:24:19
Simply you can do this way:
Outputs as:
Or simply do like this:
Result:
@kaub0st3r 2016-05-24 09:36:18
On SQL Server 2000
@Art Schmidt 2016-05-13 21:00:11
If you are assigning the results to a column or variable, give it the DATE type, and the conversion is implicit.
@Rushda 2011-09-22 12:21:32
@Somnath Muluk 2016-08-24 07:36:10
If you are using SQL Server 2012 or above versions,
Use
Format()
function.There are already multiple answers and formatting types for SQL server. But most of the methods are somewhat ambiguous and it would be difficult for you to remember the numbers for format type or functions with respect to Specific Date Format. That's why in next versions of SQL server there is better option.
Culture option is very useful, as you can specify date as per your viewers.
You have to remember d (for small patterns) and D (for long patterns).
1."d" - Short date pattern.
2."D" - Long date pattern.
More examples in query.
If you want more formats, you can go to:
@xbb 2016-07-20 15:58:29
Starting from SQL SERVER 2012, you can do this:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000')
@Kris Khairallah 2016-05-21 09:24:33
Date:
Time:
@BenR 2008-09-24 13:02:21
SQLServer 2008 now has a 'date' data type which contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:
@misteraidan 2011-08-25 00:01:41
There is also the 'time' data type in SQL2008 which answers the other half of the question of separating date and time.
@UnhandledExcepSean 2014-07-03 12:48:46
FYI, I benchmarked different methods of trimming off time from dates and this was the fastest method. Granted the difference was small, but it was clearly faster over a large # of executions.
@Dr. MAF 2015-11-19 09:10:23
wt about sqlserver 2005??
@Frosty840 2017-07-31 07:24:00
@Dr.MAF Completing the circle, the pre-2008 answer is here: stackoverflow.com/questions/113045/…
@Krishnraj Rana 2016-04-13 10:46:30
Okay, Though I'm bit late :), Here is the another solution.
Result
And if you are using SQL Server 2012 and higher then you can use
FORMAT()
function like this -@Zack 2016-04-20 21:20:47
Your first example still has a time component. The point of the question was how to remove that.
@Shyam Bhimani 2016-03-29 22:56:51
You can simply use the code below to get only the date part and avoid the time part in SQL:
@David Faber 2018-03-24 12:48:50
Not only is it for Oracle, not MS SQL - it's not even correct. To get the date part only from Oracle, one would use
TRUNC(SYSDATE)
@etni 2014-11-19 20:46:35
@Andriy M 2014-11-19 21:47:27
This suggestion has been covered by other answers (more than once).
@abatishchev 2011-01-31 09:44:38
If using SQL 2008 and above:
@Fredrick Gauss 2012-12-13 16:10:45
Msg 243, Level 16, State 1, Line 1 Type date is not a defined system type.
@abatishchev 2012-12-13 20:01:29
@FredrickGauss: What type, Date? What version of SQL Server do you use?
@Nick 2015-09-24 19:18:42
Beware! declare @date1 datetime = '2015-09-30 20:59:59.999'; select cast(@date1 as date) returns '2015-10-01'
@Nick 2015-09-24 20:51:07
@abatishchev sorry, that should have been
declare @date1 datetime = '2015-09-30 23:59:59.999';select cast(@date1 as date)
@abatishchev 2015-09-25 01:33:16
@Nick: this is the issue with DateTime. use
DateTime2
instead and it works fine. sqlfiddle.com/#!6/9eecb7/2833@Frédéric 2015-12-11 17:07:38
@Nick, to complement abatishchev response, your @date1 is indeed
2015-10-01
, due toDateTime
limitations. Try without any cast toDate
, it yields2015-10-01
too!declare @date1 datetime = '2015-09-30 23:59:59.999';select @date1
=>2015-10-01
@NicVerAZ 2015-12-29 17:04:48
One of these easy to remember SQL tricks. As Mike says, only 2008 onward but, if you find a 2005 and previous DB somewhere, you may have a lot of issues :)
@abatishchev 2015-12-29 18:04:30
@NixVerAZ I believe there are exactly 0 reasons to run SQL Server 2005 in late 2016. This is pure idiocy, isn't it? A good sign of something terribly wrong there.
@user1172173 2017-06-09 18:05:55
I like the use of the ANSI std "CAST()" - if portability is a concern (or even achievable) these days :). Also preferred if wanting to preserve precision: msdn.microsoft.com/en-us/library/ms187928.aspx
@Luc VdV 2017-10-18 07:28:57
@abatischchev - 1 reason that applies to more instances than you'd hold for possible: management refusing to approve the budget for updating antiques as long as someone manages to keep them running. Even SQL Server 2000 running on Windows 2000 is still alive. SQL Server 2005 Express: about 50 instances in my company alone (no, not mine, the one I work for).
@Luc VdV 2017-10-18 07:37:01
The '2015-09-30 20:59:59.999' issue doesn't seem to apply to Sql Server 2016 anymore.
@David Faber 2018-03-24 12:47:33
One can go even further with the ANSI standard by using
SELECT CAST(CURRENT_TIMESTAMP AS DATE)
(CURRENT_TIMESTAMP
is equivalent toGETDATE()
).@Anderson Silva 2013-11-17 17:36:27
To obtain the result indicated, I use the following command.
I holpe it is useful.
@Binitta Mary 2015-09-01 13:02:45
@Gerard ONeill 2015-04-30 19:00:38
I favor the following which wasn't mentioned:
It also doesn't care about local or do a double convert -- although each 'datepart' probably does math. So it may be a little slower than the datediff method, but to me it is much more clear. Especially when I want to group by just the year and month (set the day to 1).
@Gordon Bell 2008-09-22 03:35:23
Edit: The first two methods are essentially the same, and out perform the convert to varchar method.
@eddiegroves 2008-09-22 03:48:48
These methods are all great, but which single one do you suggest using?
@Michael 2014-08-14 16:02:18
Note that the "correct" version of the top two is
select dateadd(dd, datediff(dd, 0, getdate()), 0)
, because thedd
s can then be swapped out for any of thedatepart
keywords to clip the date at any segment you choose. (Also note thatdd
is just an abbreviation forday
.)@lit 2016-01-15 18:14:06
I know this is old, but I do not see where anyone stated it this way. From what I can tell, this is ANSI standard.
It would be good if Microsoft could also support the ANSI standard CURRENT_DATE variable.
@brianary 2019-12-02 18:26:31
select {fn current_date()} as today
works for me.@lit 2019-12-08 17:28:39
@brianary - That's nice, but it is not ANSI SQL.
@brianary 2019-12-08 18:28:22
That's fair enough, and your answer is nicely portable, but I figured as long as we're working around T-SQL, this also works (and shows that implementing ANSI CURRENT_DATE would be trivial for MS).
@Surekha 2015-07-08 06:28:44
Date(date&time field) and DATE_FORMAT(date&time,'%Y-%m-%d') both returns only date from date&time
@The1nk 2015-12-17 18:31:56
The question states SQL Server. This seems like MySQL?