Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Data Types

    just to let you know CDate() does not return an error but it doesn't fix the sort problem

    I designed a SQL Server field holding a date as varchar and it doesn't sort very well. (Don't ask me why I decided to set a date field that way...)

    I'd like to *not* change the field type in SQL Server (although in a test it seemed to work) -- so is there a way to convert the data on reading into ASP? I tried CInt(myRS.Fields("Date_Completed")) and that bombed.

    If this isn't possible then let me know as well and I will work on re-setting the field type in SQL Server.

    TIA

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Converting Data Types

    Steve, I'm having trouble following your post. You tried CDate(Field) AS MyDate and that didn't work? I do recall some weirdness about using expressions in the ORDER BY clause. I think I used the Access query designer as a way to test out various syntaxes, but it has been a long time.

    What do you think about reformatting the field into a sortable string like yyyymmdd rather than converting it to a date type?

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Converting Data Types

    > It would be nice to be able to insert something in SQL Server to re-define the result
    > column as Date when needed and just leave the source table alone

    Two choices: Views and Stored Procedures.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Data Types

    that's a bit too gnomic. i am already running the result thru a view -- do you know how to convert a type in a view?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Data Types

    PROBLEM SOLVED - do a CAST conversion in View. Booyah!

    the basic issue is sorting in a table.

    this is what it looks like normally (by just calling up the field and sorting<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    9/8/04
    9/9/04
    9/30/04
    9/3/04

    I'm thinking this is sorting on date as though it is a text string (because the SQL Server column is set to varchar). And, basically, the sort gets invoked on-the-fly using a SQL String so I don't think I can jump into the dataset and convert the results to CDate() on display and then do the sort, if that makes sense.

    Incidentally, when I try re-defining the Date_Completed records to CDate() then the output is either the highest or lowest date in the result set, depending on how I sort it, which is *weird*.

    It would be nice to be able to insert something in SQL Server to re-define the result column as Date when needed and just leave the source table alone, as the system is 'live' and more than a little critical here....

    I suspect my design decision has turned around to bit me!

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Converting Data Types

    You callin' me a gnome? (Just kidding, I've never heard that word before.)

    A view, like a stored procedure or subquery, should let you recast data any way you like using an expression. So in your underlying table, your date column might be a string, and in your view, the date column could be a date. Now, I say this having never created a view, and having given away my reference books, but I understand that TSQL syntax should work in a view as it does in a query.

    { Reading microsoft.com... }

    Okay, try something like this in your SELECT clause:

    SELECT field1, field2, CAST(field3 AS datetime) AS 'DateAsDate', field4 ...

    Does the implicit casting work?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Data Types

    never heard of gnomic? it can come in handy sometimes.

    Anyhow, since I last 'spoke' to you I found and implemented CAST and now the heavens have showered my cubicle with flowers. (as if! no one here would understand what I did).

    so, problem solved. thanks for your help!

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Converting Data Types

    Could you post the precise syntax for future reference? Thanks.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Data Types

    In SQL Server you do this:

    SELECT
    CAST(sskelton.Complainant_Info.Date_Completed AS DATETIME) AS Date_Completed
    FROM sskelton.Complainant_Info

    For example. Depending on your login permissions the SQL Server objects may need to be fully qualified as in this example. If you don't assign an alias to the CAST conversion using AS, SQL Server will assign it something you may find less useful. There are a lot of different conversions besides DATETIME covered by CAST.

    DATETIME will work on a field with expected date settings.

    According to Books Online:

    SQL Server recognizes date and time data enclosed in single quotation marks (') in these formats:

    * Alphabetic date formats (for example, 'April 15, 1998')
    * Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
    * Unseparated string formats (for example, '19981207', 'December 12, 1998')

    (remember, Access uses hashes (#) as datetime deliminators.)

Posting Permissions

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