Dewi Jones Dewi Jones - 4 years ago 172
SQL Question

Convert SQL Returned date to DateTime Format

I wonder if you can help me! I've searched the great answers of above and Google and alas i have been unable to find the answer to my conundrum!

Essentially I am running a SQL command from within PowerShell to retrieve the last restore Date Time however it is not displaying in a way that I (I think) cannot compare to the current date to see if the restore is consistent.

The script running is as per below:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SERVER\Instance;Database=DataBaseName;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "DECLARE @DB sysname = 'DataBaseName';
SELECT TOP 1 restore_date
FROM msdb.dbo.restorehistory
WHERE destination_database_name = @DB
ORDER BY restore_date DESC;"
$SqlCmd.Connection = $SqlConnection
$dbrestoredate = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-Output "Database Restore date " $dbrestoredate


However this returns the format as "07 February 2017 09:15:30".

I'd much prefer this to be a standardized format e.g.
dd/mm/yyyy
so that I can compare that value to today's date and if it's a match do something.

If you have another way of doing the comparison that's fine by me, I just need to it to do something if the date returned is today's date.

Answer Source

You could use convert:

select TOP 1 CONVERT(char(10), restore_date, 103) from msdb.dbo.restorehistory

This will return a string representing the date in the requested format (dd/mm/yyyy)

Please note that using TOP x without using ORDER BY is non-deterministic, since the order of the rows returned by the query can not be guaranteed unless using the order by clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download