Professional Web Applications Themes

Convarting date and time to date - Microsoft SQL / MS SQL Server

Roy Goldhammer wrote:  That's because there isn't one. If you want to store date without time, then you need to store it as a string. Of course, that means you won't be able to treat it as a real date without converting it to datetime. Look up a topic called "Using Date and Time Data" in BOL. Bob Barrows...

  1. #1

    Default Re: Convarting date and time to date

    Roy Goldhammer wrote: 

    That's because there isn't one. If you want to store date without time, then
    you need to store it as a string. Of course, that means you won't be able to
    treat it as a real date without converting it to datetime.

    Look up a topic called "Using Date and Time Data" in BOL.

    Bob Barrows


    Bob Guest

  2. #2

    Default Re: Convarting date and time to date

    Thankes bob

    But i can't find the type of date only and not date and time
    Can you help me on it?
    "Bob Barrows" <com> wrote in message
    news:phx.gbl... 
    >
    > The answer is (sort of) in your subject line. Look up the CONVERT function
    > in Books Online (BOL).
    >
    > Bob Barrows
    >
    > PS. You can't store "only the date part" unless you store it as a string.
    > BOL has a very good discussion of the datetime datatype.
    >
    >[/ref]


    Roy Guest

  3. #3

    Default Re: Convarting date and time to date

    The same example can be interpreted as

    create table #t (
    with_time datetime,
    without_time as cast(convert(varchar(25), with_time, 112) as
    datetime),
    only_time as convert(varchar(10), with_time , 108)
    )
    insert into #t (with_time) values (getdate())
    select * from #t

    --
    -Vishal
    Roy Goldhammer <net.il> wrote in message
    news:phx.gbl... 
    > >
    > >[/ref]
    >
    >[/ref]


    Vishal Guest

  4. #4

    Default Re: Convarting date and time to date

    Gentlemen,

    All this nonsense about storing dates and times as strings ?
    Don't you like being able to use convert / datepart / dateadd / datediff / + / - /
    comparisons without worrrying about formats / ....

    Date without time = choose a particular reference time eg Midnight and use it.

    e.g.

    Insert MyTable
    ( NativeDatetimeCol )
    values
    ( '20030730' ) -- unsepararated string format, or one of the ISO8601 standard formats.

    or more explicitly

    Insert MyTable
    ( NativeDatetimeCol )
    values
    ( convert( char(10), GetDate(), 121 ) ) -- implicit conversion back from yyyy-mm-dd

    or even more explicitly

    Insert MyTable
    ( NativeDatetimeCol )
    values
    ( convert( datetime, convert( char(10), GetDate(), 121 ), 121 ) ) -- explicit roundtrip conversion

    Similarly for time - choose a reference date eg the default 1900-01-01 and use it.

    select convert( datetime, convert( char(8), GetDate(), 108 ), 108 )

    Storing dates or times in string format, is to put it more politely than I am wont to - plain daft.
    For times, int/tinyint/bigint SecondsOrSomethingSinceMidnight can occasionally be useful,
    but that's as far as I'm willing to go.

    Regards
    AJ

    "Roy Goldhammer" <net.il> wrote in message news:%phx.gbl... 


    Andrew Guest

  5. #5

    Default Re: Convarting date and time to date

    unfortunately this is kind of a habit in this newsgroup :-))) some asks for
    date only part of a datetime column and N replies spit a select
    convert(varchar, ...) without explaining that the result is actually a
    column of strings!

    cheers,
    </wqw>

    "Andrew John" <com> wrote in message
    news:eVx#phx.gbl... 
    / + / - / 
    it. 
    standard formats. 
    from yyyy-mm-dd 
    explicit roundtrip conversion 
    use it. 
    am wont to - plain daft. 
    occasionally be useful, 
    news:%phx.gbl... 
    >
    >[/ref]


    Vlad Guest

Similar Threads

  1. CFMX7.0.1 Administrator date time issue showing 13hrsbehind server time
    By bayfield in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 8th, 04:34 AM
  2. Replies: 3
    Last Post: October 21st, 07:13 PM
  3. Time/Date format and changing time to GMT
    By Shaiboy_UK in forum ASP
    Replies: 1
    Last Post: October 19th, 01:04 PM
  4. Does Canon Rebel Ti Date Time = EOS 300V Quartz Date
    By Paradise in forum Photography
    Replies: 7
    Last Post: August 4th, 02:37 AM
  5. Date and time...
    By SamMan in forum Macromedia Dreamweaver
    Replies: 12
    Last Post: July 20th, 11: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