date for 15th Feb 12.00 a.m

• Pink123

  Default port

  Points: 1486

  More actions

  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,

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004577

  More actions

  Follow

  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)
  

• Pink123

  Default port

  Points: 1486

  More actions

  February 24, 2011 at 7:16 am

  #1290967

  Thanks for the reply.It helped me

• Jeff Moden

  SSC Guru

  Points: 1004577

  More actions

  Follow

  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)