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 date.
SELECT GETDATE()
ResuleSet:
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 suggest many format are displayed on MSDN.
Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.
Reference Pinal Dave (http://www.SQLAuthority.com), BOL
pinaldave
Thnx.
It helped me lots.
tks!
cheers mate!
Thanx dude!!! Good work:)
What a wonderful explanation…..
You must be a top noch programmer or a system solution developer……
Lots of love and thanks
How should I change the query to update the “LastModifiedDate” column in my table to a specific value?
HI.
I want to display the date in U.S. format.
Instead of Getdate() iam specifying directly the date.
I have tried using these syntaxes.. It is not changing to that format.
Kindly help
Select convert(varchar,’10/09/2007′,1) ,
Select convert(varchar,’10/09/2007′,101),
Select convert(varchar,’10/09/2007′,110)
thanx ,
it helped me alot
thankyou for this function
The convert and string method has more overhead than you think if you need to use it in large rowsets.
Also, you still need another CAST to turn it back into datetime.
An alternate method one is to cast to float, FLOOR, and cast back to datetime.
Finally: the function usually is non deterministic if you use string to datetime (or vice versa) because the date format is culture and locale sensitive. CAST and CONVERT also mess up determistism.
Using the DATEADD(..DATEDIFF..) allows determinism, keeps all calculations within the datetime datatype, and guarantees complete locale insensitivity.
Determinism is important for indexing computed columns, or if the function is used in WHERE clause
[...] code review of Jr. DBA I found interesting syntax DATEFORMAT. This keywords is very less used as CONVERT and CAST can do much more than this command. It is still interesting to learn about learn about this new [...]
Nice Solution
It is Vey helpful for My duplicate Transcation entry where date is set as Index.
Thanks
Hello,
I am extracting year from date in oracle i. e. to_char(sysdate,’yyyy’) Now i want to change my database from oracle to SQL Server 2005. So the point is how to convert
to_char(sysdate,’yyyy’) from oracle to SQL Server 2005.
Plz help me out.
Thanking you,
Tushar
Hai Tushar,
You try this select datepart(yyyy,getdate())
rgds
Thnax Buddy Nice Examples its working a lot….
i personally feel u should add some more exapmles
for sql queries which screw the programming day to day
any ways thanx
Sarthak…
Gr8 work dude.. ppl face number of problems working with datetime…
Regards
Bharath T
10x…..
I want to convert varchar type date into datetime so i am getting the following error.
Solve this Problem….
“Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”
Dear Sir/Madam,
Can we use CONVERT while index a date field?
But what if I need the date to be 2007-12-14, with hyphens! SQL Server only offers me 2007-12-14 00:00:00, I don’t need the time at all.
It helped me a lot.
Thanks
nice soln
I want to do date format conversion while retrieving date from database.
is it possible?
thks
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
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
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
Sir,
if db have more than one field having DateTime datatype than how can one specify the particular field in a sql query ?
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))
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..
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
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 !)
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?
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
Aslamo Alikom
I’ve one qeustion that i wnat to use “like” word instead “=” in the query that retrieve the Date…. thanks
Cheers
i need date in 05 jun 2008 format can u help me to resolve this
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.
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 ?
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
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.
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
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
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.
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.
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.
thanks it really worked
hiiiiiii
can anyone help me out with this problem am facing with ie
am not able to use between command in sql server
am getting errors
hi all
i have 6 columns with data in it but after giving this command
(select * from income where dt between ’sep 7 200′ and ’sep 9 2008′)
command is executed successfully but the output is
am getting only the names of the columns without data in it
please help me out
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
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
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
i required only time in select query from datetime field in format like:
2:20:55 AM.
if any body know then tell me.
[...] year ago I wrote post about SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice where I have discussed two different methods of getting datepart from [...]
DECLARE @ret_string varchar (255)
DECLARE @year as varchar(10)
DECLARE @Month as varchar(10)
DECLARE @Day as varchar(10)
set @year = cast(year(getdate()) as varchar)
set @Month = cast(month(getdate()) as varchar)
set @Day = cast(Day(getdate()) as varchar)
EXEC xp_sprintf @ret_string OUTPUT, ‘%s-%s-%s’, @year, @Month, @Day
PRINT @ret_string
Result will be like this
2008-10-26
Answer to the ” Vrushal” @ 34
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
SELECT CONVERT(VARCHAR(26), GETDATE(), 13)
27 Oct 2008 00:17:58:423
SELECT CONVERT(VARCHAR(26), GETDATE(),22)
10/27/08 12:25:10 AM
Select Convert(char(11),getdate(),8)
SELECT right(GetDate(),7)
Sir,Plz send me the query to retrieve only time from datetime column.
hai
ur Website Very UseFul For Me And Us
JAI HIND
How do I count on just the date portion of a datetime field?
tnx
select replace(convert(varchar(11), getdate(), 106), ‘ ‘, ‘/’) as date
result
———
date
12/Dec/2008
Testing for December 31st to get resultset of data for anything greater or equal to the 31st, but keep getting all of 2008 data.
Using:
select * from TABLE where enddate >= CONVERT(VARCHAR(10),’2008-12-31′,111)