Last day of the month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Last day of the month

Author

Topic

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-02 : 13:13:40

I am updating a database that stores dates in the ever-useful MMYY format. That is, it doesn't store the day, and it stores the dates as strings.

I need to convert these "dates" into real dates with the day set to the last day of the month.

I came up with this formula:


set dateformat mdy
declare @olddate char(4)
set @olddate = '1203'
select dateadd(dd, -1, dateadd(mm, 1, left(@olddate,2) + '/01/' + right(@olddate,2)))

This code is artificial; in actual use it will be like this:


select dateadd(dd, -1, dateadd(mm, 1, left(hiredate,2) + '/01/' + right(hiredate,2))), empname
from emp;

Is there a more straighforward way to do this? Thanks in advance.

--
TimothyAllen
[url]http://www.timallen.org[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 13:55:22

Here is a function that I wrote to get the last day of the month (stole part of it from someone on the web):



CREATE FUNCTION udf_LastSundayOfTheMonth
  ( @Date datetime )
RETURNS datetime
AS
BEGIN
  DECLARE @weekday int
  DECLARE @Lastday datetime
  DECLARE @number int
  DECLARE @day datetime
  SELECT @weekday = 0
  SELECT @Lastday = (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)))
  SELECT @number = DATEPART(day, @Lastday)
  WHILE @weekday <> 1
  BEGIN
    SELECT @day = (CAST(STR(MONTH(@Date)) + '/' +  STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
    SELECT @weekday = DATEPART(weekday, @day)
    SELECT @number = @number - 1
  END
  RETURN @day
END

Tara

Granick
Starting Member

46 Posts

Posted - 2003-12-02 : 14:18:42

From everything I have seen, I have used the method that Tim suggested myself, and even Tara's Function does the same basic thing. If you use this alot, I would highly recommend encapsulating it in a function as it will save you lots of time, and even some trouble when/if you ever make a mistake in typing it and can't figure out why it is coming up with weird results.

Shannon

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-02 : 16:16:35

There are other ways of reaching the last day of the current month.

Here's an outtake from another thread.

-- Assume you can translate your dates to a real date, start with a date
DECLARE @MyDate DATETIME
SET @MyDate = 'Feb 12 1974'

SELECT DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @MyDate)+1, 0))

Looks like I didn't read your original post closely. -- Sam

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-03 : 11:02:44

Here is a UDF which implements a last day of the month function:


CREATE FUNCTION [dbo].[lastdate] (@olddate char(4))  
RETURNS datetime AS  
BEGIN 
return dateadd(dd, -1, dateadd(mm, 1, left(@olddate,2) + '/01/' + right(@olddate,2)))
END

It would have to be changed slightly to accept a date as input.

--
Timothy Chen Allen
email me if you have a job in New Orleans for me
[url]http://www.timallen.org[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources