Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

convert int int to date urgent please Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1465776
Posted Thursday, June 20, 2013 9:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
ashwinboinala (6/20/2013)
thanks for u r time getting below error
Msg 208, Level 16, State 0, Line 10
Invalid 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 learn

For 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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1465778
Posted Thursday, June 20, 2013 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1465780
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse