SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Concatenating Numbers/Converting Dates to Integers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

grrr223
Starting Member

USA
30 Posts

Posted - 02/24/2004 :  11:54:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 02/24/2004 :  12:04:20  Show Profile  Visit ditch's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 02/24/2004 :  12:09:25  Show Profile  Visit ditch's Homepage  Reply with Quote
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

USA
30 Posts

Posted - 02/24/2004 :  16:07:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000