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 want to do date format conversion while retrieving date from database.
is it possible?
thks
Read about Convert function in SQL Server help file
Hey Thanks a lot.Its works.
GOOD
I m lloking for a sql server query which will display the report between two dates from the database where datetime field is defined.
Sachit
where date_col>=@from_date and date_col<=@to_date
I m looking for a sql server query which will display the report between two dates from the database where datetime field is defined.
Sachit
where datetimecol>=’your_date’ and datetimecol<dateadd(day,1,'your_date')
Thanks
It works best for me
Actually from last 2 weeks i m searching this query
and finally i m very happy that i will now process my project
thanxs alot!!!
i’m working in Asp.net with backend of SQL Server.. i hav doubt for Compare Two Different Column in Table..Like ( Date_From and Date_To) i need to search for Date_From and Date_To when i entered Date_From TextBox and Date_To Textbox .. it ll compare Two Columns and retrive the Date to fill in Grid.. still i’m not able to Get the Concept .. how to Find this? if anybody pls rep as soon as possible..
thanks in advance
Apply this logic
where date_col>=@from_date and date_col<=@to_date
Sir,
if db have more than one field having DateTime datatype than how can one specify the particular field in a sql query ?
Where particular_date_col=’some date value’
Thanks! Helped a lot!
Helped a lot!!
my query was as follows (viral this might help you)
SELECT * FROM MRO_MRSA where
AdmissionDate BETWEEN DATEADD(D, 0, DATEDIFF(D, 0, @dateFrom)) AND DATEADD(D, 0, DATEDIFF(D, 0, @dateTo))
Same problem here dude can u provide me solution , if u get actual soln.
Thanks & Warm Regard
Amit Kumar
[removed phone number and email address]
If input dates have time too, you may need to change the logic to
SELECT * FROM MRO_MRSA
where
AdmissionDate >=DATEADD(D, DATEDIFF(D, 0, @dateFrom),0) AND
AdmissionDate <DATEADD(D, DATEDIFF(D, 0, @dateTo)+1,1)
Hii
Ur explanation helped me solve my problm
Thanks a lot
i want 2 disply date part from datatime datatype..
so wrote a query
“select convert(datetime, month_payroll, 102) as month_payroll from monthly_rates_swap”
& i get o/p as = “2008-04-29 13:44:17.937”
but i want o/p = ’29/04/2008′
can any1 pla tell me tht how to convert datetime into simple date…coz in my company, datetime datatype is used for date…
n i want 2 dipsly only Date in SQL..not date & time…
i searched a lot on net..but i dnt get any ans…
plz help me.
thx in advance..
use convert function with style 103
Hi Dave
Just very curious how to seperate Time from Datetime?what is the best way to store and retrieve time alone in a database like 03.00 am or 3.00 p.m ?
Best regards
Sanu
I suggest you to use datetime column
When you input time values like ‘3:00 AM’, it will get stored as
1900-01-01 03:00:00.000′
You can get time part easily and do all kinds of date related calculations
My problem is not reading the date but inserting it into the database in ASP. eg
mydate=”3/4/2008″
Insertqry=”Insert into mytable ( [birthday]) ) values ( ‘ & CDATE(mydate) & “‘)
results in 4/3/2008 going into the database.
session.LCID=3081 often solves this kind of problem, but not here.
(I can use parameters I believe, but I’m stubborn. There must be a way of doing it how I want !)
It is because you have used ambigous date format. You should always use unambigous date formats like YYYYMMDD format. Make sure you read this artcle that explains why it happens
If i convert date using above method, it will convert into varchar data type, but further we can’t operate date function on it… what to do?
You need to again convert back to datetime. That is why you need to use method 1 only
Hi Vrushali,
I hope you have already resolved your problem.
Anyway this is very simple in fact.e.g
“SELECT CONVERT(CHAR(11),GETDATE(),0)”
will give you a date like ‘May 27 2008’
You can experiment with different arguments for interesting results which suits your requirement.
Cheers
SELECT CONVERT(VARCHAR(10),GETDATE(),109)
Aslamo Alikom
I’ve one qeustion that i wnat to use “like” word instead “=” in the query that retrieve the Date…. thanks
Why do you want to use like on dates?
Can you post some more informations?
Cheers
i need date in 05 jun 2008 format can u help me to resolve this
This is the formation. Do it in your front end application