Grabbing a date from a filename

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  October 18, 2006 at 4:23 am

  #170114

  Hello all,

  I have the following filename

  GC_20060616_All_Demographics

  I am trying to pull out the date e.g. the number between the _ _ characters and using it as a small date format. This is probably very easy and I have done it before getting a Surname out of a field after a space but Im struggling with this one.

  Thanks in advance

  Debbie

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  October 18, 2006 at 5:26 am

  #666429

  hi debbie

  if u r sure that the format(including the length) of the filename will not change then this shud work

  select

  substring('GC_20060616_All_Demographics',(charindex('_','GC_20060616_All_Demographics',1)+1),8)

  "Keep Trying"

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  October 18, 2006 at 5:32 am

  #666432

  Fantastic,

  All my files follow the same format so this is working fine

  select DISTINCT CAST(substring(Filename,(charindex('_',FileName,1)+1),8)AS SmallDateTime)

  FROM dbo.FailedExports

  WHERE FileName LIKE 'GC%'

  Thanks again

  Debbie