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 7:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:54 PM
Points: 29, Visits: 321
/****** 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/20
error :::

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
Post #1465681
Posted Thursday, June 20, 2013 7:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 1,207, Visits: 9,333
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.
Post #1465688
Posted Thursday, June 20, 2013 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:54 PM
Points: 29, Visits: 321
i have 0 in that so do i need to write case
Post #1465694
Posted Thursday, June 20, 2013 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:54 PM
Points: 29, Visits: 321

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

Post #1465700
Posted Thursday, June 20, 2013 7:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 1,207, Visits: 9,333
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

Post #1465705
Posted Thursday, June 20, 2013 7:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:54 PM
Points: 29, Visits: 321
thanks for u r time getting below error
Msg 208, Level 16, State 0, Line 10
Invalid object name '#validdatesasints'.
Post #1465717
Posted Thursday, June 20, 2013 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:54 PM
Points: 29, Visits: 321
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
Post #1465719
Posted Thursday, June 20, 2013 8:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
Would this give you a clue?
SELECT CONVERT( date, CONVERT( char(8), NULLIF( Someintdate, 0)))
FROM (VALUES(20130601), (0), (20130515))x(Someintdate)




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1465721
Posted Thursday, June 20, 2013 8:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 860, Visits: 2,323
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
Post #1465741
Posted Thursday, June 20, 2013 8:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 1,207, Visits: 9,333
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.
Post #1465750
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse