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
Thank you :)
Hi,
I’d like to convert a datetime field to varchar in YYYY MMM DD format.
I’m expecting the same result as the following SQL:
select SUBSTRING(CONVERT(varchar(20),GetDate(),113),8,4) + ‘ ‘ + SUBSTRING(CONVERT(varchar(20),GetDate(),113),4,3) + ‘ ‘ + SUBSTRING(CONVERT(varchar(20),GetDate(),113),1,2)
— output: 2011 Aug 18
Is there any easy way (something like SELECT CONVERT(VARCHAR(12), GETDATE(), 106)) of doing this without substring?
Thanks in advance.
Sri
The correct way of doing this is to do at front end application
If you want to do it by sql, use
select datename(year,getdate())+’ ‘ +convert(varchar(6),getdate(),9)
Thank you, Madhivanan. I need to do it in the SQL.
The SQL is very simple and working fine.
Thanks again,
Sri
I tried both solutions:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
And I got better performance in option 1.
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I get 3 second executing the query with option 1 and with option 2 was around 5 second.
It is becuase of VARCHAR convertion. SQL Server takes extra time to do that.
i have problem with accending date…. my current syntax is
SELECT * FROM mytablename ORDER BY datefield ASC. right now my datefield using VARCHAR…
my result are :
01/02/2009
03/01/2009
04/06/2009
05/03/2009
06/12/2008
07/02/2009
result should be :
06/12/2008
03/01/2009
01/02/2009
07/02/2009
can anyone teach me the simple way to solve this problem and give some help please…. ^_^
Try this
SELECT * FROM mytablename ORDER BY cast(datefield as datetime) ASC
Also always use proper DATETIME datatype to store dates
this is very helpfull :)
A Lot quicker: Convert(DateTime, convert(int, GetUtcDate()))
Ok, I guess not so short:
The convert function is rounding the value. So dates that has passed noon will become tomorrows dates — oops.
thus correct value would be:
Convert(Int, convert(float, GetUtcDate()))
Converting from float to int will truncate the value.
This is most reliable method
select dateadd(day,datediff(day,0,getdate()),0)
how to convert nvarchar data in the format dd-MM-yyyy into datetime or smalldatetime while runtime?
select convert(datetime,your_nvarchar_date,103)
how to select only date part
when selecting two dates using Between clause
How to select only date part without time..can some one help me.
Thanks.
What about using the numerical rappresentation of a DateTime?
CONVERT(INT,GetDate()) give the date part of datetime rappresentation.
So my suggestion is to use CONVERT(DATETIME, CONVERT(INT,GetDate())).
Another advantage to integer cast versus string cast is that it’s simpler to use in ordering, filtering and so on (think about BETWEEN).
I have a vachar field in DB to store dates (which is actually creating problem).Now i can’t change the field type as i contains real data and lots of other application uses this DB. i want to design a query which compares dates i.e select * form Emp where joining_date(its in varchar type) between ‘datefrom’ and ‘dateto’
thnx its done…
What is the dateformat? Depending on it you can convert it and compare
where cast(col as datetime) between datefrom and dateto
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
hi can someone help me get date values in my table
i inserted 10-mar-05 but system gave me 1900-01-03
Thanks
i got it.
how i select date value from sql?. I used ms acces i used ‘ datevalue’ key word. but in sql ‘to_date’ key word is not working. any one please help me?. How i retrieve a date value in sql through java?. Please help me.
Hi i have a issue can you please help.
I have 35 K records (email address) i need to pull out only the domain part (after @) and update to another column.
How to do this?
Thnks a ton
Very Very Thanks..
Sir,
I want select data from table orber by register date in desc order and mssql register date field type is nvarchar.
what is query i need to use…
Please help me urgently….
Thanks
Sethu
Convert it to datetime in order by clause
ORDER BY cast(register_date as DATETIME)
Thanks alot.. its works fine…