Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    Chicago, IL
    Posts
    2

    Question Unanswered: Now whats wrong with this one?

    I'm trying to convert a date to our format, and select certain info for a new file. Does anybody see what's wrong? Also, where do I say where the new info goes?

    Thanks alot.


    CREATE PROCEDURE spx_MilleniumExport
    @date datetime = null
    AS


    -- set @date = '9/3/2003'
    if ( @date is null )
    begin
    -- Default to current day
    set @date = convert(varchar,getdate(),101)
    end

    set @date = convert(varchar,@date,101)


    select
    [result],[IntCode], [name],[Address] ,
    [City] ,
    [State] ,
    [Zip] ,
    [Phone],
    [DayPhone],
    [calldate],
    [calltime],
    [AppointmentDay],
    [AppointmentDate],
    [AppointmentTime],
    [meetingwith],
    [comments],
    [SpeakTo],
    [Askfor],
    [Insurancename],
    [CrossStreet1],
    [CrossStreet2],
    [FirstName],
    [LastName],
    [SpouseFName]
    [Operator] from Clients.DBO.Millenium as cli


    where result = 'ap'
    and convert(datetime, calldate) = @date

    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You must specify a length when converting to varchar, or the default length is just a single character. You can conslidate and correct this code:

    -- set @date = '9/3/2003'
    if ( @date is null )
    begin
    -- Default to current day
    set @date = convert(varchar,getdate(),101)
    end
    set @date = convert(varchar,@date,101)

    ...into this:
    set @date = convert(varchar(10), isnull(@date, getdate()), 101)

    blindman

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Good point, blindman, but what's the meaning of converting the input parameter into a string, and comparing it with a date time field? So, left out all conversion, and just set your @Date to GetDate, if @Date appears to be NULL.

    There was also another question: where does the export go to? This procedure returns a recordset. It's up to you to insert this recordset into a table, for example.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Good point DoctorBlue, but what you forgot is that the getdate() function returns the time of the day also .. so you will need to convert that into varchar or small datetime before using it to compare with another datetime field.

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Right, the time portion must be truncated at least for GetDate. The solution depends whether the table field holds a time portion too, and whether the input @Date has a time portion. If both doesn't, I'd keep using dates, and just taking care of GetDate.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    blindman, I think the default for VARCHAR is 30 characters, at least judging by what I see.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good catch, ms_sql_dba.

    Books Online:
    "When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30."

    I guess the lesson is to always declare your variable lengths.

    Question of the day: Why 30?

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    that's a good one. dunno! anyone?

  9. #9
    Join Date
    Sep 2003
    Location
    Chicago, IL
    Posts
    2
    Thanks alot ladies and gents.

Posting Permissions

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