We help IT Professionals succeed at work.

DateSerial in SQL Server 2000

pacificdb
pacificdb asked
on
Hi,

Is there a way to use the DateSerial function in SQL Server 2000? I have installed the enterprise edition plus Analysis Services, but (although the Books Online say I can use it), I can't seem to get DateSerial to work. SQL Server just says that it is an unrecognized function.

- Graham
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Can you post how you try to use it?

Author

Commented:
angel111,

SELECT DateSerial(2001,1,1)

However, I received an email from a friend saying that DateSerial only works within Analysis Server. That would be consistent with what I found.

- Graham
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
DateSerial is not available in SQL Server (tsql), but by VBA, which is accessible directly by the Analysis Server, but still not in TSQL.
So that confirms you findings.

Now, as a workaround, replace
select dateserial ( 2001, 5, 24 )
by
select dateadd( day, 24 -1, dateadd ( month , 5  -1, dateadd(year, 2001  -2000, '01/01/2000')))

Sorry there is not "better" solution...

CHeers

Author

Commented:
angel111,

Thanks for your response. I wanted to use the DateSerial function so I could work out the start of the next financial quarter. I ended up using the following, but since I'm doped up to the eyeballs with medication at the moment, I'm unsure about whether my code will do the job. What do you think?

SELECT @dte='01/' + convert(char(2),((month(getdate())-1)/3)*3+1)  + '/' + convert(char(4),year(getdate()))
          SELECT @NextQuarter = convert(smalldatetime, @dte)

-- BTW, @dte is a char(10).

- Graham
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Not sure, because of the case if the next quarter is in the next year...

There fore, try this:

SELECT @dte='01/' + convert(char(2),((month(getdate())-1)/3)*3)  + '/' + convert(char(4),year(getdate()))
SELECT @NextQuarter = dateadd(month,3,convert(smalldatetime, @dte))

Cheers
pacificdb:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
Máté FarkasDatabase Developer and Administrator
CERTIFIED EXPERT

Commented:
Hello!
Here is a real DateSerial function to SQL:
--------------------------------------------------------------------------
CREATE FUNCTION DateSerial(@iYear int, @iMonth int, @iDay int)
RETURNS datetime
AS
BEGIN
DECLARE @dResult datetime

SET @dResult = CONVERT(datetime, CAST(@iYear as nvarchar(10)) + '-01-01')
SET @dResult = DateAdd(m, @iMonth - 1, @dResult)
SET @dResult = DateAdd(d, @iDay - 1, @dResult)

RETURN @dResult
END
-----------------------------------------------------------------------
call it so:
SELECT dbo.DateSerial(2007, 2, 31)

Enjoy! :)

Author

Commented:
I don't know what else you'd like me to do. I had accepted angelIII's solution back in 2001, and there appears to be no other way to close this question.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.