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: Today @ 8:07 AM
Points: 29, Visits: 380
/****** 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: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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: Today @ 8:07 AM
Points: 29, Visits: 380
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: Today @ 8:07 AM
Points: 29, Visits: 380

/****** 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: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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: Today @ 8:07 AM
Points: 29, Visits: 380
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: Today @ 8:07 AM
Points: 29, Visits: 380
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 3,901, Visits: 8,834
Would this give you a clue?
SELECT CONVERT( date, CONVERT( char(8), NULLIF( Someintdate, 0)))
FROM (VALUES(20130601), (0), (20130515))x(Someintdate)




Luis C.
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?

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: Yesterday @ 7:43 AM
Points: 916, Visits: 2,502
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: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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