Monday, June 24, 2013

[T-SQL] help with datediff

[T-SQL] help with datediff

help with datediff

Posted: 23 Jun 2013 11:20 PM PDT

hello theregot 2 time fields *start date*end datethe format of these columns is [b]hhmmss[u][/u][/b]i want to calculate the duration . (end date-start date)could i do this with datdiff or any other function?pay attention that a situation like this can be happened :*start date 235641*end date 001612so if you subtract ( 001612-235641) you got a negative number , in that case, i think you need to add 24 hours...thank you

Delete Operation on table with 60 Million records

Posted: 23 Jun 2013 05:41 PM PDT

Hi ,[b]Environment: SQL Server 2008 Enterprise Edition with sufficient hardware.[/b]I have a table with > 60 Million records on which I am performing delete operation using a stored proc. I am deleting the records by batch using TOP clause by joining with a #temp table (with no indexes). I have 1 Cluster Index and 3 non-cluseter index on this main table. Issue is the block itself is taking 95% of the stored proc's total execution. Below is the query that I am using to delete records. WHILE 1 = 1 BEGIN DELETE TOP(10000) t FROM EXPORT.SE_VARCS_DBF t -- Main table with > 60 Million Recs INNER JOIN #PatchIdentifiers p -- Temp Table ON t.M_SCNTYPE = p.M_SCNTYPE AND t.M_FAMILY0 = p.M_FAMILY0 AND t.M_MLABEL0 = p.M_MLABEL0 AND t.M_SLABEL0 = p.M_SLABEL0 WHERE t.ContainerID = @TargetContainerID IF @@ROWCOUNT = 0 BREAK ENDCould you please help me out with the advice.Thanks,Arjun.

GetDate() 5 Years ago without time formated 'YYYY-MM-DD'

Posted: 23 Jun 2013 03:40 AM PDT

I'm trying to restrict a query to go back five years ago.I have the following query which is not in the format that I need.[code="plain"]SELECT convert(varchar(11), DateAdd(yy, -5, GetDate()))[/code]Returns:[code="plain"]Jun 23 2008[/code]What I need is the following:[code="plain"]'2008-06-23'[/code]Thank you.

Need help with the code

Posted: 23 Jun 2013 02:39 PM PDT

Hi,When I am running the below stored procedure through a job I am getting the below error message.Executed as user: NT AUTHORITY\SYSTEM. Conversion failed when converting the varchar value '2314K' to data type int. [SQLSTATE 22018] (Error 245). The step failed.Can you please help me in fixing the above mentioned error.Stored procedure code:USE [Nice]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE PROCEDURE [dbo].[SP_Test1] ASBEGIN BULK INSERT Table_Test FROM 'C:\Test\Test.csv' WITH ( FIELDTERMINATOR = '","', ROWTERMINATOR = '\"' ) INSERT INTO [dbo].[Table_Test1] ([emp_code] ,[first_name] ,[last_name] ,[middle_name] ,[initials] ,[pay_code] ,[pay_rate] ,[paid_by] ,[birth_date] ,[gender] ,[ssn] ,[street_addr1] ,[street_addr2] ,[city] ,[state] ,[zip] ,[phone1] ,[phone2] ,[hire_date] ,[sen_date] ,[dept_date] ,[term_date] ,[status] ,[wrk_status] ,[hrs_week] ,[homedept_id] ,[Test_Code] ,[user_name] ,[Testing_Code2_1] ,[Testing_Code2_2] ,[Testing_Code2_3] ,[Testing_Code2_4] ,[Testing_Code2_5]) SELECT left([col3],6) ,[col5] ,[col4] ,null ,null ,null ,0 ,[col21] ,null ,'M' ,right([col26],4) ,null ,null ,null ,null ,null ,[col10] ,[col11] ,[col6] ,[col7] ,null ,[col8] ,CASE [col22] WHEN 'B' THEN 'U' WHEN 'U' THEN 'U' WHEN 'T' then 'C' ELSE 'C' END ,[col9] ,null ,-1 ,[col16] ,left([col25],len([col25])-len('NSTES')) ,null ,[col20] ,[col12] ,[col14] ,left([col15],30) FROM [dbo].[Table_Test]update Table_Test1 set Test_Code=1001 where Test_Code=1000ENDThank You,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog