SQL Clone
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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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 (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 1
Wow! Thank you.
ron k
ron k
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 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 Eights!
SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)

Group: General Forum Members
Points: 964 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10971 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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 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
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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 Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14040 Visits: 12141
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