December 8, 2005 at 9:44 am
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?
December 8, 2005 at 9:52 am
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
December 8, 2005 at 10:04 am
Wow! Thank you.
December 8, 2005 at 3:14 pm
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
December 9, 2005 at 2:43 am
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'))
December 9, 2005 at 7:15 am
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/[/url]
December 9, 2005 at 7:17 am
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.
October 17, 2007 at 3:04 am
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
March 29, 2010 at 9:45 am
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)))
March 29, 2010 at 6:10 pm
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".
_____________
Code for TallyGenerator
March 29, 2010 at 10:38 pm
Ian Yates (12/9/2005)
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.
This is true... for now. With 2008 and datetime2, you can go past this level of accuracy, which will introduce a possible gap (although admittedly pretty tiny) if you change to a new data type. The best solution is usually just to use a < first day of the next month instead of <= the last milliseconds of the last day of the current month.
That said, if you have to do it the other way, -3ms is the best you can do.
March 31, 2010 at 1:23 am
Garadin (3/29/2010)
Ian Yates (12/9/2005)
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.This is true... for now. With 2008 and datetime2, you can go past this level of accuracy, which will introduce a possible gap (although admittedly pretty tiny) if you change to a new data type. The best solution is usually just to use a < first day of the next month instead of <= the last milliseconds of the last day of the current month.
That said, if you have to do it the other way, -3ms is the best you can do.
Things do change. I was never comfortable with the whole 3ms thing - others had suggested it in the posts but the original poster wonder "why subtract 3ms".
Frankly in hindsight the best answer would be to find out more about what the person was trying to do and explain that rather than
WHERE [theDate] between [MidnightOfADate] and [LastPossibleTimestampOfADate]
he/she is better off doing
WHERE [theDate] >= [MidnightOfADate] and [theDate] < [MidnightOfADate+1]
which is pretty what you're suggesting. It's definitely the better way to go. Much easier to maintain and understand 🙂
Jesper's solution gives a correct answer if all you want is the date - it wouldn't be useful for ranged queries necessarily but would be useful for report display, etc.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply