none
SQL Query HELP!!!!!

    Question

  • Hi,

    I have a Field as  “Createdon” which is a datetime field (2012-02-06 08:00:00.000). The Created on field shows the time and the date the record was created on the table

    I want to write a SQL query to do the  2 things

    1.       All Records that were created after 1800 hours on a particular day should add a day and the time should be set to 09:00

    For example if the Created on Value is 2012-02-06 19:00:00.000 then the Output should be is

    2012-02-07 09:00:00.000

    1.       All records that were created after  a Friday and Before Monday 8.59am  should have new date as   Moday 9:00:00

    Can anybody help with this query 

    Tuesday, February 21, 2012 10:33 AM

Answers

  • 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.

    Monday, February 27, 2012 12:18 PM
  • >> 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

    Monday, February 27, 2012 3:58 PM
  • 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

    Saturday, March 03, 2012 8:47 PM

All replies

  • 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
    

    Tuesday, February 21, 2012 11:27 AM
  • 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 ...
    Tuesday, February 21, 2012 11:38 AM
  • >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

    Monday, February 27, 2012 12:01 PM
  • 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.

    Monday, February 27, 2012 12:18 PM
  •  


      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



    Monday, February 27, 2012 1:19 PM
  • >> 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

    Monday, February 27, 2012 3:58 PM
  • 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

    Saturday, March 03, 2012 8:47 PM