convert int int to date

Question

Post reply

convert int int to date

ak08

Mr or Mrs. 500

Points: 543
More actions
June 20, 2013 at 7:18 am

#278942

/****** 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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

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.

ak08 Mr or Mrs. 500 Points: 543 More actions · Answer 2

ak08

Mr or Mrs. 500

Points: 543

June 20, 2013 at 7:34 am

#1625906

i have 0 in that so do i need to write case

ak08 Mr or Mrs. 500 Points: 543 More actions · Answer 3

/****** 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.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

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

ak08 Mr or Mrs. 500 Points: 543 More actions · Answer 5

thanks for u r time getting below error

Msg 208, Level 16, State 0, Line 10

Invalid object name '#validdatesasints'.

ak08 Mr or Mrs. 500 Points: 543 More actions · Answer 6

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Would this give you a clue?

SELECT CONVERT( date, CONVERT( char(8), NULLIF( Someintdate, 0)))

FROM (VALUES(20130601), (0), (20130515))x(Someintdate)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

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

HowardW One Orange Chip Points: 29109 More actions · Answer 9

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 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.

Converting NULL values won't fail. There must be non-null int values that are not convertible.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 11

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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

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