Search code examples
sqlsql-server-2000

Make date with month week and weekday in SQL Server 2000

In SQL Server 2000, I have:

  • Year: 2015,
  • Month: 01,
  • Week: 4,
  • Week-Day: 2

I need: 2015-01-19

I have :

  • Year: 2015,
  • Month: 01,
  • Week: 3,
  • Week-Day: 3

I need: 2015-01-13

And so on.

Thanks in advance.

Solution

  • It is rather ambiguous what you mean by the first week of a month, but taking the common definition of the week starting on the first day, you can do this with date arithmetic. The result is something like:

    select convert(datetime,
               @year * 10000 + @month*100 + (@week - 1) * 7 + (@weekday - 1),
               112)

    To be honest, I'm not 100% sure if SQL Server 2000 will convert the date correctly. You might need to cast it as a string first:

    select convert(datetime,
               cast(@year * 10000 + @month*100 + (@week - 1) * 7 + (@weekday - 1) as varchar(8)),
               112)

    And, as the comment says, you don't want to be using unsupported software. It is time to upgrade.