Extracting a date or time from DATETIME?

[Andreas Schildbach]

Hello everybody,

Is there an easy and robust way to extract the time from a DATETIME
value or the date from a DATETIME value?

The method of converting it to a CHAR/VARCHAR and using SUBSTR is a
bit flawed and will break if the server's dateformat changes.

Is there a function for it?

Thanks for any information!

- Andreas

[BPMargolin]

The builtin function CONVERT supports a variety of date and time formats.
Take a look at the Books Online for details.

[nite_...@hotmail.com]

Did you see the third parameter on convert function? Set it to 108 to get the
time. If you only want the hours and miniutes:
convert( char(5) , getdate() , 108 )

Norman

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Martin Hunt]

There are 2 functions which can handle this (and the convert with correct
3rd parameter), DATENAME and DATEPART

DATENAME
(datepart, date) Returns a character string representing the specified date
part (datepart) of the specified date (date).

DATEPART
(datepart, date) Returns an integer representing the specified date part
(datepart) of the specified date (date).

Where datepart represents the part of the datetime value you want to
retrieve and date is the datetime value

e.g.

datepart(month,getdate()) will give you the current month number i.e. 10
datename(month,getdate()) will give you the current month name i.e.
October

Look in Transact-SQL help under date functions to find out more.

Andreas Schildbach wrote in message
<3617a14c....@news.mediaways.net>...