Professional Web Applications Themes

Formatting datetime values - Microsoft SQL / MS SQL Server

You will have make use of combinations of CONVERT functions. Try: select cast(convert(varchar, getdate(),112) + ' ' + left(convert(varchar, getdate(),108), 5) as datetime) -- -Vishal "Przemo" <p.dutkiewiczdeltatrans.pl> wrote in message news:0a7b01c34236$1ed63d90$a501280aphx.gbl... > Hi, > > I have chenged my datebase from Access to sql2000. I have > problems with formatting of datetime values. I have used > Format function in access. > 1. In sql2000 I see that there is only function CONVERT with > predefined format. And what if I would like to get from > datetime value only time in format hh:mm ? > 2. How to convert ...

  1. #1

    Default Re: Formatting datetime values

    You will have make use of combinations of CONVERT functions.
    Try:

    select cast(convert(varchar, getdate(),112) + ' ' + left(convert(varchar,
    getdate(),108), 5) as datetime)

    --
    -Vishal
    "Przemo" <p.dutkiewiczdeltatrans.pl> wrote in message
    news:0a7b01c34236$1ed63d90$a501280aphx.gbl...
    > Hi,
    >
    > I have chenged my datebase from Access to sql2000. I have
    > problems with formatting of datetime values. I have used
    > Format function in access.
    > 1. In sql2000 I see that there is only function CONVERT with
    > predefined format. And what if I would like to get from
    > datetime value only time in format hh:mm ?
    > 2. How to convert 3 values year, month and day to one
    > datetime value ? In access I was able to use DateSerial
    > function.
    >
    > Thanks!
    > Przemo

    Vishal Parkar Guest

  2. #2

    Default Re: Formatting datetime values

    1. SELECT CONVERT(CHAR(5), date_time_column, 108)
    Just use only the first 5 characters of hh:mm:ss
    2. There are many ways to skin this cat, this is one
    DATEADD(dd, day -1,DATEADD(mm, month - 1(DATEADD(yy, year -
    1900,'19000101')


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Przemo" <p.dutkiewiczdeltatrans.pl> wrote in message
    news:0a7b01c34236$1ed63d90$a501280aphx.gbl...
    > Hi,
    >
    > I have chenged my datebase from Access to sql2000. I have
    > problems with formatting of datetime values. I have used
    > Format function in access.
    > 1. In sql2000 I see that there is only function CONVERT with
    > predefined format. And what if I would like to get from
    > datetime value only time in format hh:mm ?
    > 2. How to convert 3 values year, month and day to one
    > datetime value ? In access I was able to use DateSerial
    > function.
    >
    > Thanks!
    > Przemo

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Formatting datetime values

    > select cast(convert(varchar, getdate(),112) + ' ' + left(convert(varchar,getdate(),108), 5) as datetime)

    Also translates to this:
    select dateadd(ss, -datepart(ss, getdate()), dateadd(ms, -datepart(ms, getdate()), getdate()))
    But I don't think that's what the poster wanted.

    "Vishal Parkar" <vgparkarhotmail.com> a écrit dans le message de news: #KA0OejQDHA.3236TK2MSFTNGP10.phx.gbl...
    > You will have make use of combinations of CONVERT functions.
    > Try:
    >
    > select cast(convert(varchar, getdate(),112) + ' ' + left(convert(varchar,
    > getdate(),108), 5) as datetime)
    >
    > --
    > -Vishal
    Guest

Similar Threads

  1. formatting money values
    By Mattastic in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: September 22nd, 11:33 AM
  2. Replies: 1
    Last Post: December 6th, 01:55 AM
  3. averaging the datetime values
    By SlowG in forum Informix
    Replies: 2
    Last Post: August 16th, 08:33 PM
  4. Replies: 2
    Last Post: August 5th, 06:35 PM
  5. Formatting combo default values
    By Trisha in forum Microsoft Access
    Replies: 2
    Last Post: July 4th, 10:19 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