sql 2008 upgrade - datetime Settings

When installed a new instance of sql server, or upgrade to sql 2008. Check the following.


1. SQL Collation should be the same.

2. The date and time setting should be the same, if it English (United states)

3. In the Regional and Language settings also in the administrative tab  --- click on System locale and change to English United States.

4.

Code to find No of lines in SP

SELECT t.sp_name AS 'Stored Procedure',


SUM(t.lines_of_code) - 1 AS 'No of Lines in code ',

t.type_desc AS 'Object Description'

FROM

(

SELECT o.name AS sp_name,

(LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), ''))) AS lines_of_code,

CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'

WHEN o.xtype IN ('FN', 'IF', 'TF') THEN 'Function'

END AS type_desc

FROM sysobjects o

INNER JOIN syscomments c

ON c.id = o.id

WHERE o.xtype IN ('P', 'FN', 'IF', 'TF')

AND o.category = 0

AND o.name

NOT IN ('fn_diagramobjects', 'sp_alterdiagram',

'sp_creatediagram', 'sp_dropdiagram',

'sp_helpdiagramdefinition', 'sp_helpdiagrams',

'sp_renamediagram', 'sp_upgraddiagrams',

'sysdiagrams')

) t

GROUP BY t.sp_name, t.type_desc

ORDER BY 1

Drop temp table

IF EXISTS


(

SELECT *

FROM tempdb.dbo.sysobjects

WHERE ID = OBJECT_ID(N'tempdb..#temp1')

)

BEGIN

drop table #temp1

END

calculating time difference

--*********************************************************************************


--part 1

--*********************************************************************************

DROP TABLE #temp1

declare @date datetime

set @date = convert(varchar,getdate()-1,101)

SELECT distinct @date Tdate,emp.EmployeeNo,emp.[EmpName],

(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0) ClockIn,

(select Max([CLKDT])from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0 having

Max([CLKDT])<>(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT

and datediff(day,CLKDT,@date)=0)) Clockout,

EMPREF into #temp1

FROM tblEmployee emp

JOIN [tmsdata].[tmsuser].[tmsclk] [CLK] ON [EMP].[TMSNo] COLLATE DATABASE_DEFAULT = [CLK].[EMPREF] COLLATE DATABASE_DEFAULT

WHERE datediff(day,CLKDT,@date)=0 and emp.iscosting = '1' AND emp.active = 1

ORDER BY emp.[EmpName]

--*********************************************************************************

--part2

--*********************************************************************************

DROP TABLE #temp2

SELECT empref, empname, DATEDIFF(mi, ClockIn, Clockout) AS clockedhrs

INTO #temp2 FROM #temp1





--*********************************************************************************

--part3

--*********************************************************************************

DROP TABLE #TEMP3

--declare @date datetime

--set @date = convert(varchar,getdate()-2,101)

SELECT emp.[EmpName] AS empname,TMSNo,

SUM(DATEDIFF(mi, lt.timein, lt.timeout)) AS scannedhrs

INTO #TEMP3

FROM tblLotCardTracking lt JOIN tblEmployee emp ON lt.EmployeeNo = emp.EmployeeNo

INNER JOIN #temp1 t1 ON t1.EMPREF COLLATE DATABASE_DEFAULT = emp.TMSNo

WHERE lt.TimeIn > @date AND lt.TimeIn < @date + 1 AND emp.iscosting = '1' AND emp.active = 1

GROUP BY emp.[EmpName],TMSNo

--*********************************************************************************

--SELECT * FROM #temp1

--SELECT * FROM #temp3

--*********************************************************************************

--part 4

--*********************************************************************************

SELECT

t2.empname,

CONVERT(CHAR(8),DATEADD(MINUTE,SUM(scannedhrs)% 1440, '00:00'), 108) AS scannedhrs,

CONVERT(CHAR(8),DATEADD(MINUTE,clockedhrs% 1440, '00:00'), 108) AS clockedhrs,

scannedhrs,clockedhrs,

CONVERT(NUMERIC(18,2),(CONVERT(NUMERIC(18,2),scannedhrs)/CONVERT (NUMERIC(18,2),clockedhrs) )*100)

FROM #TEMP3 t3 INNER JOIN #temp2 t2 ON t2.EMPREF COLLATE DATABASE_DEFAULT = t3.TMSNo

group BY t2.empname,clockedhrs,scannedhrs

--*********************************************************************************

END