SQL QUERY with a date

• lucassoberano

  Newbie

  Points: 5

  More actions

  October 19, 2010 at 11:11 pm

  #95085

  Hi!

  I need the following thing:

  I must to a QUERY with SQL SERVER where I select a column that has a DATE. If the week day of that DATE is SUNDAY then I should show FRIDAY. And if it´s SATURDAY is should show it as TUESDAY.

  Which would be the QUERY?

  Thanks!!

  Lucas

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  October 20, 2010 at 5:34 am

  #1239588

  You can use a Look up table for the same

  DECLARE @tblDays TABLE

  (

  DayName VARCHAR(20),

  DisplayName VARCHAR(20)

  )

  DECLARE @tbl_Data_Table TABLE

  (

  DateColumn SMALLDATETIME

  )

  INSERT @tblDays

  SELECT 'Sunday', 'Friday' UNION ALL

  SELECT 'Monday', 'Monday' UNION ALL

  SELECT 'Tuesday', 'Saturday' UNION ALL

  SELECT 'Wednesday', 'Wednesday' UNION ALL

  SELECT 'Thursday', 'Thursday' UNION ALL

  SELECT 'Friday', 'Sunday' UNION ALL

  SELECT 'Saturday', 'Tuesday'

  INSERT @tbl_Data_Table

  SELECT GETDATE() UNION ALL

  SELECT GETDATE() + 1 UNION ALL

  SELECT GETDATE() + 2 UNION ALL

  SELECT GETDATE() + 3 UNION ALL

  SELECT GETDATE() + 4 UNION ALL

  SELECT GETDATE() + 5 UNION ALL

  SELECT GETDATE() + 6

  SELECT DT.DateColumn, D.DayName, D.DisplayName

  FROM @tbl_Data_Table DT

  INNER JOIN @tblDays D ON DATENAME( WEEKDAY, DateColumn ) = DayName

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/