CODEWITHSUNDEEP
sqlsql-serversql-server-2008
savan kuppast
savan kuppast

Reputation: 21

DATETIME SQL Query

I am using SQL Server 2008 Management Studio. I have a datetime column in my table.

When I select all contents of the table, the date column has data like 10/10/2013 12:00:00 AM.

I need a query to display all contents from the table with date column data as 10/10/2013.

Upvotes: 0

Views: 148

Answers (6)

selvackp
selvackp

Reputation: 1

For your question i was created one table as

 create table dateverify(date_v varchar(20));

Inserted single column value

   declare @a varchar(20);
      set @a='10/10/2013';
   begin
      insert into dateverify(date_v) values(@a);
   end;

using below query,

   SELECT CONVERT(VARCHAR(20), date_v , 103) AS [DD/MM/YYYY] from dateverify;

got an answer like you are asked..10/10/2013

Upvotes: 0

Devart
Devart

Reputation: 121902

Try this one (2008 and higher) -

SELECT CAST('10/10/2013 12:00:00 AM' AS DATE)

For 2005 -

SELECT CAST('10/10/2013 12:00:00 AM' AS VARCHAR(10))

Output -

10/10/2013

Upvotes: 5

t-clausen.dk
t-clausen.dk

Reputation: 44316

SELECT CONVERT(VARCHAR(10), column, 101) -- u.s standard format mm/dd/yyyy
SELECT CONVERT(VARCHAR(10), column, 103) -- British/French standard format dd/mm/yyyy

Upvotes: 5

hangqi
hangqi

Reputation: 141

You can use FORMAT() function:

SELECT FORMAT(<your_column>, 'MM/dd/YYYY') FROM <your_table>;

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Using CONVERT function along with styles for DATETIME you can choose the way dates are displayed. 101 would give you mm/dd/yyyy, 103 = dd/mm/yyyy

SELECT CONVERT(NVARCHAR(20),your_datetime_col, 103) FROM your_table

SQLFiddle DEMO

Upvotes: 0

backtrack
backtrack

Reputation: 8144

This will help you

cast(floor(cast(@dateVariable as float)) as datetime)

Upvotes: 0

Related Questions