convert date to string then grab parts of the string

• maria.lindquist

  Ten Centuries

  Points: 1112

  More actions

  October 24, 2016 at 8:40 am

  #318365

  I'm trying to convert a date to a string then grab parts of the string to make a value but it is not working correctly

  Here is my code:

  select externalId1

  , externalId2

  , fund_name

  , fund_externalId1

  , award_paidAmount

  , disbursement_netAmount

  , disbursement_scheduledDate

  , disbursement_createdOn

  , '5' + SUBSTRING(disbursement_scheduledDate,4,1) + SUBSTRING(disbursement_scheduledDate,6,2)

  , disbursement_modifiedOn, award_createdOn, award_modifiedOn

  , case when a.disbursement_adjustedReleaseDate IS NULL then a.disbursement_scheduledDate else a.disbursement_adjustedReleaseDate END AS ScheduledDisbursementDate

  from dbo.dataExtract_AwardDisbursement_View a

  I'm getting this error:

  Msg 8116, Level 16, State 1, Line 1

  Argument data type datetime is invalid for argument 1 of substring function.

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  October 24, 2016 at 8:44 am

  #1908389

  You are attempting to use a string function on a date or datetime data type.

  You need to use cast or convert on the field first, then attempt the substring.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2016 at 8:51 am

  #1908394

  maria.lindquist (10/24/2016)

  ---

  I'm trying to convert a date to a string then grab parts of the string to make a value but it is not working correctly

  Here is my code:

  select externalId1

  , externalId2

  , fund_name

  , fund_externalId1

  , award_paidAmount

  , disbursement_netAmount

  , disbursement_scheduledDate

  , disbursement_createdOn

  , '5' + SUBSTRING(disbursement_scheduledDate,4,1) + SUBSTRING(disbursement_scheduledDate,6,2)

  , disbursement_modifiedOn, award_createdOn, award_modifiedOn

  , case when a.disbursement_adjustedReleaseDate IS NULL then a.disbursement_scheduledDate else a.disbursement_adjustedReleaseDate END AS ScheduledDisbursementDate

  from dbo.dataExtract_AwardDisbursement_View a

  I'm getting this error:

  Msg 8116, Level 16, State 1, Line 1

  Argument data type datetime is invalid for argument 1 of substring function.

  Care to explain what it is you are trying to accomplish?

• Y.B.

  SSChampion

  Points: 11652

  More actions

  October 24, 2016 at 9:07 am

  #1908402

  I'm not 100% sure what you are looking for but It would seem that DATEPART would be your friend as it appears you are trying to grab the year and month from a datetime field.

  Here are some examples...

  DECLARE @somedate DATETIME = GETDATE()

  SELECT

  @somedate AS myDate,

  DATEPART(year, @somedate) AS justYear,

  DATEPART(month, @somedate) AS justMonth,

  '5' + CAST(DATEPART(year, @somedate) AS VARCHAR(4)) + CAST(DATEPART(month, @somedate) AS VARCHAR(2)) AS endResult

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  October 24, 2016 at 10:57 am

  #1908424

  YB really hit the nail on the head. Depending on what you're trying to do, the DATEPART function can perform quite well. It's covered in Books Online at https://msdn.microsoft.com/en-us/library/ms174420.aspx.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions