Professional Web Applications Themes

Function to P Date from String - Microsoft SQL / MS SQL Server

I've written a function to pull the datetime out of a memo field (migrating from Notes to SQL Server) <below>. The time element isn't needed, just the date. It's working, but the date is being changed. If you run the function (select dbo.FN_GETDATETIME('') --No need to pass anything in, hardcoded for sample), you can see that it is returning 07/23/2002 instead of 07/23/2003. Any ideas? Thanks, Morgan CREATE FUNCTION FN_GETDATETIME(comment2 VARCHAR(8000)) RETURNS SMALLDATETIME AS BEGIN declare looper int declare retval smalldatetime declare comment varchar(1000) set looper = 1 set comment = '7/23/2003 6:57:22 PM by: Morgan-> (SI: PARTS NETWORK) Email ...

  1. #1

    Default Function to P Date from String

    I've written a function to pull the datetime out of a memo field (migrating
    from Notes to SQL Server) <below>. The time element isn't needed, just the
    date.

    It's working, but the date is being changed. If you run the function (select
    dbo.FN_GETDATETIME('') --No need to pass anything in, hardcoded for sample),
    you can see that it is returning 07/23/2002 instead of 07/23/2003.

    Any ideas?

    Thanks,

    Morgan

    CREATE FUNCTION FN_GETDATETIME(comment2 VARCHAR(8000))
    RETURNS SMALLDATETIME
    AS
    BEGIN
    declare looper int
    declare retval smalldatetime
    declare comment varchar(1000)

    set looper = 1
    set comment = '7/23/2003 6:57:22 PM by: Morgan-> (SI: PARTS NETWORK) Email
    sent to Morgan.'
    while looper <= datalength(comment)
    begin
    if isdate(substring(comment, 1, looper)) = 1
    begin
    select retval = convert(smalldatetime, substring (comment, 1, looper))
    set looper = datalength(comment)
    end
    else set looper = looper + 1
    end

    RETURN RETVAL
    END


    Morgan Guest

  2. #2

    Default Re: Function to P Date from String

    I think I fixed it...
    Changed the return type to varchar and killed the convert. Apparently the
    isdate function was returning 1 before it had read the whole date, and the
    year was defaulting to 2002? Anyways, I added looper + 4 to the substring
    function to get the correct date.

    alter FUNCTION FN_GETDATETIME(comment VARCHAR(8000))
    RETURNS smalldatetime
    AS
    BEGIN
    declare looper int
    declare retval smalldatetime
    --declare comment varchar(1000)

    set looper = 1
    --set comment = '7/23/2003 6:57:22 PM by: Morgan-> (SI: HDA PARTS NETWORK)
    Email sent to Morgan.'
    while looper <= datalength(comment)
    begin
    if isdate(substring(comment, 1, looper)) = 1
    begin
    select retval = convert(smalldatetime,substring (comment, 1, looper + 4))
    set looper = datalength(comment)
    end
    else set looper = looper + 1
    end

    RETURN RETVAL
    END


    "Morgan" <net> wrote in message
    news:phx.gbl... 
    (migrating 
    (select 
    sample), 
    Email 


    Morgan Guest

  3. #3

    Default Re: Function to P Date from String

    Hi Wayne, thanks for replying. I discovered as much after I posted. I posted
    what appears to be a working version (based on my tests) as a reply to this
    thread.

    Thanks again!

    Morgan

    "Wayne Snyder" <com> wrote in message
    news:phx.gbl... 
    Email 
    > (migrating [/ref]
    the 
    > (select 
    > sample), 
    > Email [/ref]
    looper)) 
    >
    >[/ref]


    Morgan Guest

  4. #4

    Default Re: Function to P Date from String

    Hi Raydan,
    Thankfully all of the dates -are- at the front of the string, so there is no
    guesswork involved. As far as I could tell, the modified version I posted
    earlier is returning the correct date based on SET DATEFORMAT MDY.

    Thanks,
    Morgan

    "raydan" <nospamcom> wrote in message
    news:phx.gbl... 
    > Email [/ref]
    looper)) [/ref]
    not 
    >
    >[/ref]


    Morgan Guest

  5. #5

    Default Re: Function to P Date from String

    TRY
    select retval = convert(smalldatetime, substring (comment, 1, looper + 3))




    "Morgan" <net> wrote in message news:<phx.gbl>... 
    Guy Guest

  6. #6

    Default Re: Function to P Date from String

    Take this much out of the function and execute it in Query yzer..

    declare looper int
    declare retval smalldatetime
    declare comment varchar(1000)

    set looper = 1
    set comment = '7/23/2003 6:57:22 PM by: Morgan-> (SI: PARTS NETWORK) Email
    sent to Morgan.'
    while looper <= datalength(comment)
    begin
    if isdate(substring(comment, 1, looper)) = 1
    begin
    select substring(comment, 1, looper)
    select retval = convert(smalldatetime, substring (comment, 1, looper))
    set looper = datalength(comment)

    end
    else set looper = looper + 1
    end

    The first time isdate returns true the string is 7/23/2.....so you are not
    picking up the entire date string.
    "Morgan" <net> wrote in message
    news:phx.gbl... 
    (migrating 
    (select 
    sample), 
    Email 


    Wayne Guest

Similar Threads

  1. #40342 [NEW]: date() function not parsing complete string format
    By garmoan at gmail dot com in forum PHP Bugs
    Replies: 3
    Last Post: February 5th, 09:29 AM
  2. How to p a string into a date?
    By csteinola in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: March 20th, 05:05 PM
  3. P Date bug?
    By Guillaume in forum Ruby
    Replies: 5
    Last Post: January 25th, 07:33 PM
  4. p php string
    By MrBrain in forum PHP Development
    Replies: 1
    Last Post: October 15th, 11:29 PM
  5. P part of string (mid string function ?)
    By mimages in forum PHP Development
    Replies: 7
    Last Post: September 27th, 12:58 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139