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)
 Concatenating Numbers/Converting Dates to Integers

Author  Topic 

grrr223
Starting Member

30 Posts

Posted - 2004-02-24 : 11:54:23
My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:


SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)


However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-24 : 12:04:20
Try this:

DECLARE @Macola INT
DECLARE @MacolaDate DATETIME

SET @MacolaDate = '1/1/2004'

SET @Macola = (Cast(Datepart(yy,@MacolaDate) as INT) * 10000) + (Cast(Datepart(mm,@MacolaDate) as INT) * 100) + Cast(Datepart(dd,@MacolaDate) as INT)


SELECT @Macola
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-24 : 12:09:25
This could also work:
DECLARE @Macola INT
DECLARE @MacolaDate DATETIME

SET @MacolaDate = '1/1/2004'

SET @Macola = (YEAR(@MacolaDate) * 10000) + (MONTH(@MacolaDate) * 100) + DAY(@MacolaDate)

SELECT @Macola
Go to Top of Page

grrr223
Starting Member

30 Posts

Posted - 2004-02-24 : 16:07:05
Thank you, I love these forums. It's just amazing to see the completely different approaches to the exact same relatively simple problem. I really like your way of doing it, I was like "Why didn't I think of that."

Someone else gave me this response which also works:

Cast(Cast(Datepart(yy,@MacolaDate) as varchar) +
Replicate('0',2-len(Cast(Datepart(mm,@MacolaDate) as varchar)))+
Cast(Datepart(mm,@MacolaDate) as varchar) +
Replicate('0',2-len(Cast(Datepart(dd,@MacolaDate) as varchar)))+
Cast(Datepart(dd,@MacolaDate) as varchar)as int)
Go to Top of Page
   

- Advertisement -