aspdotnet-suresh offers articles and tutorials,csharp dot net, articles and tutorials,VB.NET Articles,Gridview articles,code examples of 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Query to get only date from datetime in sql server like dd/mm/yyyy, mm/dd/yyyy, yyyymmdd, dd-mm-yyyy etc

Nov 23, 2010

Here I will explain how to convert or get only date from datetime in different formats like like dd/mm/yyyy, mm/dd/yyyy, yyyymmdd, dd-mm-yyyy etc in SQL Server.


In many situations I got requirement like changing datetime to date and changing the date format to different ways like dd/mm/yyyy or mm/dd/yyyy or or We can covert our datetime to date with different formats using different styles. I will display different format of dates with samples check it 


SELECT @DateTime = '2010/05/20 11:21:13'

/*For mm/dd/yyyy format*/
Select CONVERT(VARCHAR(10),@DateTime ,101) as Date

/*For format*/
Select CONVERT(VARCHAR(10),@DateTime ,102) as Date

/*For dd/mm/yyyy format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,103) AS Date

/*For format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,104) AS Date

/*For dd-mm-yyyy format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,105) AS Date

/*For hh:mi:ss format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,108) AS Date

/*For mm-dd-yyyy format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,110) AS Date

/*For yyyy/mm/dd format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,111) AS Date

/*For yyyymmdd format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,112) AS Date

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest,, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email


Anonymous said...

Nice Blog

Unknown said...

its very useful....

Keep rocking Mr.Suresh...


Anonymous said...

Very helpful

madhubabuchinnam said...

nice one........

Anonymous said...

hey, suresh very nice for ur solution i need a small help from u hope u will help me ...

in when ever i m inserting gregorian date its showing hijri date in report.

thnks in advance?

Anonymous said...

Very helpful sir
just assume...

I had lots time waste for it
again thanks to share this aretical

Anonymous said...

ur posts are always helpful

Anonymous said...


Anonymous said...

if you cheng date the query will not work

Rohan Kota said...

Thanks Man..Suresh..!

Unknown said...

Hi my name is vishal your article is Very GOOD!. I have been trying to do the same with sql select query with contains selected datas from 3 or 4 tables in sql server2008. Given the query i have done below:
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
SqlCommand cmd = new SqlCommand("Select d.dialyserID,r.errorCode,r.dialysis_date,pn.patient_first_name,pn.patient_last_name,d.manufacturer,d.dialyzer_size,r.start_date,r.end_date,d.packed_volume,r.bundle_vol,r.disinfectant,t.Technician_first_name,t.Technician_last_name from dialyser d,patient_name pn,reprocessor r,Techniciandetail t where pn.patient_id=d.patient_id and r.dialyzer_id=d.dialyserID and t.technician_id=r.technician_id and d.deleted_status=0 and d.closed_status=0 and pn.status=1 and r.errorCode<106 and r.reprocessor_id in (Select max(r.reprocessor_id) from reprocessor r where r.dialyzer_id=d.dialyserID) order by pn.patient_first_name,pn.patient_last_name", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
when i execute the query above i get value of r.dialysis_date which is of date type in sql sever2008 but in format dd-MMM-yy along with time 12:00:00 AM which i dont want 12:00:00AM to be tagged along value of r.dialysis_date value.
I want r.dialysis_date value to be in format dd-MMM-yyyy.Tell me how should i fix my problem.Reply please.!

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.