Max Date (Another easy one) - 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

General SQL Server Forums

New to SQL Server Programming

Max Date (Another easy one)

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-12-01 : 13:31:46

Hi there - I'm working with a table that has seperate YEAR MONTH DAY columns. How can I obtain the most recent date, and have the output formatted as a date format "MM/DD/YYYY" (also known as 101 format).

Thanks!

YEAR MTH DAY
2010 01 01
2009 05 29
2010 03 01
2010 04 28
2011 01 28
2011 06 28

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-01 : 13:51:47

This is two ways of doing it.

declare @t table (yr int, mth int, dy int)
insert @t
select 2010, 01, 01
union all select 2009, 05, 29
union all select 2010, 03, 01
union all select 2010, 04, 28
union all select 2011, 01, 28
union all select 2011, 06, 28

select convert(varchar(10),cast(dt as datetime),101) as [date]
from
(
select cast(yr as char(4)) + right(('00' + ltrim(rtrim(cast(mth as char(2))))),2) + right(('00' + ltrim(rtrim(cast(dy as char(2))))),2) as [dt]
from @t
) t

Other way

select convert(varchar(10),cast(dt as datetime),101) as [date]
from
(
select cast(((yr * 10000) + (mth * 100) + dy ) as char(8)) as [dt]
from @t
) t

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 13:55:08

Oh, it may be easy for some of the guru's on this forum, but it does not look very easy for me - primarily because I don't know what the data types of the columns are.

If all the columns are varchars, and are correctly padded with zeros, and you want the output to be varchar, then you could do this:

SELECT [mnth]+'/'+[day]+'/'+[year] FROM YourTable;

But, if they are numbers, and you still want to get the result as varchar, then you need to cast the numbers to varchar and pad them with zeros like this:

SELECT
   RIGHT('0'+CAST([mnth] AS VARCHAR(2)),2) +'/'+
   RIGHT('0'+CAST([day] AS VARCHAR(2)),2) + '/'+
   CAST([year] AS VARCHAR(4))
FROM
   YourTable;

Having said that, ideally I would think you want the result to be of date/datetime type. If that is the case, you can do what I did above and cast it to date. For reasons I can't fully explain, I don't like casting numbers to varchars when doing datetime operations. If you share that view, you can do something like this - which may be more convoluted and dense, but I like it:

SELECT
   DATEADD(dd,[day]-1,DATEADD(mm,[mnth]-1,DATEADD(yy,[year]-1900,'19000101')))
FROM
   YourTable

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-01 : 13:56:30

SELECT TOP 1 Mth+'/'+Day+'/'+Year
FROM yourTable
ORDER BY convert(datetime,Yr+Mth+Dy) desc

Jim

Everyday I learn something that somebody else already knew

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-12-01 : 13:59:10

here's another way

Declare @t table ([year] int, mth char(2), [day] char(2))
insert @t select 2010, '01', '01'
insert @t select 2009, '05', '29'
insert @t select 2010, '03', '01'



SELECT	Convert(varchar(11),
		DateAdd(day, convert(int, [day])-1,
			DateAdd(month, convert(int, [mth])-1, DateAdd(year, [year]-1900, 0))
		),
	101
)
from	@t

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-12-01 : 14:45:03

Thanks All !
Jim's response was the easiest/best !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources