Spanish Day Name Of Week from given date in sql server 2005

• Sarath Vellampalli

  SSCertifiable

  Points: 5822

  More actions

  May 18, 2007 at 12:21 am

  #178991

  How can we find Spanish Day Name Of Week,Spanish Month Name Of year from given date in sql server 2005 from given date in sql server 2005.

  if anybody know this please send me answer.

  Kindest Regards,

  Sarath Vellampalli

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 18, 2007 at 7:49 am

  #707448

  The easiest way is with "datepart" and a case statement, as in the following:

  select

  (case datepart(dw,'5/14/2007') when 1 then 'Lunes' when 2 then 'Martes' when 3 then 'Miercoles' when 4 then 'Thursday' when 5 then 'Friday' when 6 then 'Saturday' when 7 then 'Sunday' end)

  select

  (case datepart(mm,'5/14/2007') when 1 then 'Enero' when 2 then 'Febrero' when 3 then 'Marzo' when 4 then 'April' when 5 then 'May' when 6 then 'June' when 7 then 'July' else 'Rest of Year :-)' end)

  I appoligize if I got any the the "spanish" names wrong, it was all I could find on the internet 🙂

  Another method that avoids the "case" statement, in the event you are doing this repeatedly in many places is to build a "dateName" table similiar to the following:

  create table DateName_ (datepartcd_ char(2), datepartno_ int, datename_ varchar(20))

  populate the table accordingly using the standard code/values from the "datepart" function then you can joing that table in a select statement

  select t1.col1, t1.col2, datename_.datename_ from t1 join datename_ on (datepart(dw,t1.date_) = datename_.datepartno_ and datename_.datepartcd_ = 'dw')

  NOTE: I didn't test that last statement but it's fairly straight forward.

  HTH,

  James.

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  May 18, 2007 at 8:30 am

  #707471

  Here is a quick example that I have tested using a "datename" table.  You would of course want to add keys, indexes, etc to the table, but, well you get the idea.

  BEGIN

  declare @DateName_ table (datepartcd_ char(2), datepartno_ int, datename_ varchar(20))

  declare @t1_ table (col1_ int identity, date_ datetime)

  declare @i_ int

  set @i_ = 1

  while @i_ < 10

  begin

   insert into @t1_ (date_) values (dateadd(d,@i_ * 30,getdate()))

   set @i_ = @i_ + 1

  end

  --I'll stick with english

  insert into @datename_ values ('dw',1,'Monday')

  insert into @datename_ values('dw',2,'Tuesday')

  insert into @datename_ values('dw',3,'Wednesday')

  insert into @datename_ values('dw',4,'Thursday')

  insert into @datename_ values('dw',5,'Friday')

  insert into @datename_ values('dw',6,'Saturday')

  insert into @datename_ values('dw',7,'Sunday')

  insert into @datename_ values('mm',1,'January')

  insert into @datename_ values('mm',2,'Feburary')

  insert into @datename_ values('mm',3,'March')

  insert into @datename_ values('mm',4,'April')

  insert into @datename_ values('mm',5,'May')

  insert into @datename_ values('mm',6,'June')

  insert into @datename_ values('mm',7,'July')

  insert into @datename_ values('mm',8,'August')

  insert into @datename_ values('mm',9,'September')

  insert into @datename_ values('mm',10,'October')

  insert into @datename_ values('mm',11,'November')

  insert into @datename_ values('mm',12,'December')

  select col1_, date_, datename_

  from @t1_ join @datename_ on (datepart(dw,date_) = datepartno_ and datepartcd_ = 'dw')

  select col1_, date_, datename_

  from @t1_ join @datename_ on (datepart(mm,date_) = datepartno_ and datepartcd_ = 'mm')

  END