Professional Web Applications Themes

Help w/ SQL - Microsoft SQL / MS SQL Server

Strange... select dateadd(dd, number_field-1, datetime_field) as desired_result from tableName "Rob" <b_sawev1.net> a écrit dans le message de news: 08fc01c34193$aa39d2a0$a001280aphx.gbl... > I have the following in a table: > datetime_field..........number_field > 2003-06-01 00:00:00.000...1 > 2003-06-01 00:00:00.000...2 > 2003-06-01 00:00:00.000...3 > > I want to select from this table and have the number field > represent the day. For example, I want: > datetime_field..........number_field..desired_resu lt > 2003-06-01 00:00:00.000...1..........2003-06-01 > 2003-06-01 00:00:00.000...2..........2003-06-02 > 2003-06-01 00:00:00.000...3..........2003-06-03...

  1. #1

    Default Re: Help w/ SQL

    Strange...
    select dateadd(dd, number_field-1, datetime_field) as desired_result from tableName

    "Rob" <b_sawev1.net> a écrit dans le message de news: 08fc01c34193$aa39d2a0$a001280aphx.gbl...
    > I have the following in a table:
    > datetime_field..........number_field
    > 2003-06-01 00:00:00.000...1
    > 2003-06-01 00:00:00.000...2
    > 2003-06-01 00:00:00.000...3
    >
    > I want to select from this table and have the number field
    > represent the day. For example, I want:
    > datetime_field..........number_field..desired_resu lt
    > 2003-06-01 00:00:00.000...1..........2003-06-01
    > 2003-06-01 00:00:00.000...2..........2003-06-02
    > 2003-06-01 00:00:00.000...3..........2003-06-03
    Guest

  2. #2

    Default Help w/ SQL

    I have the following in a table:
    datetime_field..........number_field
    2003-06-01 00:00:00.000...1
    2003-06-01 00:00:00.000...2
    2003-06-01 00:00:00.000...3

    I want to select from this table and have the number field
    represent the day. For example, I want:
    datetime_field..........number_field..desired_resu lt
    2003-06-01 00:00:00.000...1..........2003-06-01
    2003-06-01 00:00:00.000...2..........2003-06-02
    2003-06-01 00:00:00.000...3..........2003-06-03
    Rob Guest

  3. #3

    Default Re: Help w/ SQL

    Take a look at the DATEADD function in SQL Server Books Online. You can do
    something along the lines of:

    SELECT col1, col2, DATEADD(d, col2 - 1, col1)
    FROM tbl ;

    T-SQL also allows you to do:

    SELECT col1, col2, col1 + (col2 - 1)
    FROM tbl ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: Help w/ SQL

    > SELECT col1, col2, col1 + (col2 - 1) FROM tbl
    I know "col1 + (col2 - 1)" works but will it always work?
    Is this "acceptable/standard" behavior?
    What does ANSI say about this?

    "Anith Sen" <anithbizdatasolutions.com> a écrit dans le message de news: [email]uMiAmRZQDHA.2036TK2MSFTNGP10.phx.gbl[/email]...
    > Take a look at the DATEADD function in SQL Server Books Online. You can do
    > something along the lines of:
    >
    > SELECT col1, col2, DATEADD(d, col2 - 1, col1)
    > FROM tbl ;
    >
    > T-SQL also allows you to do:
    >
    > SELECT col1, col2, col1 + (col2 - 1)
    > FROM tbl ;
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >
    Guest

  5. #5

    Default Re: Help w/ SQL

    It may not be the 'standard' or always a recommended method, but I think I
    have mentioned this is allowed in T-SQL, though.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  6. #6

    Default Re: Help w/ SQL

    Adding days this way (with a constant integer) has the advantage that the
    there is no function on the datetime column, so indexes on it can be used.
    Because datetime has a higher data type precedence than integer, the integer
    will be explicitly converted to a datetime. (If the datetime got explicitly
    converted the indexes still wouldn't be used of course).


    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:eAi16jZQDHA.2128TK2MSFTNGP12.phx.gbl...
    > It may not be the 'standard' or always a recommended method, but I think I
    > have mentioned this is allowed in T-SQL, though.
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Jacco Schalkwijk Guest

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