SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

June 10, 2007 by pinaldave

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://blog.SQLAuthority.com) , BOL

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 126 Comments

126 Responses

on June 18, 2007 at 1:06 am | Reply Tanvir

Thnx.
It helped me lots.
on July 2, 2007 at 5:53 am | Reply SGI

tks!

cheers mate!
on July 5, 2007 at 1:57 am | Reply Sherry

Thanx dude!!! Good work:)
on July 11, 2007 at 11:59 am | Reply Rajesh Kumar

What a wonderful explanation…..

You must be a top noch programmer or a system solution developer……

Lots of love and thanks
on July 20, 2007 at 8:06 pm | Reply ganesh

How should I change the query to update the “LastModifiedDate” column in my table to a specific value?
on September 10, 2007 at 5:31 am | Reply Karthik.V

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)
- on November 18, 2009 at 4:12 pm karthik
  
  SELECT CONVERT(VARCHAR(10),GETDATE(),104)
  SELECT CONVERT(VARCHAR(10),GETDATE(),105)
  SELECT CONVERT(VARCHAR(10),GETDATE(),106)
  SELECT CONVERT(VARCHAR(10),GETDATE(),107)
on September 11, 2007 at 9:18 pm | Reply srinivas

thanx ,
it helped me alot
on September 18, 2007 at 3:57 am | Reply subbu

thankyou for this function
on September 18, 2007 at 4:32 am | Reply gbn

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
on September 28, 2007 at 7:45 am | Reply SQL SERVER - Introduction and Example for DATEFORMAT Command Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on October 19, 2007 at 10:50 pm | Reply Khushbu

Nice Solution
It is Vey helpful for My duplicate Transcation entry where date is set as Index.

Thanks
on November 3, 2007 at 4:35 pm | Reply Tushar

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
on November 6, 2007 at 9:39 am | Reply Rama Krishna

Hai Tushar,

You try this select datepart(yyyy,getdate())

rgds
on November 7, 2007 at 8:30 pm | Reply Sarthak

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…
on November 27, 2007 at 2:27 pm | Reply Bharath Tabeti

Gr8 work dude.. ppl face number of problems working with datetime…

Regards
Bharath T
on November 28, 2007 at 2:08 pm | Reply ashmi

10x…..
on December 4, 2007 at 12:26 pm | Reply Raza Shah

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.”
on December 7, 2007 at 1:50 pm | Reply Sanatsujata

Dear Sir/Madam,

Can we use CONVERT while index a date field?
on December 14, 2007 at 4:13 am | Reply Mantvydas

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.
on January 14, 2008 at 1:52 pm | Reply Syed Nisar

It helped me a lot.

Thanks
on January 30, 2008 at 11:55 am | Reply Amit

nice soln
on February 2, 2008 at 12:12 pm | Reply Aravind

I want to do date format conversion while retrieving date from database.
is it possible?
thks
on February 6, 2008 at 3:16 pm | Reply Swap

Hey Thanks a lot.Its works.
on February 7, 2008 at 3:43 pm | Reply ANU

GOOD
on February 15, 2008 at 1:08 am | Reply Sachit

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
on February 15, 2008 at 1:09 am | Reply 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
on February 26, 2008 at 12:01 pm | Reply Rahul Pawar

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
on April 14, 2008 at 8:00 pm | Reply ravi

thanxs alot!!!
on April 16, 2008 at 7:08 pm | Reply rajasekar

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
on April 17, 2008 at 7:14 pm | Reply Viral Kothari

Sir,
if db have more than one field having DateTime datatype than how can one specify the particular field in a sql query ?
on April 21, 2008 at 1:42 pm | Reply Jan

Thanks! Helped a lot!
on May 1, 2008 at 10:38 am | Reply Swati Shah

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))
on May 6, 2008 at 11:25 am | Reply Lekha

Hii

Ur explanation helped me solve my problm
Thanks a lot
on May 10, 2008 at 4:46 pm | Reply Vrushali

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..
on May 11, 2008 at 1:01 pm | Reply Sanu

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
on May 15, 2008 at 5:12 am | Reply Chris Blake

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 !)
on May 22, 2008 at 10:46 am | Reply Nagesh

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?
on May 27, 2008 at 2:22 pm | Reply Mahammad

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
- on November 18, 2009 at 4:24 pm karthik
  
  SELECT CONVERT(VARCHAR(10),GETDATE(),109)
on May 27, 2008 at 6:06 pm | Reply Mohammad

Aslamo Alikom

I’ve one qeustion that i wnat to use “like” word instead “=” in the query that retrieve the Date…. thanks
on May 28, 2008 at 9:23 am | Reply Mahammad

Cheers
on June 4, 2008 at 12:27 pm | Reply preethi

i need date in 05 jun 2008 format can u help me to resolve this
on June 11, 2008 at 3:07 pm | Reply mahender

Select Convert(char(11),enddate,13)as date,* from exexamdate
on June 13, 2008 at 12:58 pm | Reply Seema

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.
on July 2, 2008 at 3:41 pm | Reply Xhelson

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 ?
on July 5, 2008 at 3:16 pm | Reply Tushar Dusane

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
on July 15, 2008 at 8:43 pm | Reply George

You rock. Finally a simple way to get all the records for Today. :-D

Your site is now in my favorites…….
on July 17, 2008 at 7:23 pm | Reply MuthuKumar

hi,
thank u..its help me lot
on July 22, 2008 at 12:00 am | Reply mahi

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.
on August 1, 2008 at 8:50 pm | Reply albany-john

Excellent, elegant, easy. Many thanks.
on August 2, 2008 at 1:06 pm | Reply Sachin

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
on August 11, 2008 at 11:16 pm | Reply Lavee Srivastava

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
on August 19, 2008 at 4:31 pm | Reply Shashi Kant

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.
- on November 5, 2009 at 5:57 pm Champ
  
  Select Convert(Varchar(15),GetDate(),108)
on August 25, 2008 at 8:52 am | Reply Elumalai

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.
on August 30, 2008 at 4:49 pm | Reply salonee

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)
on September 8, 2008 at 11:09 am | Reply Mahalakshmi

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.
on September 10, 2008 at 9:36 am | Reply likitha

thanks it really worked
on September 10, 2008 at 9:37 am | Reply aksheya

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
on September 10, 2008 at 10:08 am | Reply akanksha

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
- on November 18, 2009 at 3:45 pm karthik
  
  CHANGE THE DATE FORMAT AS ‘YYYY-MM-DD’
on September 15, 2008 at 2:42 pm | Reply pranita

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
on September 29, 2008 at 8:21 am | Reply Sherwin

Hi,

How can i use this in a multiple row query
on September 30, 2008 at 12:44 pm | Reply pavan

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
on October 7, 2008 at 1:04 pm | Reply lokesh

In view we use this contiction,we get record only datawise not time
on October 7, 2008 at 8:35 pm | Reply Bijoy

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
on October 8, 2008 at 2:39 pm | Reply Preeti

i required only time in select query from datetime field in format like:
2:20:55 AM.

if any body know then tell me.
on October 18, 2008 at 7:00 am | Reply SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2 Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on October 26, 2008 at 11:25 pm | Reply drasticnay

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
on October 26, 2008 at 11:29 pm | Reply drasticnay

Answer to the ” Vrushal” @ 34

SELECT CONVERT(VARCHAR(10),GETDATE(),111)
on October 26, 2008 at 11:55 pm | Reply drasticnay

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
on October 27, 2008 at 12:22 am | Reply drasticnay

Select Convert(char(11),getdate(),8)
SELECT right(GetDate(),7)
on October 27, 2008 at 6:52 pm | Reply Athar

Sir,Plz send me the query to retrieve only time from datetime column.
on October 31, 2008 at 11:17 am | Reply M.Shanmugham

hai

ur Website Very UseFul For Me And Us

JAI HIND
on December 2, 2008 at 9:00 pm | Reply Phil Karras

How do I count on just the date portion of a datetime field?

tnx
on December 12, 2008 at 5:59 pm | Reply kumar

select replace(convert(varchar(11), getdate(), 106), ‘ ‘, ‘/’) as date

result
———

date
12/Dec/2008
on January 2, 2009 at 9:18 pm | Reply amenu

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)
on January 9, 2009 at 5:07 pm | Reply Ashish Jain

Answer for @Athar, @Preeti

Query to retrieve only time from datetime column.

select convert(varchar(10), getdate(),108) –without millisecond

OR

select convert(varchar(10), getdate(),114) –with millisecond
on January 13, 2009 at 2:57 pm | Reply Harsha

Hi,
I want to retrieve only the date part from datetime from the SQL database. I am using
SELECT CONVERT(VARCHAR(10),GETDATE(),111) as sowDate
But it is giving me the current date :(
Plz help me.
on January 23, 2009 at 10:18 pm | Reply Codeb

I have a problem with dates that are entered in MS Access but are out-of-range like 01/02/830. Can u please help me with a query that can retrieve such dates for correction before I can import the data to SQL Server 2008.
on January 28, 2009 at 1:13 am | Reply Ankit

Hi,

Thanks for your blog..

I have a problem in comparing 2 date fields one has the time field as (2009-01-16 12:32:50.690) and the other (2008-08-07 00:00:00.000).

I want to compare the date part of the 2 fields. I tried coverting them to varchar (101) and using the <= operator but the result set was incorrect.

I am not sure how else I can achieve this functionality of comparing the date part only.
on January 28, 2009 at 4:34 pm | Reply aswathy

thanx a lot….
on January 29, 2009 at 4:46 am | Reply Imran Mohammed

@Ankit,

You need to use styles with convert.
Format of convert is

convert ( New Data Type, Column_Name or variable, Style)

select A.* , B.* from table_name A , table_name B
where convert ( varchar(10), A.Date_column_name , 101) = convert ( varchar(10), B.Date_column_name , 101)

Hope this helps,
IM
on February 3, 2009 at 5:41 pm | Reply Suneel Tak

Thank you sir
on February 9, 2009 at 6:23 pm | Reply Rutu

This topic helped me a lot but can u tell me what is the relation of sequence of the number with date format
on February 24, 2009 at 5:39 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice [...]
on February 28, 2009 at 12:06 pm | Reply Intellect Yog

Hello Sir,

I have one date problem regarding importing XML Date to sql Server 2005.

I am passing DataTable to SP from .NET Application C#..
the columns with DateTime data type having
Default value as :
col.DefaultValue = DateTime.Today.Date;
and
string strXML = ds.GetXml();
and passing this strXML to SP.

My XML have tag like :

2009-02-28T00:00:00+05:30

SP is not accepting the date I pass above to datetime field of SQL server 2005.

can you plz. suggest wt could i do for this..

I tried for

select cast(2009-02-28T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,
‘DATETIME’)

but it is giving NULL Value.

Thanks.

Reagerd
on February 28, 2009 at 3:25 pm | Reply Intellect Yog

Hello Sir..

My above reading DataTime from XML get solve using above mentioned casting.

Due to my some mistake in Query I was getting NULL value.

Well, but now I am fasing new problem for this DateTime only of XML…

I have two tags in my XML datatype string which are

2009-01-01T00:00:00+05:30
2009-12-31T00:00:00+05:30

Actualy these dates are 01/01/2009 and 31/12/2009 respectively

But In OpenXML when I convert these dates it gives me different OUTPUT

(Here with I am showing you in select )

select
cast(‘2009-01-01T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,'DATETIME’) as StartDate,

cast(‘2009-12-31T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,'DATETIME’) as EndDate

OUTPUT is :

2008-12-31 18:30:00.000 2009-12-30 18:30:00.000

respectively ..

Can you please suggest Why this is..?

Regards
on March 3, 2009 at 11:49 pm | Reply Rafael

Namasthe Pinal

Could you tell me why my DB return is different?

select getdate()

3/3/2009 3:11:56 PM

I wish it was

3/3/2009 15:11:36
on March 11, 2009 at 9:33 am | Reply Vijay

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.
on March 26, 2009 at 4:38 pm | Reply Rahman

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
on March 26, 2009 at 6:31 pm | Reply Brian Tkatch

@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.
on April 1, 2009 at 4:43 pm | Reply siva

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
on April 23, 2009 at 5:34 pm | Reply Amit

hi siva

use select right(rtrim(column_name),2) from table_name
on May 5, 2009 at 1:45 pm | Reply Searock

thanks a lot :)
on May 6, 2009 at 5:41 pm | Reply alpesh

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.
on May 6, 2009 at 5:41 pm | Reply alpesh

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
on May 7, 2009 at 9:30 am | Reply Imran Mohammed

@Alpesh,

You can use convert styles, to get different formats for date.

Please see a complete list of format styles here
http://msdn.microsoft.com/en-us/library/ms187928.aspx

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.
on May 9, 2009 at 10:10 am | Reply alpesh

@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???
on June 3, 2009 at 4:17 am | Reply Sathish

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.
on June 3, 2009 at 4:22 am | Reply 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
on June 3, 2009 at 4:37 am | Reply 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.
on June 3, 2009 at 2:14 pm | Reply Jahanzaib Khanzada

I found the soultion. Thanks
on June 26, 2009 at 12:59 pm | Reply Sandip

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.
on July 8, 2009 at 10:23 am | Reply priyaa

Answer to Seema :
To extract milliseconds from current date

SELECT(DATEpART(ms,getdate()))
on July 24, 2009 at 6:39 pm | Reply Subramanian

We can also select date using

SELECT LEFT(GETDATE(),11)
on July 28, 2009 at 10:57 pm | Reply Cliff

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…
on July 30, 2009 at 11:44 pm | Reply Mark

Your team member’s solution was better.
on July 31, 2009 at 10:29 am | Reply Manish

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.
on August 25, 2009 at 12:02 pm | Reply Nazila

It was a great help! Thanks!
on September 5, 2009 at 3:36 pm | Reply Ahmed

Thank you so much, but is it possible to display just time?
on September 29, 2009 at 4:25 pm | Reply Rabih

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
on September 29, 2009 at 4:37 pm | Reply Rabih

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)
on September 29, 2009 at 7:55 pm | Reply Tejas Shah

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
on September 30, 2009 at 8:48 am | Reply W.Ah

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
on September 30, 2009 at 2:24 pm | Reply Rabih

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
on October 1, 2009 at 4:59 am | Reply Imran Mohammed

@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.
on October 1, 2009 at 5:09 am | Reply Imran Mohammed

@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.
on October 1, 2009 at 11:27 am | Reply Rabih

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
on October 1, 2009 at 1:10 pm | Reply Adi

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
on October 1, 2009 at 2:32 pm | Reply 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
on October 1, 2009 at 2:37 pm | Reply 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
on October 1, 2009 at 4:42 pm | Reply Rabih

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
on October 29, 2009 at 2:44 pm | Reply Devesh

Your posts always helps me lot …thanx
on November 19, 2009 at 1:50 am | Reply Ash

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