Cast/Convert to Date with RTrim

• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  July 19, 2016 at 4:35 am

  #328307

  Hi all,

  I want to retrieve date and remaining info from a column, split out the date, and order the remaining info by that derived date column.

  Column-Name: F1

  Sample Row: The Double Cross (01/Mar/13)

  This is as far as I've gotten...

  SELECT RIGHT(RTRIM(F1), 11) As Date, F1 as Title

  FROM [Practice].[dbo].[ReadingList]

  Order by Date

  However SQL (understandably) doesn't yet recognise that it's date-time, and I can't find Cast/Convert examples containing Trim functions.

  Thanks,

  JB

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 19, 2016 at 4:47 am

  #1890804

  maybe?

  DECLARE @f1 AS VARCHAR(100)= 'The Double Cross (01/Mar/13)';

  SELECT CAST(LEFT(RIGHT(RTRIM(@F1), 10), 9) AS DATE);

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  July 19, 2016 at 5:10 am

  #1890808

  J Livingston SQL (7/19/2016)

  ---

  maybe?

  DECLARE @f1 AS VARCHAR(100)= 'The Double Cross (01/Mar/13)';

  SELECT CAST(LEFT(RIGHT(RTRIM(@F1), 10), 9) AS DATE);

  No that just returns a single date, and one I (would) need to embed per statement at that

  I'll rephrase -

  I need to order F1 per the dates embedded at the end of F1, so, expected result set (I'm not worried about repeating the date at the end)...

  DateTitle

  (12/Jan/12)The Setup (12/Jan/12)

  (10/Jan/13)Broken Remnants (10/Jan/13)

  (08/Jun/13)Big All Over (08/Jun/13)

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 19, 2016 at 5:33 am

  #1890814

  JaybeeSQL (7/19/2016)

  ---

  J Livingston SQL (7/19/2016)

  ---

  maybe?

  DECLARE @f1 AS VARCHAR(100)= 'The Double Cross (01/Mar/13)';

  SELECT CAST(LEFT(RIGHT(RTRIM(@F1), 10), 9) AS DATE);

  No that just returns a single date, and one I (would) need to embed per statement at that

  I'll rephrase -

  I need to order F1 per the dates embedded at the end of F1, so, expected result set (I'm not worried about repeating the date at the end)...

  DateTitle

  (12/Jan/12)The Setup (12/Jan/12)

  (10/Jan/13)Broken Remnants (10/Jan/13)

  (08/Jun/13)Big All Over (08/Jun/13)

  CREATE TABLE yourtable(

  F1 VARCHAR(50) NOT NULL

  );

  INSERT INTO yourtable(F1) VALUES ('Broken Remnants (10/Jan/13)');

  INSERT INTO yourtable(F1) VALUES ('Big All Over (08/Jun/13)');

  INSERT INTO yourtable(F1) VALUES ('The Setup (12/Jan/12)');

  SELECT F1

  FROM yourtable

  ORDER BY CAST(LEFT(RIGHT(RTRIM(F1), 10), 9) AS DATE)

  DROP TABLE yourtable;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day