Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


equivalent of DateSerial() in T-SQL? (SQL2000)


equivalent of DateSerial() in T-SQL? (SQL2000)

Author
Message
Adam O'Neil
Adam O'Neil
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1
Hi, I need to calculate the last day of the month in a stored proc--any month. In VB, I do this with the DateSerial function, but I see no equivalent in T-SQL. I see ways to split apart a date (DAY(), MONTH(), YEAR()), but no way to create a date from separate day, month, and year values. Am I missing something?
chandra pottipati
chandra pottipati
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 1

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

more date formats are discussed in this article where I found this solution

http://www.databasejournal.com/features/mssql/article.php/3076421


Adam O'Neil
Adam O'Neil
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1
Wow! Thank you.
ron k
ron k
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 5

Or if you prefer:

declare @date datetime,
@days int,
@month int,
@year int

select @month = 4, @days = 1, @year = 2005

select @date = cast(@month as varchar) + '/' + cast(@days as varchar) + '/'
+ cast(@year as varchar)

select @date

----------------------------------------------
2005-04-01 00:00:00.000

(1 row(s) affected)

ron


Jesper-244176
Jesper-244176
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
Points: 422 Visits: 33

Nice solution, but I don't quite understand why you subtract 3 ms (and why you refer to month as m and mm in the same query). The following might be easier to read, although it is essentially the same:

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))


Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

won't catch any row on that day with a time portion > 00:00:00.000. If your data contains a time portion, such a query is likely to return incomplete results.



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 445
3ms is the smallest unit of time a datetime field can capture. So by subtracting 3ms from midnight, you get the latest possible time that could be captured on the earlier day, which will catch all time values for the previous day, not just those at midnight of the prior day.



Roberto Filippi
Roberto Filippi
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 42
these are the functions that I wrote for datederia, timeserial and datetimeserial
I hope useful

CREATE FUNCTION dbo.ufnDateTimeSerial (@Year int=null, @Month int=null, @Day int=null, @Hour int=null, @Minute int=null)
/*to get the date pass year, month and day*/
/*to get the time pass hour and minute*/
RETURNS smalldatetime AS
BEGIN

DECLARE @strDate varchar(8)
DECLARE @strTime varchar(5)

IF @Year IS NULL OR @Month IS NULL OR @Day IS NULL
SELECT @strDate = ''
ELSE
SELECT @strDate = dbo.ufnStrRAlign(CAST(@Year AS int), 4, '0') + dbo.ufnStrRAlign(CAST(@Month AS int), 2, '0') + dbo.ufnStrRAlign(CAST(@Day

AS int), 2, '0')

IF @Hour IS NULL OR @Minute IS NULL
SELECT @strTime = ''
ELSE
SELECT @strTime = dbo.ufnStrRAlign(CAST(@Hour AS int), 2, '0') + ':' + dbo.ufnStrRAlign(CAST(@Minute AS int), 2, '0')

RETURN CAST(dbo.ufnStrCat (' ', @strDate, @strTime, 0) AS smalldatetime)
END


CREATE FUNCTION dbo.ufnDateSerial (@Year int, @Month int, @Day int)
RETURNS smalldatetime AS
BEGIN
RETURN dbo.ufnDateTimeSerial (@Year, @Month, @Day, null, null)
END


CREATE FUNCTION dbo.ufnTimeSerial (@Hour int, @Minute int)
RETURNS smalldatetime AS
BEGIN
RETURN dbo.ufnDateTimeSerial (null, null, null,@Hour, @Minute)
END

CREATE FUNCTION dbo.ufnStrRAlign
(@In varchar(8000), @OutLen integer, @strFiller varchar(1)=' ')
RETURNS varchar(8000) AS
BEGIN
DECLARE @Result varchar(8000)
DECLARE @FillLen integer

SELECT @FillLen =@OutLen-LEN(ISNULL(@In, ''))

IF @FillLen > 0
SELECT @Result = REPLICATE(@strFiller, @FillLen) + ISNULL(@In, '')
ELSE
SELECT @Result = ISNULL(@In, '')


RETURN @Result
END


CREATE FUNCTION dbo.ufnStrCat
(@Separator varchar(8000), @Str1 varchar(8000), @str2 varchar(8000), @Distinct smallint)

RETURNS varchar(8000) AS
BEGIN
DECLARE @Result varchar(8000)

IF ( @Distinct<>0 AND ISNULL(@Str1, '') = ISNULL(@Str2, ''))
SELECT @Result=ISNULL(@Str1, '')
ELSE
BEGIN
IF @Str1 IS NOT NULL SELECT @Result=@Str1

IF (LEN (LTRIM(ISNULL(@Result, '')))>0 AND LEN (LTRIM(ISNULL(@Str2, '')))>0) SELECT @Result=@Result + @Separator

IF @Str2 IS NOT NULL SELECT @Result=ISNULL(@Result, '') + @Str2
END

RETURN @Result
END
darth vodka
darth vodka
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 328
here's my 2p



print convert(datetime,cast(2009*10000+3*100 + 5 as varchar(8)),112)




just wondered exactly the same question...in fact here is my last day of the month bit


set @DealDateTo=dateadd(dd,-1,dateadd(mm,1,convert(datetime,cast(@DealYear*10000+@DealMonth*100 + 1 as varchar(8)),112)))


Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5852 Visits: 11417
Conversions varchar to datetime and back are the worst cpoosible conversions from performance point of view.

They may be very comfortable for a human eye, so keep them where they belong - in UI and reporting interfaces.

The correct solution was posted at the beginning:
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm, 0, getdate() )+1, 0))

Every other one is a "second best".
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search