Datevalue function - 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 2005 Forums

Transact-SQL (2005)

Datevalue function

Author

Topic

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-03-30 : 05:40:30

Hi All,

Is there any function like datavalue, for ex:

i had this function in sqlbase, i need the same function in sqlserver, is there any like this.

@DATEVALUE(date string)

This function converts the argument to a date.
@DATEVALUE is like @DATE, except its argument is a date string, or a portion of a date string. It converts the date string in any standard date string form (dd-mon-yyyy hh:mm:ss) to the date portion of the string.
The data type of the result is date.

Example

If a DATE column called APPT contains '18-JAN-1996 10:14:27 AM', then the following expression returns 18-JAN-1996:

@DATEVALUE(APPT)

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-30 : 05:44:39

see http://msdn.microsoft.com/en-us/library/ms187928.aspx

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 05:47:41

There's no DATE datatype in SQL SERVER 2005.

You will want to simply CAST or CONVERT the string to a DATETIME value. If you need only the data portion then the usual method is to strip the time. Here is an example


DECLARE @foo VARCHAR(255)

SET @foo = '2010-01-04T23:59:59.000'

SELECT @foo

SELECT CAST(@foo AS DATETIME) AS [Straight Convert]

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, @foo)) AS [Stripped Date]

In SQL Server there are 2 'universal' date strings. These are the short and Long form ISO standards

These are:

yyyymmdd - Example '20100401' - 2010, April, 01
'yyyy-mm-ddThh:mm:ss.msmsms' - Example '2010-04-01T23:59:59.001' - 2010, April 01, 11:59:59 and 1 millisecond PM

If you need to convert from a particular format of date string that is not in one of those formats then you can checkout the DATEFORMAT command which determines the rules for CAST/CONVERT

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-03-30 : 06:08:44

ok, thanks to all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources