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
DECLARE @ret_string varchar (255)
DECLARE @year as varchar(10)
DECLARE @Month as varchar(10)
DECLARE @Day as varchar(10)
set @year = cast(year(getdate()) as varchar)
set @Month = cast(month(getdate()) as varchar)
set @Day = cast(Day(getdate()) as varchar)
EXEC xp_sprintf @ret_string OUTPUT, ‘%s-%s-%s’, @year, @Month, @Day
PRINT @ret_string
Result will be like this
2008-10-26
Are you using MySQL or MS SQL Server?
If it is Mysql, post at http://www.mysql.com
Answer to the ” Vrushal” @ 34
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
SELECT CONVERT(VARCHAR(26), GETDATE(), 13)
27 Oct 2008 00:17:58:423
SELECT CONVERT(VARCHAR(26), GETDATE(),22)
10/27/08 12:25:10 AM
Select Convert(char(11),getdate(),8)
SELECT right(GetDate(),7)
Sir,Plz send me the query to retrieve only time from datetime column.
select convert(varchar(10),getdate(),108)
hai
ur Website Very UseFul For Me And Us
JAI HIND
How do I count on just the date portion of a datetime field?
tnx
select dateadd(day,datediff(day,0,getdate()),0),count(*) from table
group by dateadd(day,datediff(day,0,getdate()),0)
select replace(convert(varchar(11), getdate(), 106), ‘ ‘, ‘/’) as date
result
———
date
12/Dec/2008
Testing for December 31st to get resultset of data for anything greater or equal to the 31st, but keep getting all of 2008 data.
Using:
select * from TABLE where enddate >= CONVERT(VARCHAR(10),’2008-12-31′,111)
Have you tried this?
select * from TABLE where enddate >= ’2008-12-31′
Answer for @Athar, @Preeti
Query to retrieve only time from datetime column.
select convert(varchar(10), getdate(),108) –without millisecond
OR
select convert(varchar(10), getdate(),114) –with millisecond
Hi,
I want to retrieve only the date part from datetime from the SQL database. I am using
SELECT CONVERT(VARCHAR(10),GETDATE(),111) as sowDate
But it is giving me the current date :(
Plz help me.
You need to use the actual column name
SELECT CONVERT(VARCHAR(10),date_col,111) as sowDate from your_table
I have a problem with dates that are entered in MS Access but are out-of-range like 01/02/830. Can u please help me with a query that can retrieve such dates for correction before I can import the data to SQL Server 2008.
Use isdate() function
where isdate(date_col)=0 or len(date_col)<10
Hi,
Thanks for your blog..
I have a problem in comparing 2 date fields one has the time field as (2009-01-16 12:32:50.690) and the other (2008-08-07 00:00:00.000).
I want to compare the date part of the 2 fields. I tried coverting them to varchar (101) and using the <= operator but the result set was incorrect.
I am not sure how else I can achieve this functionality of comparing the date part only.
Use this approach
dateadd(day,datediff(day,0,date1),0)=dateadd(day,datediff(day,0,date2),0)
thanx a lot….
@Ankit,
You need to use styles with convert.
Format of convert is
convert ( New Data Type, Column_Name or variable, Style)
select A.* , B.* from table_name A , table_name B
where convert ( varchar(10), A.Date_column_name , 101) = convert ( varchar(10), B.Date_column_name , 101)
Hope this helps,
IM
It is better to compare dates as dates rather than varchar to varchar. See my previous reply
Thank you sir
This topic helped me a lot but can u tell me what is the relation of sequence of the number with date format
It is used to format the datetime values in specific reginal format
Hello Sir,
I have one date problem regarding importing XML Date to sql Server 2005.
I am passing DataTable to SP from .NET Application C#..
the columns with DateTime data type having
Default value as :
col.DefaultValue = DateTime.Today.Date;
and
string strXML = ds.GetXml();
and passing this strXML to SP.
My XML have tag like :
2009-02-28T00:00:00+05:30
SP is not accepting the date I pass above to datetime field of SQL server 2005.
can you plz. suggest wt could i do for this..
I tried for
select cast(2009-02-28T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,
‘DATETIME’)
but it is giving NULL Value.
Thanks.
Reagerd
Either you need to remove +5:30 from your_date or convert it to actual date by removing +5:30 and adding 5:30 hours
Hello Sir..
My above reading DataTime from XML get solve using above mentioned casting.
Due to my some mistake in Query I was getting NULL value.
Well, but now I am fasing new problem for this DateTime only of XML…
I have two tags in my XML datatype string which are
2009-01-01T00:00:00+05:30
2009-12-31T00:00:00+05:30
Actualy these dates are 01/01/2009 and 31/12/2009 respectively
But In OpenXML when I convert these dates it gives me different OUTPUT
(Here with I am showing you in select )
select
cast(‘2009-01-01T00:00:00+05:30’ as xml).value(‘xs:dateTime(.)’,’DATETIME’) as StartDate,
cast(‘2009-12-31T00:00:00+05:30’ as xml).value(‘xs:dateTime(.)’,’DATETIME’) as EndDate
OUTPUT is :
2008-12-31 18:30:00.000 2009-12-30 18:30:00.000
respectively ..
Can you please suggest Why this is..?
Regards
Namasthe Pinal
Could you tell me why my DB return is different?
select getdate()
3/3/2009 3:11:56 PM
I wish it was
3/3/2009 15:11:36
Why does formation matter at query analyser?
When you use front end application, do formation there
Also make sure to read this post to understand datetime column