June 20, 2013 at 7:18 am
/****** Script for SelectTopNRows command from SSMS ******/SELECT TOP 1000 [ServerName] ,[IsExisting] ,[IsEnabled] ,[IsScheduleEnabled] ,[NAME] ,CONVERT(date, CONVERT(VARchar(18), LASTRUNDATE)) ,[Date] FROM [Monitor].[dbo].[Job_Monitor] plz need it urgent lastrundate is int (20130620)format need to convert to date 2013/06/20error :::Msg 241, Level 16, State 1, Line 4Conversion failed when converting date and/or time from character string.
June 20, 2013 at 7:25 am
Nothing wrong with the syntax, so you must have values in the column that aren't a valid date value.
Look at the following examples:
DECLARE @lastrundate INT='20130228'
SELECT CONVERT(date, CONVERT(VARchar(18), @lastrundate))
--success
DECLARE @lastrundate INT='20130229'
SELECT CONVERT(date, CONVERT(VARchar(18), @lastrundate))
--failure
You'll need to find the invalid date values before converting.
June 20, 2013 at 7:34 am
i have 0 in that so do i need to write case
June 20, 2013 at 7:39 am
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [ServerName]
,[IsExisting]
,[IsEnabled]
,[IsScheduleEnabled]
,[NAME]
,case when CONVERT(date, CONVERT(VARchar(18), 'LASTRUNDATE'))='0' then NULL else CONVERT(date, CONVERT(VARchar(18), LASTRUNDATE)) end as lastrundate
,[Date]
FROM [Monitor].[dbo].[Job_Monitor]
plz help
error
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
June 20, 2013 at 7:45 am
You need to find the rows that don't convert. Try this out to find the offending rows:
CREATE TABLE #validdatesasints ( date INT PRIMARY KEY )
DECLARE @ValidDateStartValue DATE= '19000101' , --technically speaking you can go to the year 0001, but I wouldn't bother and it makes the lookup table v large
@ValidDateEndValue DATE= '21000101'
--same as above, can go to year 9999, but seems silly
DECLARE @Top INT= DATEDIFF(DAY, @ValidDateStartValue, @ValidDateEndValue) + 1
INSERT INTO #validdatesasints
( date
)
SELECT TOP ( @Top )
CONVERT(INT, CONVERT(VARCHAR, DATEADD(day,
ROW_NUMBER() OVER ( ORDER BY ( SELECT
NULL
) ) - 1,
@ValidDateStartValue), 112))
FROM master.sys.columns a ,
master.sys.columns b
--find offending rows
SELECT *
FROM [Monitor].[dbo].[Job_Monitor]
WHERE LASTRUNDATE NOT IN ( SELECT date
FROM #validdatesasints )
AND LASTRUNDATE IS NOT NULL
June 20, 2013 at 7:57 am
thanks for u r time getting below error
Msg 208, Level 16, State 0, Line 10
Invalid object name '#validdatesasints'.
June 20, 2013 at 7:59 am
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
June 20, 2013 at 8:04 am
Would this give you a clue?
SELECT CONVERT( date, CONVERT( char(8), NULLIF( Someintdate, 0)))
FROM (VALUES(20130601), (0), (20130515))x(Someintdate)
June 20, 2013 at 8:41 am
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
Wouldnt the LastRunDate be NULL if the job has never been run? which is where you problem maybe.
As a test wrap an ISNULL around the LASTRUNDATE field and set it to 1 then run the query.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 20, 2013 at 8:54 am
Jason-299789 (6/20/2013)
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 fineWouldnt the LastRunDate be NULL if the job has never been run? which is where you problem maybe.
As a test wrap an ISNULL around the LASTRUNDATE field and set it to 1 then run the query.
Converting NULL values won't fail. There must be non-null int values that are not convertible.
June 20, 2013 at 9:25 am
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
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
June 20, 2013 at 9:27 am
ashwinboinala (6/20/2013)
thanks for u r time getting below errorMsg 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.
June 20, 2013 at 9:27 am
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
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy