Post reply

date for 15th Feb 12.00 a.m

  • February 23, 2011 at 2:22 pm

    #234259

    Hi,

    How would represent date for 15th feb 2011 12 a.m in the same format as getdate() does?

    will it be something like this in sql server 2005

    '2011-02-15 00:00:00.000'

    Please help

    Thanks,

  • February 23, 2011 at 4:02 pm

    #1290676

    In short, yes.

    Try this:

    SELECT CONVERT(DATETIME,'15 Feb 2011 12 am',121) as my_date ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 23, 2011 at 4:20 pm

    #1290679

    Pink123 (2/23/2011)

    Hi,

    How would represent date for 15th feb 2011 12 a.m in the same format as getdate() does?

    will it be something like this in sql server 2005

    '2011-02-15 00:00:00.000'

    Please help

    Thanks,

    The following can easily be modified to do a whole table provided that the table contains the same type of date markings (1st, 2nd, 3rd, 4th, etc). It will produce incorrect dates for other formats.

    DECLARE @YourDate VARCHAR(50);

    SELECT @YourDate = '15th feb 2011 12 a.m';

    WITH

    cteFindPositions AS

    (

    SELECT Original = @YourDate,

    StartPos = PATINDEX('%[0-9][a-z]%',@YourDate)+1,

    EndPos = PATINDEX('% [a-z]%',@YourDate)

    )

    SELECT CAST(REPLACE(STUFF(Original, StartPos, EndPos-StartPos,''),'.','') AS DATETIME)

    FROM cteFindPositions

    ;

    --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)

  • February 24, 2011 at 7:16 am

    #1290967

    Thanks for the reply.It helped me

  • February 25, 2011 at 5:09 pm

    #1291846

    You bet. 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)

Viewing 5 posts - 1 through 4 (of 4 total)

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