convert datetime in to date - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

convert datetime in to date

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-07-08 : 11:04:07

Hi
I am running a query and want to get Date only from my table, my table have a field 'CreateDate' which has datetime value, Now I want to retrieve date only from that field. But I want that date in date format not in varchar.
Thnx

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-08 : 11:06:17

select dateadd(dd,datediff(dd,0,CreateDate),0) from MyTable

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-08 : 11:08:03

Why does everyone seem to want to do this??

Ok... you have to have time in SQL as it is a Datetime datatype... not date.

Select convert(datetime,convert(nvarchar,CreatedDate,101))

will give your CreatedDate at midnight...

Corey

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-07-08 : 11:15:26

Thnx
But this is not working for me
lets consider this example
Create table ABC(
CreateDate datetime)

insert into abc
Select getdate()

i want 2004-5-06 but data should be retrieve in date format not in varchar.

Plz Help me ASAP

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-08 : 11:20:27

A Datatype of datetime will always have a time portion on it... You cannot get rid of it in SQL Server, you can ignore it by doing either of the above queries...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-08 : 11:21:06

Ok... again.

the datetime datatype is not stored like '2004-05-30 00:00:00.000'. This output is simply the default format for QA.

If you want it to look like 'yyyy-mm-dd' then

Select left(convert(nvarchar,CreateDate,120),10) From abc

But this is still not a datetime... it is nvarchar or varchar

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-08 : 11:21:46

Apparently I type too much... *sigh*

Corey

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-08 : 11:22:58

quote:
Originally posted by Seventhnight

Apparently I type too much... *sigh*

Corey

It does look that way..

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-07-08 : 11:28:02

yes So this is SQL SERVER'S BAD, Not Mine.........I will Fire My Project Manager Today
(kekekekekekekeke)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-08 : 11:32:32

I gotta ask....why can't it be varchar?


USE Northwind
GO

CREATE TABLE myTable99(Col1 datetime)
GO

DECLARE @x varchar(10)
SELECT @x = '1960-10-24'

INSERT INTO myTable99(Col1) SELECT @x

SELECT Col1, @x FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-08 : 11:43:11

Yeah, I'm really not following whats bad about the way SQL Server handles dates... I've never had a problem with it, its always put out easily...

Corey

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 11:51:36

quote:
Originally posted by X002548

I gotta ask....why can't it be varchar?

In my case that would be because the application will detect that the column, in the recordset, is a Date(/time) datatype and react accordingly (presentation style and/or data entry validation)

For that reason I would much prefer to have a DATE datatype; DATE is much more common for us, than dateTIME, so we treat all datetime as DATE, in application, and have to handle things WITH a time specially!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources