Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 convert int int to date urgent please Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 20, 2013 9:25 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 6:50 AM Points: 6,277, Visits: 12,093
 You might need an input style for CONVERT:`;WITH Sampledata AS ( SELECT lastrundate = CAST(20130620 AS INT))SELECT lastrundate_INT = lastrundate, lastrundate_CHAR8 = CAST(lastrundate AS CHAR(8)), lastrundate_DATE = CONVERT(DATE, CAST(lastrundate AS CHAR(8)),112)FROM Sampledata` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1465776
 Posted Thursday, June 20, 2013 9:27 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 9:23 AM Points: 2,358, Visits: 7,145
 ashwinboinala (6/20/2013)thanks for u r time getting below error Msg 208, Level 16, State 0, Line 10Invalid object name '#validdatesasints'.For you to have got that error, you must've not run Howard's code correctly.Try this: -`SELECT *FROM [Monitor].[dbo].[Job_Monitor]WHERE LASTRUNDATE NOT IN ( SELECT TOP 73050 CONVERT(INT, CONVERT(VARCHAR, DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '19000101'), 112)) FROM master.sys.columns a CROSS JOIN master.sys.columns b )AND LASTRUNDATE IS NOT NULL;`That will show you the rows that you can't convert to dates. Not a DBA, just trying to learnFor better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockLinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1465778
 Posted Thursday, June 20, 2013 9:27 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 6:50 AM Points: 6,277, Visits: 12,093
 ashwinboinala (6/20/2013)i am getting this value from sysjobhistroy table from msdb there its in int format if u can give me solution where i can filter that table thats fine`SELECT run_date, CONVERT(DATE, CAST(run_date AS CHAR(8)),112) FROM msdb.dbo.sysjobhistory` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1465780

 Permissions

 Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.