UDF syntax - 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

SQL Server Development (2000)

UDF syntax

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-14 : 07:43:36

Frans writes "I want to create a function which returns only the date (not the time).
This is what I have now:

ALTER FUNCTION GetOnlyDate
(@dtDate as datetime)
RETURNS datetime

BEGIN

RETURN ( CAST(DATEPART(dd, @dtDate) AS char(2)) + '-' + CAST(DATEPART(mm, @dtDate) AS char(2)) + '-' + CAST(DATEPART(yyyy, @dtDate) AS char(4)))

END

This is how I test, but I am sure there is something wrong with syntax:
select dbo.GetAlleenDatum(#22-7-2005 9:38:05#)

Can somebody help me out please?
Thanks,
Frans"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-14 : 07:44:22

In SQL Server, dates are delimited with single quotes:

select dbo.GetAlleenDatum('22-7-2005 9:38:05')

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 07:46:52

Hi Frans, Welcome to SQL Team!

select dbo.GetAlleenDatum('20050705 09:38:05')

but if you just want to convert a date to the "day" bit and chop off the time then this will be quicker (albeit rather cryptic!):

SELECT DateAdd(Day, DateDiff(Day, 0, '20050705 09:38:05'), 0)

Kristen

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 07:47:59

"select dbo.GetAlleenDatum('22-7-2005 9:38:05')"

Hyphens won't work on all locales, unless you use "T" format - e.g.

~~select dbo.GetAlleenDatum('22-07-2005T09:38:05')~~

Edit: See below

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 08:10:37

>>select dbo.GetAlleenDatum('22-07-2005T09:38:05')

Also it wont work for the Server whose Date Format is set to mdy

Why do you need function for this?

Use it as a Simple query as Kristen suggested or do this in your Front End Application if any

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 08:27:39

Of course it isn't going to work at all, now I look at it, 'coz it needs to be 'yyyy-mm-ddThh:mm:ss', once you do that the DATEFORMAT is irrelevant to the "T" format date string Maddy:

SET DATEFORMAT DMY
GO
SELECT CONVERT(datetime, '2005-07-22T09:38:05')
GO

SET DATEFORMAT MDY
GO
SELECT CONVERT(datetime, '2005-07-22T09:38:05')
GO

SET DATEFORMAT YMD
GO
SELECT CONVERT(datetime, '2005-07-22T09:38:05')
GO

all come back with the same value "2005-07-22 09:38:05.000"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources