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
It was a great help! Thanks!
Thank you so much, but is it possible to display just time?
If you use front end application, show time part there. Otherwise
select convert(varchar(10),getdate(),108)
please help me i want to get data between two dates im writing
select * from Test_Date where t_Date>=convert(datetime,’02/02/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
can not get the correct data
The proper method is
select * from Test_Date
where
t_Date>’20090202′ and
t_Date<'20100203'
The proper method is
select * from Test_Date
where
t_Date>=’20090202′ and
t_Date<'20100203'
please help me i want to get data between two dates im writing ,these query act normaly but not correct data
select * from Test_Date where t_Date>=convert(datetime,’02/02/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
can not get the correct data
and the datatype of column t_Date is varchar(50)
Three things
1 Always use proper DATETIME datatype to store dates
2 Work on DATETIME and not VARCHARs
3 You should always use unambiguious format YYYYMMDD HH:MM:SS
So your query should be
select * from Test_Date where convert(datetime,t_Date,103)>=’20090202′ and convert(datetime,t_Date,103)<'20090203'
Hi Rabin,
select *
from Test_Date
where CAST(t_Date AS DATETIME)>=convert(datetime,’02/02/2009′,103) and CAST(t_Date AS DATETIME)<=convert(datetime,'02/02/2010',103)
let me know if it helps you.
Tejas
I have a datetime column in database in ‘9/29/2009 1:00:10 AM’ this format. Based on this coulmn value i need to get other column value but datetime value would be in ‘9/28/2009′ format.
How I can achive following query
select * from tblFirst where Startdate=’9/29/2009’
but in Database Startdate column has value as ‘9/29/2009 1:00:10 AM’
Could you help me?
Thanks in advance
Try this
select * from tblFirst
where
Startdate>=’20090929′ and
Startdate<’20090930′
Hi Mrs Tejas Shah thank for you
ok your code is correct but actuall not get
the specific result because if we put from date
’01/03/2009′ to date ’01/01/2010′ we cannot get the
record has date ’02/03/2009′ why ?
Notes: i’m save date in database as dd/mm/yyyy
date column in database has datatype varchar can you help in this code or another way to get the good result.
Thank again for you
You should always use the format YYYYMMDD which is unambigous
@Rabih,
Check if this could help.
Create Table #Example ( Eid int, EmpName varchar(50), DateJoined varchar(50))
Insert into #Example values ( 1, ‘Bill Gates’ , ’01/01/2009′)
Insert into #Example values ( 2, ‘Gustavo’ , ’01/02/2009′)
Insert into #Example values ( 3, ‘Catherine’ , ’01/03/2009′)
Insert into #Example values ( 4, ‘Kim’ , ’01/04/2009′)
Insert into #Example values ( 5, ‘Humberto’ , ’01/06/2009′)
Insert into #Example values ( 6, ‘Pilar’ , ’01/08/2009′)
Insert into #Example values ( 7, ‘Frances’ , ’01/10/2009′)
Insert into #Example values ( 8, ‘Margaret’ , ’01/12/2009′)
Insert into #Example values ( 9, ‘Carla’ , ’01/01/2010′)
Insert into #Example values ( 10, ‘Jay’ , ’01/02/2010′)
Insert into #Example values ( 11, ‘Ronald’ , ’01/03/2010′)
select * from #Example
select *
from #Example
where Convert (Datetime, DateJoined) between ’01/03/2009′ and ’01/01/2010′
Drop table #Example
~ IM.
@W.Ah
Check if this could help.
Create Table #Example ( Eid Int, Ename varchar(40), JoinDate datetime )
insert into #Example Values ( 1, ‘Imran’ , ‘9/29/2009 1:00:10 AM’)
Select * from #Example
Select Eid, Ename from #Example
Where convert (datetime, convert ( varchar, JoinDate, 101 )) = ‘9/29/2009’
Drop table #Example
~ IM.
Nice Articles,thanx for sharing ur knowledge.
Hi Mr Imran Mohammed
thank for this code but not actuall get the result please i need all records between two dates your code not gell all records.
In my table
date column in my table is varchar and i’m put date in my application like dd/mm/yyyy and i’m writing this code
select * from Test_Date where t_Date>=convert(datetime,’01/03/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
not get the record has date '02/03/2009′ try this code and see the result please help
thanks again
It should be
select * from Test_Date
where
t_Date>=’20090103′ and
t_Date<'20100203'
Hello Rabih,
r u getting follwoing error :
“The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
while using ”
select * from Test_Date where t_Date>=convert(datetime,’01/03/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
statement ??
Adi
select * from #TempTable Where convert(numeric,Convert(varchar,cast((left(right(t_Date,7),2) +’/’ + left(t_Date,2) + ‘/’ + right(t_Date,4)) as datetime),112)) >=convert(numeric,Convert(varchar,convert(datetime,’01/03/2009′,103),112)) and
convert(numeric,Convert(varchar,cast((left(right(t_Date,7),2) +’/’ + left(t_Date,2) + ‘/’ + right(t_Date,4)) as datetime),112)) = and <= condition
Hope it will give u all the results u r expecting.
Do reply.
thanks.
Regards
Adi
Rabih
it seems my select statement is not comming compleately
I did following steps.
Flow is like this :
1) arrange ur string dd/MM/yyyy to MM/dd/yyyy
2) then convert it to Datatime
3) then convert that datetime to numeric format using 112
4) Now convert ur From date and ToDate of conditon to datetime 103
5) then convert those From date and toDate to numeric format
6) and then compare using >= and <= condition
The formats you specified are ambigous
Make sure to read this post
Hir Mrs Adi
I’m very very very thank for you
you give me the good solution good work for you.
Bye
if you want send to me your Email Address.
Bye again
Your posts always helps me lot …thanx
Can Datepart be used as part of update…set query. I am trying to update a datefield by the following statement
Update tablexx
Set Datepart(yyyy, Datefld) = 2011.
But I am getting errors.
Thanks,
Ash
If you want to update all years to 2011, use
Thanks alot sir, It works it solved lots of my problems
@Ash
The date still need to be a date.
Do you want to change the fields to be January 1 2011?
SET DATEADD(yyyy, 111, 0).
0 is January 1 1900.
Excellent!!!!!!!!!!!!!!!!
Tanq very much….!!!!!
Exact result for what i searching
Hi All,
Kindly look at this statement:
strSQL = “SELECT * FROM LogTbl WHERE logDateTime=(SELECT MAX(logDateTime) FROM LogTbl);”
The problem here is that the actual value which is stored in the table looks like this: 12/2/2009 4:06:55 AM
Now the fetched record will be the latest one. But I want to fetch all the records of that date that is: mm/dd/yyyy.
So, how can we do that in the above mentioned Query Statement? Can anybody help me with it, please? Otherwise, I will have to store the time in a seperate filed in the table which is not that much efficient and worthwhile.
Using your example I tried this but it doesn’t work:
strSQL = “SELECT * FROM LogTbl WHERE logDateTime=(SELECT logDateTime FROM LogTbl CONVERT(VARCHAR(10),logDateTime,111));”
This is the error:
Technical Information (for support personnel)
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error. in query expression ‘logDateTime=(SELECT logDateTime FROM shiftLogTbl CONVERT(VARCHAR(10),logDateTime,111))’.
hi Dev
Use this, it will solve your prblm. I think..
SELECT * FROM LogTbl WHERE CONVERT(VARCHAR(10), logDateTime, 110)=(SELECT MAX(CONVERT(VARCHAR(10), logDateTime, 110)) FROM LogTbl)
Regards
PRAVEEN MUDGIL
Try this
SELECT * FROM LogTbl
WHERE logDateTime>=(SELECT MAX(dateadd(day,datediff(day,logDateTime,0),0)) FROM LogTbl)