convert datetime to just the date

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 to just the date

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-11-22 : 14:55:55

how can i convert '2006-11-22 13:54:11.450'

to just '2006-11-22' ?

the field is a datetime type, but i only want to return the date portion in this case.

im trying to use the convert function, but it aint working

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:58:40

SELECT YourDateTimeColumn,
DATEADD(day, DATEDIFF(day, 1, YourDateTimeColumn), 0) Yesterday,
DATEADD(day, DATEDIFF(day, 0, YourDateTimeColumn), 0) Today,
DATEADD(day, DATEDIFF(day, 0, YourDateTimeColumn), 1) Tomorrow
FROM YourTable

Peter Larsson
Helsingborg, Sweden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-22 : 15:12:49

too often what the poster really wants is a formatted string. That should really be done in the presentation layer, but if you're like most others you won't be happy until you find:
select left(convert(varchar, getdate(), 120),10)

EDIT:
select left(convert(varchar, '2006-11-22 13:54:11.450', 120),10)
output:
----------
2006-11-22

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:14:57

Or just

CONVERT(varchar(10), getdate(), 120)

Peter Larsson
Helsingborg, Sweden

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-11-22 : 15:36:57

thanks to all, i found it here
http://www.sql-server-helper.com/tips/date-formats.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:43:22

That's fine with just one comment. The result is not a datetime anymore...
Otherwise every dateformat there are easily found in Books Online.

Type CONVERT in QA, hightlight the word and press SHIFT-F1.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-22 : 20:14:55

quote:
Originally posted by BitShift

how can i convert '2006-11-22 13:54:11.450'

to just '2006-11-22' ?

the field is a datetime type, but i only want to return the date portion in this case.

im trying to use the convert function, but it aint working

Where do you want to show data?
If you use front end application, you can use format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources