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
Date representation in the DB and Date formatting are two different things.
You take too simplistic approach.
For example, in a native date field, I can perform date arithmetic, but not in a varchar, or string field.
Hence please dont treat them as one and the same.
Oracle has a native date field – 01-01-2009 01:12:35
You can apply a “trunc” function to chop off all time, i.e.
you will get a native ’01-01-2009′.
On both the above values, you can do arithmetic, i.e. add hours, days, or any time interval – you will get a new date as a result.
Both the above dates, trunc’ed one, and the full date,
can be formatted into a varchar or string.
Hence date presentation is different from date.
This is such a common trap programmers fall into.
Dear All,
I been trying to find an optimize way to fetch the data on datetime columns, and preferably i would go for dateadd method i.e. DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())), the reason why i would choose this method over convert is because of my size of table which is almost like 2 millions rows and somehow my datetime column is a part of primary key. Also to note is, if my concern is only to get the date part then its better to use above mentioned method than using convert, as convert will change the datatype of column on the fly to string and its an overhead for the engine to do this step. Surely Pinal might have a better solution but i been using the dateadd for quite a while now and its going smooth.
Happy coding.
Regards- Rahman
@Rahman
If the date part gets queried a lot, it may be worthwhile to have a computed COLUMN in the TABLE that applies the FUNCTION for you. Then, INDEX that COLUMN.
hello sir,
column of a table contains data as
jan09
jul08
aug08
………… like this
how can i select only last two digits of that column
select right(column_name,2) from table_name;
is not retrieving any data
pls help me
What did you mean by “not retrieving any data”?
Post some sample data with expected result
hi siva
use select right(rtrim(column_name),2) from table_name
thanks a lot :)
hello,
i hav used datetimepicker control in my form.it stores date and time both in sql server database.
is there any way to only store time, not date in sql server database?also same for retrieval??
Thank You.
hello,
i hav used datetimepicker control in my form.it stores date and time both in sql server database.
is there any way to only store time, not date in sql server database?also same for retrieval??
Thank You in advance
@Alpesh,
You can use convert styles, to get different formats for date.
Please see a complete list of format styles here
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
I have used only 3 formats (108, 109, 114) just to show example:
— only Time ( 24 hours clock)
select convert ( varchar , getdate() , 108)
Output :
23:56:37
GO
— only Time with Micro seconds ( 24 hours clock)
select convert ( varchar, getdate(), 114)
Output :
23:56:48:810
GO
— only Time with Micro seconds ( 12 hours clock)
select substring ( convert ( varchar, getdate(), 109), 13, len ( convert ( varchar, getdate(), 109)) – 12)
Output:
11:57:19:840PM
~ IM.
@Imran
sorry tosay but I asked how to store only time in sql server database . I know how to extract only time/date from tht control…..Plz…How To STORE???
If you use version 2008, you can make use of time datatype
Otherwise you can use either datetime or varchar column
Hi All,
I have a table with two datetime fields, now I want to display both columns as date only.
tablename: sometable with two fields as
created | lastlogin
—————————————————————
2009-06-02 22:02:05.033 | 2009-06-02 22:03:34.503
2009-06-02 22:02:27.530 | 2009-06-02 22:03:50.047
now I want output as
created | lastlogin
————————————-
2009-06-02 | 2009-06-02
2009-06-02 | 2009-06-02
can anyone explain please, Thanks in advance
Sathish.
I have been through all the replies and got it..
SELECT CONVERT(VARCHAR(11),created,102) as created_date, CONVERT(VARCHAR(11),lastlogin,102) as last_date from sometable
Thanks,
Sathish
@Alpesh
declare the the field whatever you want to insert only time as varchar then
while inserting use this function on that field
convert ( varchar(20) , getdate() , 108)
ex:
here
uname as varchar(20)
lastlogin as datetime
created as datetime
query:
insert into table_name (uname, lastlogin, created) values (convert ( varchar(20) , getdate() , 108), getdate(), getdate())
if yours is different case i’m sorry.
Thanks,
Sathish.
I found the soultion. Thanks
I developed the solution for date only from ‘getdate()’ function. Also this worked for me very effectivelyy. Because I does not required to change the data type in the database or not required to write down different function for each time or not required to execute the same function for so many times.
I use this method.
1) Keep data type of column as ‘datetime’.
2)Keep its default value to ‘getdate()’.
3) Write trigger on that table as
CREATE TRIGGER [TRIGGER NAME] ON [dbo].[TABLE NAME]
FOR INSERT, UPDATE, DELETE
AS
UPDATE [TABLE NAME] SET [COLUMN NAME] = CONVERT(datetime,CONVERT(varchar(20),[COLUMN NAME], 111), 111)
It works very effectively.
It is better you make use of a computed column than a trigger
Answer to Seema :
To extract milliseconds from current date
SELECT(DATEpART(ms,getdate()))
We can also select date using
SELECT LEFT(GETDATE(),11)
This is not the reliable method. Depends on the language settings of the server you will get different result
FWIW…
Many people prefer the DATEADD method rather than the CONVERT method because they are trying to compare two dates rather than output a date…
If you use the CONVER method, once you get the string with just the date in it, you have to convert it back to a datetime to do any comparisons with it other than =, and two converts is more expensive than a couple of system function calls in a non-negligible way…
Your team member’s solution was better.
I have a problem of trying to find dates in my table which does not follow the following formats. Given are the valid formats:
1. 10/1/2007
2. Thu Sep 20 06:32:10 GMT-0400 (EDT) 2007
3. 10/1/2007 5:00
Can anybody please help me regarding this?
Thanks in advance.
Use regular expression
where
date_col like ‘[0-2][0-9]/[01][0-9]/[0-9][0-9][0-9]’
or
date_col like ‘[0-2][0-9]/[01][0-9]/[0-9][0-9][0-9] [0-24]:00’