SQL Server – Convert Date and Time from Character String

sql servertype conversion

I have a backup report which looks to see if all databases have been backed up within the last 30 days, and want to insert 'Not all databases backed up in last 30 days' into that field (datetime type) if it is the case that not all databases have been backed up within the last 30 days but i get the error Conversion failed when converting date and/or time from character string.

;WITH CTE AS
(
SELECT 
--sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb') AND bus.backup_finish_date > DATEADD(day,-30,GETDATE())
 AND sdb.Name NOT IN ('DBA')
AND sdb.state_desc  = 'ONLINE'
--GROUP BY sdb.Name
)
INSERT INTO Server.Database.Schema.TableName 
SELECT @@SERVERNAME  as serverName,
        --DatabaseName as DatabaseName,
        CASE WHEN LastBackupTime IS NULL THEN 'Not all databases backed up in last 30 days' ELSE MIN(LastBackUpTime) END as LastBackUpTime

FROM CTE
GROUP BY LastBackUpTime

Best Answer

The below should return 'Not all databases backed up' if any of the databases haven't been backed up, and 'All databases backed up as of DD/MM/YYYY' if they have (this will be the highest backup date found)

;WITH CTE AS
(
SELECT 
--sdb.Name AS DatabaseName,
ISNULL(MAX(backup_finish_date),0) AS LastBackUpTime,
[sdb].[name] AS DatabaseName
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE (bus.type = 'D' OR bus.[database_name] IS NULL)
AND sdb.name NOT IN ('master', 'model', 'msdb', 'tempdb','DBA')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT 
    CASE
        WHEN MIN([CTE].[LastBackUpTime]) IS NULL THEN 'Not all databases backed up'
        WHEN MIN([CTE].[LastBackUpTime]) <= DATEADD(dd,-30,GETDATE()) THEN 'Not all databases backed up'
        ELSE 'All databases backed up as of ' + CAST(MAX([CTE].[LastBackUpTime]) AS NVARCHAR(100))
    END
FROM CTE

The script looks for the highest backup date for all non-system (and non-DBA) databases, and returns the datetime equivalent of 0 if there are none (this converts to 1900-01-01 00:00:00.000). We can then check whether the minimum date is in our 30 day region or not.

The actual issue you were experiencing was because your CASE statement returned both a DATETIME data type and an VARCHAR data type - because of data type precedence for a CASE statement SQL server will always choose the data type of the highest precedence as the data type of that field - in this case that was a DATETIME.

To prevent this we simply need to CAST the DATETIME field as a VARCHAR, which prevents the implicit conversion from VARCHAR to DATETIME (lower to higher precedence)

Related Solutions

SQL Server – Send Email if No Backups in 24 Hours

Instead of e-mailing the results of the query as a file attachment (which, SQL Server won't know it's empty until after it runs the query as a part of sending the e-mail), I suggest instead building up the body of the e-mail as follows, and then using a simple IF condition to only send the e-mail if at least one database has contributed to populating the string:

DECLARE 
  @dblist NVARCHAR(MAX) = N'', 
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT 
   bs.database_name AS db, 
   MAX(bs.backup_finish_date) AS d, 
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age 
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs 
  WHERE [type] = 'D'  
  GROUP BY database_name 
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))  

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,  
    NULL AS d,  
    9999 AS age 
  FROM 
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS 
  (
    SELECT 1 FROM msdb.dbo.backupset 
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  --EXEC msdb.dbo.sp_send_dbmail 
  --  @recipients = 'dba@someemailaddress.com',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END

SQL Server Error – Conversion Failed When Converting Date and/or Time from Character String

You are storing dates as strings - why? Opening_Date and Date should be date or datetime, not varchar. But before you can fix that, you need to identify the rows that are causing the conversion problem:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE ISDATE([Date]) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE ISDATE(Opening_Date) = 0;

And now that you've fixed the question and I know you're using 2012 and not 2008 R2, it might be cleaner to use TRY_CONVERT(), especially since it will allow you to identify any rows where the month and day are transposed incorrectly. For example, assuming you expect dates to be stored as mm/dd/yyyy strings:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE TRY_CONVERT(datetime, [Date], 101) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE TRY_CONVERT(datetime, Opening_Date, 101) = 0;

In addition to identifying garbage rows where users have stored nonsense like floob and 9992-13-36 as "dates," this will also identify rows where users have stored 13/07/1999 instead of 07/13/1999 (but there is no way to know if 05/06/2000 is meant to be May 6 or June 5).

Now you need to fix those rows before you can correct the tables.

ALTER TABLE dbo.DimStore ALTER COLUMN Opening_Date date; -- or datetime;
ALTER TABLE dbo.FactTransactions ALTER COLUMN [Date] date; -- or datetime;

You might also consider renaming the Date column to be (a) less vague and (b) not a reserved word.

If you can't fix the tables, then you need to change your query:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, 
  CASE WHEN ISDATE(T2.Opening_Date) = 1 THEN T2.OpeningDate END,
  CASE WHEN ISDATE(T1.[Date]) = 1 THEN T1.Date END)
FROM dbo.FactTransactions AS T1
INNER JOIN dbo.DimStore AS T2 
  ON T1.cod_store = T2.cod_storeKey
WHERE ISDATE(T2.Opening_Date) = 1
  AND ISDATE(T1.[Date]) = 1;

And @RLF brought up a great point, too; if you can't fix the table, then the date columns could contain data that represent a specific date (say, September 7) but be entered in the wrong format (e.g. on a US English system, entered as a string in British format, 7/9/2015). So really, you need to fix the table and stop storing these things as strings.

Some other useful material:

Best Answer

Related Solutions

SQL Server – Send Email if No Backups in 24 Hours

SQL Server Error – Conversion Failed When Converting Date and/or Time from Character String

Related Question