Professional Web Applications Themes

How to combine date strings with text - Microsoft SQL / MS SQL Server

I am transferring an Access database to SQL Server 2000 and running into trouble creating the same 'combination' fields in the SELECT statement. The Access database calculates the number of days from an order start date and then adds the static text "days" to the end. The output should include the order start date in mm/dd/yy format with the calculated number of days next to it. Like this: MM/DD/YY (# Days) 09/29/98 (255 Days) I have attempted to accomplish this with the following code, but SQL seems to have a problem with combining date and text in the same field: ...

  1. #1

    Default How to combine date strings with text

    I am transferring an Access database to SQL Server 2000 and running
    into trouble creating the same 'combination' fields in the SELECT
    statement. The Access database calculates the number of days from an
    order start date and then adds the static text "days" to the end. The
    output should include the order start date in mm/dd/yy format with the
    calculated number of days next to it. Like this:

    MM/DD/YY (# Days)
    09/29/98 (255 Days)

    I have attempted to accomplish this with the following code, but SQL
    seems to have a problem with combining date and text in the same
    field:

    -- Start Date (test for null and then calculate days from start date
    to today)
    CASE ISNULL(dbo.tblOrder.OrderStartDate,' ')
    WHEN ' '
    THEN ' '
    ELSE GETDATE()-dbo.tblOrder.OrderStartDate END + N'Days' AS [Start Date]

    I am obviously new to SQL and would appreciate any guidance you can
    provide. Thank you in advance!
    Christopher Guest

  2. #2

    Default Re: How to combine date strings with text

    First of all, unless you are supporting international characters, change
    your tables' datatypes to non-unicode datatypes: char instead of nchar,
    varchar instead of nvarchar, etc. Then drop the N from your code below.

    You almost have it right: you need to convert the result of the date
    arithmetic to a string. I would use DATEDIFF so it will look like this:
    CASE ISNULL(dbo.tblOrder.OrderStartDate,' ')
    WHEN ' ' THEN ' '
    ELSE
    CAST(DATEDIFF(days,dbo.tblOrder.OrderStartDate,GET DATE()
    AS varchar(10))
    END + ' Days' AS [Start Date]

    HTH,
    Bob Barrows

    Christopher Dean wrote: 



    Bob Guest

  3. #3

    Default Re: How to combine date strings with text

    Thank you very much, Jacco. That works perfect!


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Christopher Guest

Similar Threads

  1. Get latest (by date) record and combine it with sum()
    By alanspamenglefield@yahoo.co.uk in forum MySQL
    Replies: 0
    Last Post: January 6th, 03:59 PM
  2. dealing with empty date strings
    By Lossed in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 11th, 08:01 PM
  3. Can you combine text boxes in fh10?????
    By ~ shelly ~ in forum Macromedia Freehand
    Replies: 9
    Last Post: January 19th, 05:13 PM
  4. Replies: 4
    Last Post: September 17th, 08:59 PM
  5. extract strings between alternating text
    By Lydia Shawn in forum PERL Miscellaneous
    Replies: 5
    Last Post: August 10th, 01:42 PM

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