Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Concatenating Numbers/Converting Dates to Integers

    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.

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Concatenating Numbers/Converting Dates to Integers

    just to be sure
    what is the type of your variable @Macola

    doeas it HAVE to be integer ?

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    Well, I am working with a database design that I cannot modify and it stores the dates in an int column with the format 20040101 and any conversions to the 100,000 values in the table takes too long, so I want to convert my date to an integer.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    i'm not sure if this is what you want

    but create this user define function

    CREATE function dbo.Date(@Date DateTime)
    returns Varchar(10) as
    begin
    return(
    Cast(Datepart(yy,@Date) as varchar) +
    Replicate('0',2-len(Cast(Datepart(mm,@Date) as varchar)))+Cast(Datepart(mm,@Date) as varchar) +
    Replicate('0',2-len(Cast(Datepart(dd,@Date) as varchar)))+Cast(Datepart(dd,@Date) as varchar)
    )
    end



    then you'll be able to get easily

    set @Macola=dbo.date('1/1/2004')
    set @Macola=dbo.date(@MacolaDate)


    the Replicate+Len functions adds the 0 if necessary

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    Thank you!

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    is it what you needed ('cause i'm going offline)

  7. #7
    Join Date
    Feb 2004
    Posts
    126
    Yes it is, I appreciate your help.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cross post?

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32771

    Anyway, what does your base data look like

    Since you say you want it to be 20040101, are you assuming that that's the int value?

    Where do you get 1/1/2004 from?

    Some ddl might help

    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,
    That part confused me...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Posts
    126
    Sorry for any confusion. Let me try this again.

    I need to pass StartDate and EndDate parameters to the query I use as the record-source for several reports.

    My database stores dates as integers in the format 20040101, but I don't want my end users to have to enter dates in that integer format, also, I figured it would be easier to convert the integer dates to datetimes in the query so that I wouldn't have to make this conversion on every report, it would already be done for me.

    I wrote a T-SQL scalar function that converts from the integer format to a datetime format, and I just used that 'fnIntToDate(doc_date)' as the column returned in the header for the 3 date columns I have. This function looks like this, and works well:
    Code:
    Convert(Datetime,Left(Right(@MacolaDate,4),2) + '/' + Right(@MacolaDate,2) +  '/' + Left(@MacolaDate,4))
    I do my development on a database with 1,000 rows, but our production database has over 100,000, and this date conversion (along with a few other things I have since fixed) caused the query to time-out. And according the the query analyzer, I can save 3-4 seconds PER COLUMN if I just leave the integers as integers and convert my date parameters to integers instead.

    So I'm asking for help on a function that does the opposite of what my function does. I want to pass it a datetime value and have it return a date in the format 20040101. I had gotten all of it except the leading 0s, but I think the previous poster answered that for me, so i'm goign to go try that now.

    Thank you.

  10. #10
    Join Date
    Feb 2004
    Posts
    126
    And what he told me worked great . I just converted it to an integer and I'm good to go.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •