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
Thanks Mr.Pinal. This is Praveen from Xtend Technologies, cochin. I was looking for a solution to get the distinct date part of a date time field in SQL server.
Hello Praveen,
If you are using SQL Server 2008 then you can get distinct date by using below statement:
SELECT DISTINCT CONVERT(DATE,(datetime_column)) FROM tableName
Otherwise use below:
SELECT DISTINCT CONVERT(VARCHAR(10),(datetime_column)) FROM tableName
Regards,
Pinal Dave
Thanks! Short and sweet and it works!
I have a date column, like ‘yyyy-mm-dd’, so i want to retrieve the data from table with only a perticular year..
Please help me..
Thanx..
select * from where datepart(yyyy,) = ‘2009’
select * from Table where datepart(yyyy,DateColumn) = ‘2009’. Format i was trying to send is not displaying correctly. plz check this if it could help you.
Use the following criteria in WHERE clause:
DATEPART(yy, date_column) = perticular_year
If you want to mkae use of the index of date_column,
where
date_column>=dateadd(year,@year-1900,0) and
date_column<dateadd(year,@year-1900+1,0)
Hope this might help u..
select * from where datepart(yyyy,) = ‘2009’
Thanks for this post. Saved me a lot of time.
i need help in visual basic and sql. when i click date in vb components of calendar1 in selected date tat should be access and show through the msflexgrid or another form.
Please help me soooooon!
where
date_column>=dateadd(day,datediff(day,0,selected_date),0) and
date_column<dateadd(day,datediff(day,0,selected_date)+1,0)
I NEED TO KNOW THAT,THERE IS A FIELD CALL DATE,AND ANOTHER HAVE VALUE
DATE VALUE
DATE1 100
DATE2 200
DATE3 300
DATE1 200
DATE3 100
I WANT TO GET RESULT LIKE THIS
DATE1 TOTAL VALUE= 300
DATE 2 TOTAL VALUE=200
……….. .
HOW CAN I DO THIS IN PHP CORD
@Upekha
This is a standard GROUP BY query.
SELECT date, SUM(Value) FROM table GROUP BY date;
Hello,
I am doing a project in access 2007.
I have a table with two fields: startdate and enddate.
i wanted to have an sql statement that will return all the dates within startdate and enddate.
Is this possible?
Thank you very much.
hi hurb,
maybe the below soln will help you,
create table #tempdate1(date datetime)
declare @date1 datetime
set @date1 = ‘2010-02-10’
while (@date1 <= '2010-02-15'))
begin
insert into #tempdate1 select @date1
set @date1 = dateadd(dd,1, @date1)
end
select * from #tempdate1
drop table #tempdate1
you will get the dates from 2010-02-10 to 2010-02-15 (based on the condition in the while, where changing the condition in the while you can get different results based on the condition).
you can have this one in a SP and have two input parameters such as startdate and enddate and pass the values to these variables and you can achieve your required soln by minimal changes in the above code.
Hello Hurb,
Union two statements, one for each column as below:
select startdate from table
union
select enddate from table
Let us know if this doesn’t fulfill your requirement.
Regards,
Pinal Dave
Great tip! I went with the DateAdd()
Thanks a Lot !!!!! Pinal
Thanks
Hello sir,
I want to set only the date in 2 variables and compare them.
I used the following code
declare @ydate as datetime,@ctdate as datetime,@dtdiff as datetime
set @ydate=current_timestamp-1
set @ctdate=current_timestamp
select @ydate
select @ctdate
set @dtdiff=datediff(dd,@ydate,@ctdate)
select @dtdiff
and got the output as
@ydate:
2010-03-15 10:07:59.107
@ctdate:
2010-03-16 10:07:59.107
@dtdiff:
1900-01-02 00:00:00.000
I don’t want this output . I need compare the values and find whether the @ctdate is greater than @ydate.
Please help me out.
Hello Koushuikha,
Change the datatype of @dtDiff variable to int.Then use the IF clause to indentify the higher one.
Regards,
Pinal Dave
Thanks pinaldave. I really help me.
Thanks pinaldave. I really help me…
If I need to get a rolling weekly average (week starting on Monday) how would i write that?
select
COUNT(*) as numOfFiles,
NumberOfLoans as numRecords,
convert(char(10),dateadd(week,-1,dateadd(day,-1*(datepart(weekday,[CreateDtTm])-1),[CreateDtTm])),101) as WeekStartDate,
convert(char(10),dateadd(day,-1*(datepart(weekday,[CreateDtTm])-1),[CreateDtTm]),101) as WeekEndDate
from testtable
I can only get my start and end of the week, how would I go about calculating the daily/weekly/monthly averages? Any help in the right direction is greatly apprecaited…
thanks
Hi,
i want to know how i can creat field in table wich it’s data type is date (tt/ee/wwww)
Can you be more specific?
What do tt, ee and wwww mean?