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
i have 2 date in mm/dd/yyyy formate
i want to search data b/w dates
what is easy way.
1 You should use proper DATETIME datatype to store data
2 Make sure to read this post
Hi pinal,
Convert function doesn’t work when we apply airthmatic operators with it for e.g
CONVERT(VARCHAR(10),GETDATE(),111)-CONVERT(VARCHAR(10),date,111)
You cannot subtract characters. What do you want to do?
Hi:
Can anyone please help me how to convert string ‘yyyy/mm’ as datetime yyyy/mm/dd in sql 2005 ?
Thanks in advance
select cast(‘2010/12’+’/01’ as datetime)
Hi,
what do you think about
cast
(
dateadd
(
day
,
-10,
getdate())
as
date)
Sorry!
cast ( dateadd ( day , -10, getdate()) as date)
Actually I meant:
cast(getdate() as date)
Please delete the previous two
can anybody tell me how to display only date part from database…i’ve taken datetime filed in database…
my corrent coding is
snet.XCmd x = new snet.XCmd(“select * from Personal where email=@p1”);
x.Cmd.Parameters.AddWithValue(“@p1”, Session[“email”].ToString());
DataTable dt = x.GetTable();
txtFname.Text = dt.Rows[0][2].ToString();
txtMname.Text = dt.Rows[0][3].ToString();
txtLname.Text = dt.Rows[0][4].ToString();
txtGender.SelectedValue = dt.Rows[0][5].ToString();
txtDob.Text = dt.Rows[0][6].ToString(); //i wanna show here only date
bool bt;
bool.TryParse(dt.Rows[0][7].ToString(), out bt);
txtIsFullDate.Checked = bt;
txtAdrs.Text = dt.Rows[0][8].ToString();
txtCity.Text = dt.Rows[0][9].ToString();
txtState.Text = dt.Rows[0][10].ToString();
txtPin.Text = dt.Rows[0][11].ToString();
string country = dt.Rows[0][12].ToString();
Hi Pinal,
it’s a really helpful write up on getdate()…oit helps me alot…i am workin on a script that gives previous date in output only…
select dateadd(dd, datediff(dd, 0, getdate())-1, 0);
this query gives me the output in 2011-03-08 00:00:00.000 this format…
can u help me to get the output in “08-03-2011” this format…
Abhijit
Where do you want to show formatted dates?
Sir… You are really a genious in SQL.Your SQL queries provide immense help to all. But i do have a question?
Can you give me the SQL query to retrieve data based on current date and time where my database table has two seperate fields for Date and Time whose data types are date and time(0) respectively. I am stuck in this query. I need to retrieve data from database.
And at the same time i need another query to add time of 3 hours to the current time and retrieve those datas from database.
Any help will greatly be appreciated
hey this solutn was vry helpfull to me thank u soo mch :)
select convert(varchar(10),getdate(),104)
it will return dd.mm.yyyy format
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
return same result in case of varchar and datetime input
but
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
output varies in case of varchar and datetime input
below is the example
select convert(varchar(10),convert(datetime,’5/13/2011′,111),111)
Select convert(varchar(10),’2011-05-13 14:46:34.000′,111)
both statement return different result
Hi,
I want to retrieve the records based on the column ‘INSERT_DATE’ which is having the datetime format as ‘2010-12-09 21:03:13.966’
Make sure to read this
Hi,
How to faster way to retrieve the data through select statement?
And before going to partition what are the things need to remember?
1 Make sure the indexed columns are used properly in the SELECT statement
2 Read about it in SQL Server help file
Hello Friends,
Can any one help me quickly where am doing mistake in the below queries… If I run the first below query its giving me output in 4 to 5 seconds of time…
SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”’+ @startdate + ”’AND
shpm.shipdate =”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc'
But if I take out the query from parameter @sqlQuery and run it then its taking lot of time(approximately 4 to 5minutes)
Can anyone help me what is the right Query to get it without @sqlquery parameters
INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(‘0’ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc'
Your VARCHAR(10) should be at least VARCHAR(12).
SELECT CONVERT(VARCHAR(10),’Jun 6 2011 9:35AM’,111), CONVERT(VARCHAR(12),’Jun 6 2011 9:35AM’,111)
should prove my point.
how do i retieve only the date from my table which has a column say date of birth in the datetime format
select dateadd(day,datediff(day,0,datecol),0) from table
What if you have an actual field in a table that contains both date and time of an entry and you only want the date part? E.G. the field is timelogged and it contains the date/time in the format YYYY-MM-DD 00:00:00.000
How would I go about getting only the YYY-MM-DD part?
What is the datatype of the column? If it is datetime datatype, use
select dateadd(day,datediff(day,0,datecol),0) from table