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.
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 INTDECLARE @MacolaDate DATETIMESET @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 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-02-24 : 12:09:25
|
This could also work:DECLARE @Macola INTDECLARE @MacolaDate DATETIMESET @MacolaDate = '1/1/2004'SET @Macola = (YEAR(@MacolaDate) * 10000) + (MONTH(@MacolaDate) * 100) + DAY(@MacolaDate)SELECT @Macola |
|
|
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) |
|
|
|
|
|
|
|