Help needed in converting an int datatype to MM/DD/YYYY and converting another int to HH:MM PM

/*
--Results
name NextRunDate
syspolicy_purge_history 2012-08-28 02:00:00.000
Email Reminders 2012-08-28 07:00:00.000
Last EDI Exported Status 2012-09-02 09:00:00.000
*/
SELECT
sysjobs.name ,
CONVERT(datetime,
STUFF(STUFF(CONVERT(VARCHAR,dbo.sysjobschedules.next_run_date),7,0,'-'),5,0,'-')
+ ' '
+ stuff(stuff(Right('0' + cast(dbo.sysjobschedules.next_run_time as varchar(6)),6),3,0,':'),6,0,':')
) AS NextRunDate
FROM dbo.sysjobs
INNER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
Order BY dbo.sysjobschedules.next_run_date, dbo.sysjobschedules.next_run_time ASC

CELKO (8/29/2012)

>> When running the script below I noticed that the two result fields [sic: columns are not fields] are of data type INTEGER. I would like to convert the value of the next_run_date to the format of MM/DD/YYYY and the value of next_run_time to the format of HH:MM AM or PM.<<

Your problem is that you have no idea how RDBMS and SQL work. We have abstract data type in SQL; how they are stored in the databases has nothing whatsoever to do with how they are displayed in the presentation layers.

Furthermore, the only display format allowed in ANSI/ISO Standard is 'YYYY-MM-DD HH:MM:SS.SSS', not the local dialect you think you should have. That was how COBOL worked in the 1950's when there were no temporal data types. All temporal stuff has to be done with procedural code and strings. Just like you are doing now! You also used context sensitive COBOL field names instead of ISO-11179 data element names.

We have a timestamps data type, which MS calls DATETIME2(n), so you should not split out the DATE and TIME fields (yes, this is the only place ANSI used 'field'; parts of temporal values).

These are huge conceptual error and not a little mistake. Let's make guesses and see if we can re-write this pseudo-COBOL to SQL.

SELECT J.name, JS.run_timestamp
FROM DBO.SysJobSchedules AS JS, DBO.SysJobs AS J
WHERE J.job_id = JS.job_id;

>> I have the following script that appears to work correctly in converting an INTEGER to standard time format end with am or pm. <<

This is a stinking kludge and should not exist. Standard time does not have the old AM/PM. This will improve the quality of data in your enterprise by orders of magnitude.

Now, create two look up tables, load the dates with 50, 10 or whatever years, load the times with a full day. Do the look ups and then add the date and time together.

Create TABLE Stupid_Integer_Dates
(cal_date DATE NOT NULL,
int_date INTEGER NOT NULL PRIMARY KEY);

Create TABLE Stupid_Integer_Times
(cal_time TIME NOT NULL,
int_time INTEGER NOT NULL PRIMARY KEY);

Finally, find the moron that put a timestamp in integers and kill him. I suggest burning him at the stake with his COBOL manuals Smile