Problem with Date formating

• rayash16

  Ten Centuries

  Points: 1054

  More actions

  April 8, 2009 at 12:53 pm

  #99601

  Hi,

  I have a situation where i take extract the information about quarter and year from the name of the flat file which i use later in staging table and fact table.

  the name of the file is like AB_Q1_08.txt

  i use substring to extract info as

  select SUBSTRING(AB_Q1_08.txt,9,2)

  select Replace(Substring(AB_Q1_08.txt,12,5),'_','-')

  result what i get it ,AB and Q1-08 respectively.

  is there any way i can save Q1-08 in date format ,say quarter or year.

  i tried breaking year as 2008 but it also stores as varchar.

  Can somebody tell me how do i convert it to date format.

  thanks

  ashish

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  April 8, 2009 at 2:20 pm

  #974695

  You can convert this string format into a datetime if you are happy to use the following dates for the 4 quarters:

  Q1: YYYY-01-01

  Q2: YYYY-04-01

  Q3: YYYY-07-01

  Q4: YYYY-10-01

  DECLARE @dateString char(5)

  DECLARE @result datetime

  SELECT @dateString = 'Q2-07'

  IF (@dateString LIKE 'Q[1-4]-[0-9][0-9]') BEGIN

  DECLARE @thisYear int

  SELECT @thisYear = YEAR(GETDATE())

  /* Deal with 2-digit year */

  DECLARE @thatYear int

  SELECT @thatYear = @thisYear - @thisYear % 100 + CONVERT(int, SUBSTRING(@dateString, 4, 2))

  SELECT @thatYear = @thatYear

  + CASE WHEN (@thisYear - @thatYear < -40) THEN -100 WHEN (@thisYear - @thatYear >= 60) THEN 100 ELSE 0 END

  SELECT @result = DATEADD(quarter, CONVERT(int, SUBSTRING(@dateString, 2, 1)) - 1, DATEADD(year, @thatYear - 1900, 0))

  END

  SELECT @result AS [Date]

• rayash16

  Ten Centuries

  Points: 1054

  More actions

  April 8, 2009 at 2:50 pm

  #974723

  Hey thanks a lot for the script

  it is very handy.