March 19, 2009 at 10:28 am
I have the following SP which outputs the results to a table.
The date column in the source table is 20090319 (INT).
I want to convert it to 2009-03-19 when it populates my new table:
-- List of jobs
DECLARE @jobs TABLE (
job_id uniqueidentifier,
originating_server_id INT,
[name] varchar(128),
enabled tinyint,
description varchar(512),
start_step_id int,
category_id int,
own varbinary(85),
notify_level_eventloger_sid int,
notify_level_email int,
notify_level_netsend int,
notify_level_page int,
notify_email_operator_id int,
notify_netsend_operator_id int,
notify_page_operator_id int,
delete_level int,
date_created datetime,
date_modified datetime,
version_number int
)
-- Job history inported from ssis
DECLARE @DealbookJobHistory TABLE
(
instance_id int,
job_id uniqueidentifier,
step_id int,
step_name nvarchar(128),
sql_message_id int,
sql_severity int,
[message] nvarchar(1024),
run_status int,
run_date int,
run_time int,
run_duration int,
operator_id_emailed int,
operator_id_netsent int,
operator_id_paged int,
retries_attempted int,
server nvarchar(128),
curr_date DATETIME
)
-- New tbale to hold results of failed jobs
DECLARE @Jobs_failed TABLE
(
[name] nvarchar (128),
[message] nvarchar(1024),
run_date int
)
INSERT INTO @Jobs_failed
select j.name, jh.message, jh.run_date
from jobs j
join DealbookJobHistory jh
on j.job_id = jh.job_id
WHERE jh.message like '%Failed%'
and jh.step_id = 1 -- Step one of job i.e. will only show error
-- Run the
SELECT * FROM @Jobs_failed
March 19, 2009 at 10:31 am
What table and field is this date going into?
March 19, 2009 at 10:36 am
Hi Lynn,
Its as the in of the SP:
INSERT INTO @Jobs_failed
select j.name, jh.message, jh.run_date
from jobs j
join DealbookJobHistory jh
on j.job_id = jh.job_id
WHERE jh.message like '%Failed%'
and jh.step_id = 1
March 19, 2009 at 10:40 am
that field is defined as an INT, so you can't have it in this format: '2009-03-19' are we missing something here?
Oh and you should be starting dates in a datetime, not in an int...
Oh and for formatting check CONVERT in BOL:
e.g
SELECT CONVERT(DATETIME,'20090319')
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 10:49 am
I have to agree with Chris above.
March 19, 2009 at 10:51 am
Right slightly lost now.....
Does this mean I have to go back to the source change the import type from INT to Date?
The Source data is coming from the system database MSDB tables:
sysjobhistory import to DealbookJobHistory
sysjobs import to Jobs
this process is done by an SSIS package....
March 19, 2009 at 10:54 am
not sure what you saying but:
this table:
DECLARE @Jobs_failed TABLE
(
[name] nvarchar (128),
[message] nvarchar(1024),
run_date int
)
has Run_date defined as an int, which makes it impossible to start anything that looks like this '2009-03-19'
I would suggest that you do a transaformation in your SSIS package and sorted the date as a date 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 11:01 am
If in your table you declare rundate as a datetime and just insert into to from sysjobhsitory run date the conversation should happen automatically.
here is an example of the conversion:
DECLARE @date DATETIME
SET @date = '20090319'
SELECT @date
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 11:07 am
Christopher Stobbs (3/19/2009)
If in your table you declare rundate as a datetime and just insert into to from sysjobhsitory run date the conversation should happen automatically.here is an example of the conversion:
DECLARE @date DATETIME
SET @date = '20090319'
SELECT @date
You have to explicitly convert the INT value to a datetime or else you will get an overflow.
March 19, 2009 at 11:10 am
are you sure?
My script when run seems to work!
could that be became of the format settings on the server?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 11:15 am
Christopher Stobbs (3/19/2009)
are you sure?My script when run seems to work!
could that be became of the format settings on the server?
Yes I am. Try taking the single quotes off your date value (which is a string as it is).
March 19, 2009 at 11:17 am
:hehe:
Sorry I'm an idiot 😀
obviously a varchar need cofffffffeeeeee
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 11:20 am
Thansk, you guys have been very helpful.
the problem is, the source table sysjobhsitory has the run_date set as INT.
In my SSIS I converted it to a string first, then into a date, then inserted this into my table.
this keeps failing....
Any other way?
March 19, 2009 at 11:22 am
why does it fail?
What is the error?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 11:30 am
A Little Help Please (3/19/2009)
Thansk, you guys have been very helpful.the problem is, the source table sysjobhsitory has the run_date set as INT.
In my SSIS I converted it to a string first, then into a date, then inserted this into my table.
this keeps failing....
Any other way?
Why are you converting it to a string just to convert it back to an int?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply