date format

  • TO_DATE('11/17/2005 20:51:27', 'MM/DD/YYYY HH24:MI:SS')

    above function in Oracle.. wht is the equavalent in sql server for the format of date 'MM/DD/YYYY HH24:MI:SS' ?

    any help pls?

  • This has all the data on formatting dates in T-SQL: http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Honestly, though, you should be formatting dates in the presentation layer, not in the database or data access layer. Doing it at presentation allows for localization, personal options, et al, doing it in lower layers blocks those options.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I think the CONVERT function is what you're looking for. Or you could allow the data layer to store the data and leave the formatting to the presentation layer.

    John

  • John Mitchell-245523 (12/14/2010)


    I think the CONVERT function is what you're looking for. Or you could allow the data layer to store the data and leave the formatting to the presentation layer.

    John

    Jinx!

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • MonsterRocks (12/14/2010)


    TO_DATE('11/17/2005 20:51:27', 'MM/DD/YYYY HH24:MI:SS')

    above function in Oracle.. wht is the equavalent in sql server for the format of date 'MM/DD/YYYY HH24:MI:SS' ?

    any help pls?

    I know this post is several days old but why is it necessary to format a date in the database instead of more properly in the GUI?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • s jeff.. i realized that.. i made it in presentation layer

  • Perfect. Thanks for the feedback. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi FIOlks

    can someone tell me what I am not doing correctly

    the first sql works but the second does not

    vercdate is a datetime field

    can someone explain to me why the first works and the second does not:

    what am i missing here

    select

    COUNT (docid) as 'Version Count'

    from

    dversdata

    where

    vercdate >= '20090101 00:00:00'

    and

    vercdate <= '20091231 23:59:59';

    go

    Version Count

    -------------

    2489931

    select

    COUNT (docid) as 'Version Count'

    from

    dversdata

    where

    WHERE vercdate BETWEEN '20090101 00:00:00' AND '20091231 23:59:59';

    go

    Msg 156, Level 15, State 1, Server NSAB-SS73-SQL-N, Line 6

    Incorrect syntax near the keyword 'WHERE'.

    Thanks

    Jim

  • You have "where" twice.

  • Damn !

    thanks Michael

    it's Monday 🙂

    I must have stared at that for sometime and it did not even compute

    Jim

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply