asked on

Convert a column to date format in a view

Greetings,

I created a view from the sys tables in the msdb database in order to build reports on all schedule database backups. The view works fine and the information looks good but the date field in this view are integer datatypes instead of datetime data types. Can anyone provide a solution to my view listed below that will convert the run_date column from and integer data type column to a date column format. Currently the data in the run_date column looks like this yyyymmdd(20061103), and I would like to have it look like this mm/dd/yyyy(11/03/2006).

Here is the view listed below:
USE [msdb]
GO
/****** Object: View [dbo].[Backup_History] Script Date: 12/13/2006 13:15:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Backup_History]
AS
SELECT dbo.sysjobs.name, dbo.sysjobhistory.step_name, dbo.sysjobhistory.sql_message_id, dbo.sysjobhistory.message, dbo.sysjobhistory.run_status,
dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time, dbo.sysjobhistory.server, dbo.sysjobhistory.run_duration
FROM dbo.sysjobhistory INNER JOIN
dbo.sysjobs ON dbo.sysjobhistory.job_id = dbo.sysjobs.job_id

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "sysjobhistory"
Begin Extent =
Top = 6
Left = 38
Bottom = 121
Right = 218
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "sysjobs"
Begin Extent =
Top = 126
Left = 38
Bottom = 241
Right = 254
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Backup_History'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Backup_History'

Aneesh

SELECT CONVERT(VARCHAR, run_date, 103) run_date

Omega002

ASKER

Aneeshattingal your suggestion did not convert it at all.

SELECT CONVERT(VARCHAR, run_date, 103) run_date

Omega002

ASKER

I used the following syntax listed below and it did not convert the int field run_date to a date format:

SELECT CONVERT(VARCHAR, run_date, 103) run_date,name from dbo.BACKUP_HISTORY

Aneesh

Now i see, run_date is int

SELECT CONVERT(VARCHAR, cast(cast(run_date as varchar) as datetime), 103) run_date

Omega002

ASKER

Still not quite there. Your suggestion placed the date format like this when I used your suggestion:

results from your suggestion made the date format look like this: dd/mm/yyyy instead of mm/dd/yyyy

The query I used:
SELECT CONVERT(VARCHAR, cast(cast(run_date as varchar) as datetime), 103) run_date
from dbo.BACKUP_HISTORY

Omega002

ASKER

Ok this is what I have so far but it is failing. The error on this report indicates that it failed to convert the run_date from an int to a datetime format. The syntax of my t-sql is listed below:

SELECT CONVERT(VARCHAR, cast(cast(run_date as varchar) as datetime), 101) run_date,name,server,run_status
from dbo.BACKUP_HISTORY
where run_date between @startdate and @enddate

Anthony Perkins

Try it this way:

SELECT      CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101) run_date,
      CASE
            WHEN ISDATE(CAST(run_date as char(8))) = 1 THEN CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101)
            ELSE NULL            -- Date is not a valid date (Probably 0 or Null)
      END run_date,
      name,
      server,
      run_status
from      dbo.BACKUP_HISTORY
where      run_date between @startdate and @enddate

Omega002

ASKER

Your syntax returned the following error:

Conversion failed when converting nvarchar value '12/01/2006' to data type int.

Anthony Perkins

You are right, I seem to have left an extra line, it should be:

SELECT
CASE
WHEN ISDATE(CAST(run_date as char(8))) = 1 THEN CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101)
ELSE NULL -- Date is not a valid date (Probably 0 or Null)
END run_date,
name,
server,
run_status
from dbo.BACKUP_HISTORY
where run_date between @startdate and @enddate

Omega002

ASKER

I still get the same error.

Anthony Perkins

It looks like I missed the Where condition. Do you still get the error if you comment out the WHERE clause? As in:

SELECT
CASE
WHEN ISDATE(CAST(run_date as char(8))) = 1 THEN CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101)
ELSE NULL -- Date is not a valid date (Probably 0 or Null)
END run_date,
name,
server,
run_status
from dbo.BACKUP_HISTORY
-- where run_date between @startdate and @enddate

Omega002

ASKER

It will work if you comment that portion out but I need that condition for my report. I suggestions in regards to that?

Omega002

ASKER

any suggestions in regards to that?

Anthony Perkins

What are the data types for @startdate and @enddate? Are they datetime?

Omega002

ASKER

the startdata and enddate are based off the run_date for capture date range parameters for a report, so it is needed.

Anthony Perkins

If they are datetime you can do something like this:

SELECT
CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101) run_date,
name,
server,
run_status
from dbo.BACKUP_HISTORY
Where run_date BETWEEN CAST(CONVERT(char(8), @startdate, 112) as integer) AND CAST(CONVERT(char(8), @enddate, 112) as integer)

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Omega002

ASKER

Ok that works. Thanks!

Anthony Perkins

If it reads:
DECLARE @startdate integer,
@enddate integer

And the format is the same as the run_date, than use:
SELECT
CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101) run_date,
name,
server,
run_status
from dbo.BACKUP_HISTORY
Where run_date BETWEEN @startdate AND @enddate

If it reads:
DECLARE @startdate nvarchar(8),
@enddate nvarchar(8)

And the format is the same as the run_date, than use:
SELECT
CONVERT(char(10), CAST(CAST(run_date as char(8)) as datetime), 101) run_date,
name,
server,
run_status
from dbo.BACKUP_HISTORY
Where run_date BETWEEN CAST(@startdate as integer) AND CAST(@enddate as integer)

If it reads... Well, I think you get the picture.