select with a while loop

• orenk

  Old Hand

  Points: 388

  More actions

  February 1, 2016 at 2:52 am

  #311235

  Hey,

  is there a way to bring a select and a a while loop for a row together?

  What I like to do is a select the bring me the following:

  Nr || DateBegin || DateEnd || Datediff via function (count weekdays, without weekend and holiday days)

  I have a loop that works fine.

  declare @start datetime, @End date, @diff int, @C int

  select @start ='20.12.2015',

  @End = '03.01.2016',

  @diff = 0,

  @C = datediff(dd,@start,@End)

  while @C >0

  Begin

  if left(dbo.isarbeitstag(@End,3),1)=9

  begin

  set @diff = @diff +1

  end

  set @end =dateadd(d,-1,@end)

  set @C = @C-1

  end

  print @diff

  Now the @Start should be the row DateBegin and @End should be DateEnd.

  How can i bring the while loop in the select with the result in a row?

  Thanks for help

  Olaf

• orenk

  Old Hand

  Points: 388

  More actions

  February 1, 2016 at 6:17 am

  #1855656

  Solution:

  Add a new function.

  create FUNCTION AnzahlArbeitstage(@Start datetime , @End datetime)

  RETURNS int

  AS BEGIN

  declare @diff int, @C int

  select @diff = 0,

  @C = datediff(dd,@start,@End)

  while @C >0

  Begin

  if left(dbo.isarbeitstag(@End,3),1)=9

  begin

  set @diff = @diff +1

  end

  set @end =dateadd(d,-1,@end)

  set @C = @C-1

  end

  return @diff

  end

  select dbo.AnzahlArbeitstage( '01.01.2016','08.01.2016')

• Phil Parkin

  SSC Guru

  Points: 246956

  More actions

  February 1, 2016 at 6:23 am

  #1855658

  Well done on solving your problem.

  Generally though, we aren't big fans of two of the techniques you have employed here: namely WHILE loops and scalar functions, for performance reasons.

  Should you have any desire to learn some new and probably faster ways of solving your problem, you'll need to post more information so that people can understand what you are trying to do.

  Along with this narrative, if you include DDL, sample data and desired results (see the link in my signature for details), you'll likely get a working solution to your problem.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 1, 2016 at 7:34 am

  #1855676

  As Phil has pointed out the performance of this code will be poor. Can you post the code for function dbo.isarbeitstag? Does it reference a calendar table?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• orenk

  Old Hand

  Points: 388

  More actions

  February 1, 2016 at 7:42 am

  #1855680

  Hey, here the second function.

  It´s just for reporting, so the performance is not my top point 🙂

  Whats better then while?

  create FUNCTION isArbeitstag(@cDatum DATETIME, @typus AS INTEGER)

  /*

  @cdatum= Datum, das kontrolliert werden soll

  @typus = 0 (nur Wochenenden)

  @typus = 1 (Bundesweite Feiertage und Wochenenden)

  @typus = 2 (Alle Feiertage und Wochenenden)

  Beispielhafte Übergabewerte:

  ----------------------------

  '1,Neujahr'

  '2,Heilige drei Könige'

  '0,Samstag'

  '0,Sonntag'

  '9,Arbeitstag'

  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  Bitte die Einstellungen von "Datepart" kontrollieren

  Für Sonntage muss datepart(weekday,@cdatum)=7 gelten

  Wenn dies nicht zutrifft, dann müssen alle Aufrufe

  dieser Funktion entsprechend angepasst werden

  (Auch beim Buß- und Bettag)

  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  */

  RETURNS VARCHAR(27)

  AS BEGIN

  DECLARE @m AS INTEGER

  DECLARE @N AS INTEGER

  DECLARE @a AS INTEGER

  DECLARE @b-2 AS INTEGER

  DECLARE @C AS INTEGER

  DECLARE @d AS INTEGER

  DECLARE @e AS INTEGER

  DECLARE @tag AS INTEGER

  DECLARE @mon AS INTEGER

  DECLARE @xJahr AS INTEGER

  DECLARE @TagDerWoche AS INTEGER

  DECLARE @strJahr AS VARCHAR(4)

  DECLARE @Datum AS DATETIME

  DECLARE @datOsterSonntag AS DATETIME

  IF @typus=0 BEGIN -- nur Wochenenden

  IF DATEPART(weekday,@cdatum)=6 BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7 BEGIN

  RETURN '0,Sonntag'

  END

  END

  ELSE BEGIN -- Feiertage und Wochenenden

  SET @xJahr=YEAR(@cDatum)

  SET @strJahr=CONVERT(VARCHAR(4),@xJahr)

  SET @m = 23

  SET @N = 3

  IF @xJahr > 1799 BEGIN

  SET @N = 4

  END

  IF @xJahr > 1899 BEGIN

  SET @N = @N + 1

  SET @m = 24

  END

  IF @xJahr > 2099 BEGIN

  SET @N = @N + 1

  END

  SET @a = @xJahr % 19

  SET @b-2 = @xJahr % 4

  SET @C = @xJahr % 7

  SET @d = ((@a * 19) + @m) % 30

  SET @e = ((@B * 2) + (@C * 4) + (@d * 6) + @N) % 7

  SET @tag = @d + @e + 22

  IF @tag > 31 BEGIN

  SET @tag = @tag - 31

  IF (@Tag = 26) Or ((@Tag = 25) And (@d = 28) And (@a > 10)) BEGIN

  SET @tag = @tag - 7

  END

  SET @mon = 4

  END

  ELSE BEGIN

  SET @mon = 3

  END

  SET @datum = CONVERT(DATETIME,CONVERT(VARCHAR(2),@tag) +'.'

  + CONVERT(VARCHAR(2),@mon) + '.' + @strJahr)

  --Ostersonntag

  SET @datOsterSonntag = @Datum

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Ostersonntag'

  END

  --Karfreitag

  SET @datum = DATEADD(DAY,-2,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Karfreitag'

  END

  --Ostermontag

  SET @datum = DATEADD(DAY,1,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Ostermontag'

  END

  --Pfingstsonntag

  SET @datum = DATEADD(DAY,49,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Pfingstsonntag'

  END

  --Pfingstmontag

  SET @datum = DATEADD(DAY,50,@datOsterSonntag)

  IF @cdatum=@datum BEGIN

  RETURN '1,Pfingstmontag'

  END

  --Chr.Himmelfahrt

  SET @datum = DATEADD(DAY,39,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Christi Himmelfahrt'

  END

  --Fronleichnam

  SET @datum = DATEADD(DAY,60,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  IF @typus=2 BEGIN

  RETURN '2,Fronleichnam'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6 BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7 BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  --Buß+Bettag

  SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

  SET @TagDerWoche = 0

  WHILE @TagDerWoche <>3 BEGIN

  SET @TagDerWoche=DATEPART(weekday,@datum)

  IF @TagderWoche=3 BEGIN

  SET @datum=DATEADD(DAY,14,@datum)

  END

  ELSE BEGIN

  SET @datum=DATEADD(DAY,1,@datum)

  END

  END

  IF DAY(@datum) < 16 BEGIN

  SET @datum = DATEADD(DAY,7,@datum)

  END

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  IF @typus=2 BEGIN

  RETURN '2,Buß- und Bettag'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6 BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7 BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  -- feste Feiertage

  SET @datum = CONVERT(DATETIME,'01.01.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Neujahr'

  END

  --SET @datum = CONVERT(DATETIME,'06.01.' + @strJahr)

  --IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  -- IF @typus=2 BEGIN

  -- RETURN '2,Heilige drei Könige'

  -- END

  -- ELSE BEGIN

  -- IF DATEPART(weekday,@cdatum)=6 BEGIN

  -- RETURN '0,Samstag'

  -- END

  -- IF DATEPART(weekday,@cdatum)=7 BEGIN

  -- RETURN '0,Sonntag'

  -- END

  -- END

  --END

  SET @datum = CONVERT(DATETIME,'01.05.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Maifeiertag'

  END

  SET @datum = CONVERT(DATETIME,'03.10.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Tag der deutschen Einheit'

  END

  SET @datum = CONVERT(DATETIME,'15.08.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  IF @typus=2 BEGIN

  RETURN '2,Mariä Himmelfahrt'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6 BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7 BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  SET @datum = CONVERT(DATETIME,'31.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Silvester'

  END

  SET @datum = CONVERT(DATETIME,'24.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,Heiligabend'

  END

  SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  IF @typus=2 BEGIN

  RETURN '2,Allerheiligen'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6 BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7 BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  SET @datum = CONVERT(DATETIME,'25.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,1. Weihnachtstag'

  END

  SET @datum = CONVERT(DATETIME,'26.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  RETURN '1,2. Weihnachtstag'

  END

  IF DATEPART(weekday,@cdatum)=6 BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7 BEGIN

  RETURN '0,Sonntag'

  END

  END

  RETURN '9,Arbeitstag'

  END

• Phil Parkin

  SSC Guru

  Points: 246956

  More actions

  February 1, 2016 at 8:05 am

  #1855689

  Here is a formatted version.

  create FUNCTION isArbeitstag ( @cDatum DATETIME

  , @typus AS INTEGER )

  /*

  @cdatum= Datum, das kontrolliert werden soll

  @typus = 0 (nur Wochenenden)

  @typus = 1 (Bundesweite Feiertage und Wochenenden)

  @typus = 2 (Alle Feiertage und Wochenenden)

  Beispielhafte Übergabewerte:

  ----------------------------

  '1,Neujahr'

  '2,Heilige drei Könige'

  '0,Samstag'

  '0,Sonntag'

  '9,Arbeitstag'

  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  Bitte die Einstellungen von "Datepart" kontrollieren

  Für Sonntage muss datepart(weekday,@cdatum)=7 gelten

  Wenn dies nicht zutrifft, dann müssen alle Aufrufe

  dieser Funktion entsprechend angepasst werden

  (Auch beim Buß- und Bettag)

  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  */

  RETURNS VARCHAR(27)

  AS

  BEGIN

  DECLARE @m AS INTEGER

  DECLARE @N AS INTEGER

  DECLARE @a AS INTEGER

  DECLARE @b-2 AS INTEGER

  DECLARE @C AS INTEGER

  DECLARE @d AS INTEGER

  DECLARE @e AS INTEGER

  DECLARE @tag AS INTEGER

  DECLARE @mon AS INTEGER

  DECLARE @xJahr AS INTEGER

  DECLARE @TagDerWoche AS INTEGER

  DECLARE @strJahr AS VARCHAR(4)

  DECLARE @Datum AS DATETIME

  DECLARE @datOsterSonntag AS DATETIME

  IF @typus=0

  BEGIN -- nur Wochenenden

  IF DATEPART(weekday,@cdatum)=6

  BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7

  BEGIN

  RETURN '0,Sonntag'

  END

  END

  ELSE BEGIN -- Feiertage und Wochenenden

  SET @xJahr=YEAR(@cDatum)

  SET @strJahr=CONVERT(VARCHAR(4),@xJahr)

  SET @m = 23

  SET @N = 3

  IF @xJahr > 1799

  BEGIN

  SET @N = 4

  END

  IF @xJahr > 1899

  BEGIN

  SET @N = @N + 1

  SET @m = 24

  END

  IF @xJahr > 2099

  BEGIN

  SET @N = @N + 1

  END

  SET @a = @xJahr % 19

  SET @b-2 = @xJahr % 4

  SET @C = @xJahr % 7

  SET @d = ((@a * 19) + @m) % 30

  SET @e = ((@B * 2) + (@C * 4) + (@d * 6) + @N) % 7

  SET @tag = @d + @e + 22

  IF @tag > 31

  BEGIN

  SET @tag = @tag - 31

  IF (@Tag = 26) Or ((@Tag = 25) And (@d = 28) And (@a > 10))

  BEGIN

  SET @tag = @tag - 7

  END

  SET @mon = 4

  END

  ELSE BEGIN

  SET @mon = 3

  END

  SET @datum = CONVERT(DATETIME,CONVERT(VARCHAR(2),@tag) +'.'

  + CONVERT(VARCHAR(2),@mon) + '.' + @strJahr)

  --Ostersonntag

  SET @datOsterSonntag = @Datum

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Ostersonntag'

  END

  --Karfreitag

  SET @datum = DATEADD(DAY,-2,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Karfreitag'

  END

  --Ostermontag

  SET @datum = DATEADD(DAY,1,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Ostermontag'

  END

  --Pfingstsonntag

  SET @datum = DATEADD(DAY,49,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Pfingstsonntag'

  END

  --Pfingstmontag

  SET @datum = DATEADD(DAY,50,@datOsterSonntag)

  IF @cdatum=@datum

  BEGIN

  RETURN '1,Pfingstmontag'

  END

  --Chr.Himmelfahrt

  SET @datum = DATEADD(DAY,39,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Christi Himmelfahrt'

  END

  --Fronleichnam

  SET @datum = DATEADD(DAY,60,@datOsterSonntag)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  IF @typus=2

  BEGIN

  RETURN '2,Fronleichnam'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6

  BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7

  BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  --Buß+Bettag

  SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

  SET @TagDerWoche = 0

  WHILE @TagDerWoche <>3

  BEGIN

  SET @TagDerWoche=DATEPART(weekday,@datum)

  IF @TagderWoche=3

  BEGIN

  SET @datum=DATEADD(DAY,14,@datum)

  END

  ELSE BEGIN

  SET @datum=DATEADD(DAY,1,@datum)

  END

  END

  IF DAY(@datum) < 16

  BEGIN

  SET @datum = DATEADD(DAY,7,@datum)

  END

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  IF @typus=2

  BEGIN

  RETURN '2,Buß- und Bettag'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6

  BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7

  BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  -- feste Feiertage

  SET @datum = CONVERT(DATETIME,'01.01.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Neujahr'

  END

  --SET @datum = CONVERT(DATETIME,'06.01.' + @strJahr)

  --IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

  -- IF @typus=2 BEGIN

  -- RETURN '2,Heilige drei Könige'

  -- END

  -- ELSE BEGIN

  -- IF DATEPART(weekday,@cdatum)=6 BEGIN

  -- RETURN '0,Samstag'

  -- END

  -- IF DATEPART(weekday,@cdatum)=7 BEGIN

  -- RETURN '0,Sonntag'

  -- END

  -- END

  --END

  SET @datum = CONVERT(DATETIME,'01.05.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Maifeiertag'

  END

  SET @datum = CONVERT(DATETIME,'03.10.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Tag der deutschen Einheit'

  END

  SET @datum = CONVERT(DATETIME,'15.08.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  IF @typus=2

  BEGIN

  RETURN '2,Mariä Himmelfahrt'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6

  BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7

  BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  SET @datum = CONVERT(DATETIME,'31.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Silvester'

  END

  SET @datum = CONVERT(DATETIME,'24.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,Heiligabend'

  END

  SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  IF @typus=2

  BEGIN

  RETURN '2,Allerheiligen'

  END

  ELSE BEGIN

  IF DATEPART(weekday,@cdatum)=6

  BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7

  BEGIN

  RETURN '0,Sonntag'

  END

  END

  END

  SET @datum = CONVERT(DATETIME,'25.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,1. Weihnachtstag'

  END

  SET @datum = CONVERT(DATETIME,'26.12.' + @strJahr)

  IF DATEDIFF(DAY,@cdatum,@datum) = 0

  BEGIN

  RETURN '1,2. Weihnachtstag'

  END

  IF DATEPART(weekday,@cdatum)=6

  BEGIN

  RETURN '0,Samstag'

  END

  IF DATEPART(weekday,@cdatum)=7

  BEGIN

  RETURN '0,Sonntag'

  END

  END

  RETURN '9,Arbeitstag'

  END

  What's better than WHILE? No WHILE at all (ie, get rid of loops if at all possible)

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 1, 2016 at 8:18 am

  #1855693

  It could be as simple as having a calendar table with holidays and workdays included. Then the query would come like this:

  SELECT COUNT(*)

  FROM Calendar

  WHERE cal_date BETWEEN @start AND @End

  AND IsWorkDay = 1

  --Maybe IsHoliday = 0 depending on logic

  Of course, I can't give much advice as my German goes as far as "guten tag".

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  February 1, 2016 at 8:22 am

  #1855695

  orenk (2/1/2016)

  ---

  Hey, here the second function.

  It´s just for reporting, so the performance is not my top point 🙂

  Whats better then while?

  Ouch 😉

  When you can make something run better, why would you ever choose to not do so? Take a look at Jeff Moden's article "The 'Numbers' or 'Tally' Table: What it is and how it replaces a loop" (http://www.sqlservercentral.com/articles/T-SQL/62867/)[/url].

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Jason-299789

  SSC-Insane

  Points: 21601

  More actions

  February 1, 2016 at 8:31 am

  #1855700

  One thing I noticed is when you create the date with this line of code

  SET @datum = CONVERT(DATETIME,CONVERT(VARCHAR(2),@tag) +'.'

  + CONVERT(VARCHAR(2),@mon) + '.' + @strJahr)

  you could use the DateFromParts function that was new in 2012

  SET @datum=DateFromParts(@tag,@mon,@xJahr)

  _________________________________________________________________________
  SSC Guide to Posting and Best Practices