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
Select Convert(char(11),enddate,13)as date,* from exexamdate
I have date in varchar eg ‘20080602-13:47:08.603’
wnat to extract milliseconds only like 603,could you please tell me the way.
1 Always use proper DATETIME datatype to store dates
2 declare @d varchar(30)
set @d=’20080602-13:47:08.603′
select datepart(millisecond,cast(replace(@d,’-‘,’ ‘) as datetime))
Hmm I Have A Problem:
I Have a Value Like this: Date: ’04/05/2005′
The Problem:
1.
Select * FROM tblTest
where Date LIKE ‘%2005%’
Result: I Have 4 records
2. Select * FROM tblTest
where Date LIKE ‘%05_2005%’
Result: I Have 2 Records (’04/05/2005′, ’12/05/2005′)
3. Select * FROM tblTest
where Date LIKE ‘%04_05_2005%’
Or Date LIKE ’04/05/2005′
Or Date LIKE ‘&04/05/2005&’
Result : 0 recods :S:S
WHere Is The Problem , Can u Help Me ?
The problem is you are converting date values to varchar and compare
What do you want to do?
Hi Pinal,
Your articles are very useful to all.
I am working in database backup utility.
I neet to select only from & to date data.
But tables has no column of date datatype.
Is SQL Server give the data to which date it is inserted into database.
Thanks In Advance
If you dont have a datetime column, it is not possible to know when data are actually added
You rock. Finally a simple way to get all the records for Today. :-D
Your site is now in my favorites…….
hi,
thank u..its help me lot
Hi all,
How do I make this work? Thanks in advance
select count(*) from
where convert(modfd_date, varchar(10),110) =
select convert(mon_dt, varchar(10), 110) from (select case
When datepart(weekday, getdate()) = 2 Then
getdate()
else
dateadd( day, -datepart(weekday, getdate() -2), getdate() )
End as mon_dt) t
If I use convert function I get the error “‘varchar’ is not a recognized built-in function name.”
What is the best way to select data on a date column without using the time part. (other than the option stated above ie., DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) )
I have an index on the date column and would like to use it. Not sure if SQL Server will use an index if a function is applied to a indexed column.
Thank you for any help.
convert(modfd_date, varchar(10),110)
should be
convert(varchar(10),modfd_date, 110)
Also what did you want to do?
If you give us more infrmations, there is a change that there is a simpler solution
Thanks you for work..
Excellent, elegant, easy. Many thanks.
Hi
If we give Month and Year, Can we get the Start Date and End Date of the Month for that particular year. is it possible?
Regards
Sachin
Yes. Refer this
Hi,
I am using MSSQL server and I want to retrieve all records from [case] table where date_received is like ’12-02-2008′ and for this I am using below listed query (1) but its not returning me any record since there are matching records present in table.
1. select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%12-02-2008%’
2. select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%2008%’
Query 1 does not return any record but 2 retuns me results.
Can anyone please help me out?
Regards,
Lavee
You should use
select convert(varchar, date_received, 110) as date from [case]
where
date_received >=’20080212′ and
date_received <'20080212'
select convert(varchar, date_received, 110) as date from [case]
where
date_received >=’20080212′ and
date_received <'20080213'
Respected Sir,
I am developing a Payroll software in VB6.0 and using SQL as backend.
I have a problem that i cant subtract the time from the datetime datatype
Please help me in subtracting datetime.
Regards,
Shashi Kant.
Select Convert(Varchar(15),GetDate(),108)
select date_col-’10:00:00.000′ from your_table
Hello Sir,
I need a query to retrive record from the table based on day alone,month alone,year alone.for eg if i run that query in the august i will get the record which and all created in that month.
where month(date_col)=8
hi i ha too mare solutions watch it in different formats
SELECT CONVERT(VARCHAR(10),GETDATE(),100)
SELECT CONVERT(VARCHAR(10),GETDATE(),101)
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
HI,
this is mahalakshmi. I am doing leave application using C#.net. In that iam checking existing dates are there or not. I have used below query
SELECT COUNT(LeaveApplicationID) AS Expr1
FROM LeaveApplication
WHERE (CONVERT(CHAR(10), FromDate, 101) BETWEEN ’09/09/2008′ AND ’09/13/2008′) AND (CONVERT(CHAR(10), ToDate, 101) BETWEEN ’09/09/2008′ AND
’09/13/2008′)
Fromdate is 09/12/2008 and todate is 09/18/2008.
but it’s giving count as 0.
Please solve my problem.
Try this code
SELECT COUNT(LeaveApplicationID) AS Expr1
FROM LeaveApplication
where
FromDate>=’20080909′ and
FromDate=’20080909′ and
toDate<'20080914'
hi
i working on one project ie payrol system in that i hv to calculate employee present days automaticaly including late mark and all
plz help me hw i can calculat time diff
i insert time in database using date time picker
You can use datediff function to find the difference between the two dates
Hi,
How can i use this in a multiple row query
how can i get date in the format “dd/MM/yyyy” with time included init.i have done a lot of googling.but cant able to manage.
tried with
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)——> 28 Apr 2006 12:39:32:429AM
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)—–> 28/04/2006 12:39:32:429AM
but they are returning a garbage values
Use
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
In view we use this contiction,we get record only datawise not time
i had use the following function to get only the date part from datetime data type.
set @data=CONVERT(varchar(8), @feedbackdate, 112)
it did work fine and returned the date part as 20081005
but while again using the procedure to insert the values
i.e.
Insert into tblfeedback (feedbackid,customerid,feedbackdate,feedbacktime,description) values(@maxUserId,@customerid,@data,@feedbacktime,@description)
again it gets changed into the same and displays the time too
please give me a solution for the same thankx
Datetime column is the combination of both date and time
You cant have date only until you use date datatype of SQL Server 2008
i required only time in select query from datetime field in format like:
2:20:55 AM.
if any body know then tell me.
Read about CONVERT function in SQL Sefver help file
You will find the answer