TSQL question: How can I perform a calculation on on multiple values?

Hello Experts,

I need to return values like this '131531013' into a date format like 2007/1/5. I have the math for it:
declare @tsdate int
set @tsdate = 131531013
declare @day int      
declare @month int      
declare @year int      

select @day = @tsdate % 256      
select @month = (@tsdate % 65536) / 256      
select @year = @tsdate / 65536      

but I'm stuck on getting it into a multi-row dataset.

Thanks!
Keith
glo-dbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Please check the code
Create table myDate (dtString varchar(20))
--Consider you have created above table
insert into myDate values ('131531013')
--And inserted date you mentioend
select * from #myDate 
--Select Query
select cast((dtString  % 256) as varchar(2)) + '/' + cast(((dtString  % 65536) / 256) as varchar(2)) + '/' + cast((dtString / 65536) as varchar(4)) as [myDate] from #myDate 
--Output

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
First, what database are you using: MS SQL Server or MySQL? The question tags list both types ...


> but I'm stuck on getting it into a multi-row dataset

If you mean run that logic on a column of values, yes ... you could put the logic into a UDF. Then call the UDF from your query.  Example in MS SQL:

    SELECT    dbo.ConvertIntToDate( SomeIntegerCol ) FROM  TheTable ....

    http://msdn.microsoft.com/en-us/library/ms186755.aspx
    http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Also, is your int value ("131531013") some sort of epoch? Just wondering if there's an easier way to do the conversion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.