SQL Query HELP!!!!!

All Replies

• Tuesday, February 21, 2012 11:27 AM

   

   
  

  0

  Try this.  I use datepart to avoid localised daynames, so have to make sure that I know what the start of the week is.

  declare @YourTable table (Createdon datetime)
  
  insert into @YourTable select '6 Feb 2012 08:00'
  insert into @YourTable select '6 Feb 2012 19:00'
  insert into @YourTable select '10 Feb 2012 10:00'
  insert into @YourTable select '10 Feb 2012 19:00'
  insert into @YourTable select '11 Feb 2012 09:00'
  
  set datefirst 7 --Sets Sunday(7) to be first day of week
  
  select 
  	Createdon,
  	case 
  		--Friday after 6pm
  		when cast(Createdon as time) > '18:00:00' and datepart(dw, Createdon) = 6
  			then cast(cast(Createdon+3 as date) as datetime) + '09:00:00' 
  		--Saturday
  		when datepart(dw, Createdon) = 7
  			then cast(cast(Createdon+2 as date) as datetime) + '09:00:00' 
  		--Sunday
  		when datepart(dw, Createdon) = 1
  			then cast(cast(Createdon+1 as date) as datetime) + '09:00:00'
  		--other days after 6pm
  		when cast(Createdon as time) > '18:00:00' --after 6pm
  			then cast(cast(Createdon+1 as date) as datetime) + '09:00:00' 
  		--the rest
  		else Createdon
  		
  	end
  from @YourTable
  

  
  

  • Reply
  • Quote

   
• Tuesday, February 21, 2012 11:38 AM

   

   
  

  0

  SELECT *
  INTO #tmpData
  FROM (
  	SELECT GETDATE() as MyDate
  	UNION SELECT DateAdd(hour, -2000, GETDATE()) -- LongTimeAgo
  	UNION SELECT '20120217 08:00' -- Friday
  	UNION SELECT '20120218 10:00' -- Saturday
  	UNION SELECT '20120219 10:00' -- Sunday
  	UNION SELECT '20120220 08:00' -- Monday
  	UNION SELECT '20120220 10:00' -- Monday
  ) Data
  
  ALTER TABLE #tmpData ADD ID int IDENTITY (1,1)
  
  SELECT * FROM #tmpData ORDER BY 1
  
  UPDATE #tmpData SET MyDate = DATEADD(HOUR,9, CONVERT(datetime, DATEADD(day,1,CONVERT (date, MyDate))))
  FROM #tmpData
  WHERE DateDiff(hour, MyDate, getdate()) > 1800
  
  UPDATE #tmpData SET MyDate = DATEADD(HOUR,9, CONVERT(datetime, DATEADD(day,CASE WHEN DATEPART(weekday,MyDate) in (6,7) THEN 8 ELSE 1 END - DATEPART(weekday,MyDate),CONVERT (date, MyDate))))
  WHERE DATEPART(weekday,MyDate) in (6,7) -- Saturday or Sunday
   OR ( DATEPART(weekday,MyDate) in (1) AND DatePart(Hour,MyDate) < 9) -- Or Monday before 09:00
  
  SELECT * FROM #tmpData
  
  DROP TABLE #tmpData
  

  Check if Sunday is 7 ...
  
  • Reply
  • Quote

   
• Monday, February 27, 2012 12:01 PM

  Moderator

   

   
  

  0

  >WHERE DATEPART(weekday,MyDate) in (6,7) -- Saturday or Sunday
  

  That may not work due to DATEFIRST setting:

  SET DATEFIRST 7;
  SELECT @@DATEFIRST AS [DateFirst] 
      ,CAST('1999-1-3' AS datetime2) AS SelectDate
      ,DATEPART(weekday, '1999-1-3') AS DayOfWeek
      ,DATENAME(dw,'1999-1-3') AS WeekDay;
  /*
  DateFirst	SelectDate	DayOfWeek	WeekDay
  7	1999-01-03 00:00:00.0000000	1	Sunday  */
      
  SET DATEFIRST 1;
  SELECT @@DATEFIRST AS [DateFirst] 
      ,CAST('1999-1-3' AS datetime2) AS SelectDate
      ,DATEPART(weekday, '1999-1-3') AS DayOfWeek
      ,DATENAME(dw,'1999-1-3') AS WeekDay;
  /*
  DateFirst	SelectDate	DayOfWeek	WeekDay
  1	1999-01-03 00:00:00.0000000	7	Sunday */

  It is safer to use DATENAME.

  Related article:

  http://www.sqlusa.com/bestpractices2008/between-dates/

  ---

  Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

  • Reply
  • Quote

   
• Monday, February 27, 2012 12:18 PM

   

   
  

  0

  SQLUSA,

  It has got to be safer to use DATEPART, after explicitly checking or setting DATEFIRST, as you shouldn't assume that the language is English.
  

  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Friday, March 09, 2012 12:29 PM
  •  
  • Reply
  • Quote

   
• Monday, February 27, 2012 1:19 PM

   

   
  

  0

   

  
    declare @dt datetime ,@dd datetime  , @final_dt datetime
    select @dd = dateadd( hh ,10 ,GETDATE())
    select @dt =  convert(datetime , convert(varchar(20) , @dd , 101)   )  
  
  
  
  declare @d1 datetime, @d2 datetime  ,@d4 datetime 
  
  
  select @d2  = DATEADD(HH ,9 ,@dt )
  select @d4  = DATEADD(HH ,18 ,@dt )
  
  
  
  
  if  @dd between @d2 and    @d4
  
  
  begin
  select @final_dt = @dd
  end
  
  
  else 
  begin
  select   @final_dt = dateadd( dd ,1 , @d2 )
  end
  
  
  select  @final_dt
  
  
  ----------

  hope this will help you
  
  
  
  

  • Reply
  • Quote

   
• Monday, February 27, 2012 3:58 PM

   

   
  

  0

  >> I have a field [sic] as “created_on” which is a datetime field [sic] (2012-02-06 08:00:00.000). The created on field [sic] shows the time and the date the record [sic] was created on the table <<

  
  

  Several FUNDAMENTAL errors here. Columns are not fields. Rows are not records. Tables are not files. Logical data does not have to be physical.  You do not yet think in RDBMS. But the immediate error will get you in trouble. You never put audit meta data in the table being audited. Well, maybe if you want to go to jail :) Think about what happens to the audit trail when someone deletes the row. Where is the required “second signature” on the audit?

  
  

  This is why we use third party audit tools that collect data from the log file. And why the log is on a PHYSICALLY separate storage. 

  
  

  >> All records [sic] that were created after 1800 hours on a particular day should add a day and the time should be set to 09:00. <<

  
  

  So you want to falsify the audit trail? And it is so easy to do with this design, isn’t it? Which is another reason it is illegal; a single user with full privileges on the table can change both the data and the audit without a second signature.

  
  

  >> Can anybody help with this query <<

  
  

  Your lawyer, perhaps? :)

  ---

  --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Friday, March 09, 2012 12:29 PM
  •  
  • Reply
  • Quote

   
• Saturday, March 03, 2012 8:47 PM

  Moderator

   

   
  

  0

  SQLUSA,

  It has got to be safer to use DATEPART, after explicitly checking or setting DATEFIRST, as you shouldn't assume that the language is English.
  

  That is the choice you have to make. Here in the US, we do use English.
  

  Otherwise, you have to deal with DATEFIRST:

  http://sqlcode.blogspot.com/2007/07/calculate-number-of-business-days.html

  ---

  Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Friday, March 09, 2012 12:29 PM
  •  
  • Reply
  • Quote