-1

I have 2 x fields T1.period and T1.year both have data type smallint Using SQL Management Studio 2014 how may I Concatenate them AND return result as a DATE type?

Also, T1.period has values 1 to 12 how may I pad this out to 01 to 12 ... or will changing to date type sort this out?

Much appreciated!

Sample data ...

period yr 1 2015 2 2009 12 2009 11 2010 10 2011

Result will be ... Date 01/01/2015 01/02/2009 01/12/2009 01/11/2010 01/10/2011

Thanks!

Looks terrible struggling to get it into lists - sorry :(

K..
  • 43
  • 7

1 Answers1

1

Converting Your Values The Old Fashioned Way

Assuming that your t1.period value actually just represents a month, you could consider just converting your values to strings and then converting that concatenated result into a date via the CAST() function :

SELECT CAST(CAST(t1.year AS VARCHAR) + '-' + CAST(t1.period AS VARCHAR) + '-1' AS DATE)

This will build a string that looks like {year}-{month}-1, which will then be parsed as a DATE and should give you the first date of the given month/year.

Using The DATEFROMPARTS() Function

SQL Server 2012 and above actually support a DATEFROMPARTS() function that will allow you to pass in the various parts (year, month, day) to build a corresponding DATE object, with a much easier to read syntax :

SELECT DATEFROMPARTS(t1.year,t1.period,1)

Additionally, if you needed a DATETIME object, you could use DATETIMEFROMPARTS() as expected.

Rion Williams
  • 74,820
  • 37
  • 200
  • 327
  • Thanks for your help Rion - when I try to use DATEFROMPARTS I get error msg 'DATEFROMPARTS' is not a recognized built-in function name. Any ideas on work around? Thanks – K.. Jul 12 '16 at 13:21
  • As I mentioned, this is only available in SQL Server 2012 and above, if you aren't using such a version, you'll need to use the previously suggested approach. – Rion Williams Jul 12 '16 at 13:22
  • Thanks - tried old way and get .. Conversion failed when converting the varchar value '0-0' to data type int. Thanks for your help! – K.. Jul 12 '16 at 13:23
  • It sounds like that is bad data as your year and month would be `0` and `0` respectively, which doesn't seem like a valid, parsable date. – Rion Williams Jul 12 '16 at 13:25
  • Ahh I sorted that Now get .. Conversion failed when converting the varchar value '2011-1' to data type int. T1.period has values 1 to 12 how may I pad this out to 01 to 12 – K.. Jul 12 '16 at 13:30
  • You shouldn't need to pad the values with zeros as it should parse just fine. Are you making sure to append the trailing `-1` to ensure that your date is read as `{year}-{month}-1`? – Rion Williams Jul 12 '16 at 13:42
  • Ooops forgot to put the '' around -1 :) Getting there - thanks! How may I format to DD-MM-YYY Thanks for you assistance Rion! – K.. Jul 12 '16 at 13:48
  • changed DATETIME to DATE YAY! – K.. Jul 12 '16 at 13:49