Return Only the Date and Not the Time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return Only the Date and Not the Time

Author

Topic

cnbhold
Starting Member

43 Posts

Posted - 2011-05-31 : 18:31:13

I have a table that stores DATETIME data. How do I create a query that will only give me the Date and not the Time?

Table Name: Migration

Sample Columns

FirstName varchar(256)
LastName varchar (256)
RequestDate (datetime)

Sample Data

Shirley....Douglas...5/31/2011 8:13:33 AM

Desired Results

SELECT FirstName, LastName, RequestDate
FROM Migration

Shirley....Douglas...5/31/2011

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-31 : 19:21:26

If you are using SQL 2008, cast it to data type DATE


SELECT FirstName, LastName, cast(RequestDate as DATE) as RequestDate
FROM Migration

If not,


SELECT FirstName, LastName, dateadd(dd,datediff(dd,0,RequestDate),0) as as RequestDate
FROM Migration

cnbhold
Starting Member

43 Posts

Posted - 2011-05-31 : 20:17:00

Thanks for your suggestions. I got an error when trying to use the DATE function since I'm using SQL Server 2005. I was able to get the following query to return just the date.

Select convert(varchar, RequestDate , 1)
from Migration

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-31 : 21:14:28

While converting it to varchar would work, depending on the consumer of the data, having the date column returned as a character string may exhibit some unexpected behaviors. For example, if you are binding it to a .Net DataGridView, .Net will interpret it as a character column rather than a date column.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-01 : 01:56:27

quote:
Originally posted by cnbhold

Select convert(varchar, RequestDate , 1)
from Migration

Don't convert or declare varchars without a length. Do you know how long a varchar you got there? Do you know how long DECLARE @s varchar is?

--
Gail Shaw
SQL Server MVP

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-06-01 : 04:19:15

quote:
Originally posted by cnbhold

I have a table that stores DATETIME data. How do I create a query that will only give me the Date and not the Time?

Table Name: Migration

Sample Columns

FirstName varchar(256)
LastName varchar (256)
RequestDate (datetime)

Sample Data

Shirley....Douglas...5/31/2011 8:13:33 AM

Desired Results

SELECT FirstName, LastName, RequestDate
FROM Migration

Shirley....Douglas...5/31/2011

Hi

check this once...

create table #Migration
(
FirstName varchar(256)
,LastName varchar (256)
,RequestDate datetime
)

insert into #Migration
select 'Shirley','Douglas','5/31/2011 8:13:33 AM'

select FirstName,LastName,convert(varchar(10),RequestDate,101)as RequestDate from #Migration

--Result
FirstName LastName RequestDate
Shirley Douglas 05/31/2011

--Ranjit

NeilG
Aged Yak Warrior

530 Posts

Posted - 2011-06-01 : 05:19:56

CONVERT(VARCHAR(10),datevalue,101)

you can have different types of dates build up just check out BOL for the required code instead of 101

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources