Here’s an example of a conversion failing: SELECT TRY_CONVERT(date, 'Next month') AS Result But if the TRY_CONVERT() fails, it returns NULL. If the CONVERT() function fails it returns an error. You can also use TRY_CONVERT() instead of CONVERT() for better error handling. Result: Conversion failed when converting date and/or time from character string.įor more examples, see CAST vs TRY_CAST in SQL Server: What’s the Difference? The TRY_CONVERT() Function If we provide that same value to the CAST() function instead, here’s what happens: SELECT CAST('Next month' AS date) AS Result Here’s an example of a cast failing: SELECT TRY_CAST('Next month' AS date) AS Result However, if you use the TRY_CAST() function instead, it returns NULL in such cases. When using the CAST() function, if the cast fails, it returns an error. You can use TRY_CAST() instead of CAST() to provide a smoother way of handling errors. But the PARSE() function can handle this: SELECT PARSE('Thursday, ' AS datetime2)įor more examples, see How to Convert a String to a Date/Time in SQL Server using PARSE(). If you provide the date in this format to the other functions, you’ll get an error. The reason I do this is to demonstrate the benefit that this function has over the others. In this example, I add the weekday as part of the string. SELECT CONVERT(date, '') AS Result įor more examples, see How to Convert a String to a Date/Time in SQL Server using CONVERT(). Here, we do the same conversion as the previous example, but with the CONVERT() function. SELECT CAST('' AS date) AS Result įor more examples, see How to Convert a String to a Date/Time in SQL Server using CAST(). In this example, we convert the string into a date data type (we specify this by using date as the second argument). In this post I outline six T-SQL functions that allow you to do this.īelow are example of how you can use these functions to convert a string to a date/time data type. 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.If you need to convert a string into a date/time value in SQL Server, you have a number of options. We can then check whether the minimum date is in our 30 day region or not. 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 00:00:00.000). WHEN MIN(.) <= DATEADD(dd,-30,GETDATE()) THEN 'Not all databases backed up'ĮLSE 'All databases backed up as of ' + CAST(MAX(.) AS NVARCHAR(100)) WHEN MIN(.) IS NULL THEN 'Not all databases backed up' IS NULL)ĪND sdb.name NOT IN ('master', 'model', 'msdb', 'tempdb','DBA') ISNULL(MAX(backup_finish_date),0) AS LastBackUpTime, 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 INSERT INTO ĬASE WHEN LastBackupTime IS NULL THEN 'Not all databases backed up in last 30 days' ELSE MIN(LastBackUpTime) END as LastBackUpTime WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb') AND bus.backup_finish_date > DATEADD(day,-30,GETDATE()) LEFT OUTER JOIN bus ON bus.database_name = sdb.name MAX(backup_finish_date) AS LastBackUpTime 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.
0 Comments
Leave a Reply. |