Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

equivalent of DateSerial() in T-SQL? (SQL2000) Expand / Collapse
Author
Message
Posted Thursday, December 8, 2005 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 13, 2006 9:40 PM
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?
Post #242910
Posted Thursday, December 8, 2005 9:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 31, 2007 7:01 AM
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

 

 

Post #242913
Posted Thursday, December 8, 2005 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 13, 2006 9:40 PM
Points: 6, Visits: 1
Wow! Thank you.
Post #242920
Posted Thursday, December 8, 2005 3:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 8, 2009 7:56 PM
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

Post #243029
Posted Friday, December 9, 2005 2:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
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'))

Post #243083
Posted Friday, December 9, 2005 7:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #243136
Posted Friday, December 9, 2005 7:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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.


Post #243139
Posted Wednesday, October 17, 2007 3:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 29, 2009 12:11 PM
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










Post #411598
Posted Monday, March 29, 2010 9:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 6:39 AM
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)))

Post #891970
Posted Monday, March 29, 2010 6:10 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
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".

Post #892353
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse