convert date to string then grab parts of the string

Question

Post reply

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.

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

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

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 · Answer 2

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: 11642 More actions · Answer 3

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: 287024 More actions · Answer 4

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