Issue with extract and convert of date field

I am required to find the time when SQL Service was shut down ungracefully. There is nothing in the event viewer or the SQL Logs till it restarts.

So 

I am extracting date from this statement:
The previous system shutdown at 1:30:21 PM on ‎3/‎29/‎2017 was unexpected.
I am able to get the date via extraction and it coverts it to datetime using powershell.
But when I insert that datetime into a sql table it gives the following error:

Msg 241, Level 16, State 1, Line 18
Conversion failed when converting date and/or time from character string.

By opening the string in UltraEdit for Hexa characters, I get to see this:
1:30:21 PM on ‎3/‎29/‎2017

I suspect this is the reason for its failure
the characters ‎. Not sure how to overcome this.

Any help is welcome

  • So, you are discovering the data and copying and paster from you extraction
    back into SQL or are you saying you are using PoSH to insert the data back
    into SQL?

    Since you know the string, you can just use -replace on the string to rid of
    those chars.

    $DateString = '‎3/‎29/‎2017'
    $DateString -replace ('‎','')
    3/29/2017
  • In reply to postanote:

    hi thanks for the response. the situation is, these characters are not visible to naked eye.
    I did run the replace command but I still cannot remove those characters:

    ‎3/‎29/‎20

    Apparently they are Unicode. The source is Windows Event Log. the full statement I extract and transform the date for is
    The previous system shutdown at 1:30:21 PM on ‎3/‎29/‎2017 was unexpected.
  • In reply to postanote:

    I am not sure why this editor keeps changing my RegEx text to an airplane gif. Argh!!!!
    Where that airplane is, that should be a bracketed 'am' string.
  • In reply to PareshMotiwala:

    ...and you are grabbing this info directly on the SQL server, from which event log?
    Can you show you code that you are using to extract this data?


    Are you doing something like this?

    # Get the event
    $EventLogMessage = Get-WinEvent -LogName Application | Select -First 3 -ExpandProperty Message

    The COM+ sub system is suppressing duplicate event log entries for a duration of 86400 seconds. The suppression timeout can be controlled by a REG_DWORD value named S
    uppressDuplicateDuration under the following registry key: HKLM\Software\Microsoft\COM3\Eventlog.
    Security policy in the Group policy objects has been applied successfully.
    This instance of SQL Server has been using a process ID of 1516 since 6/12/2017 12:32:31 AM (local) 6/12/2017 4:32:31 AM (UTC). This is an informational message only;
    no user action is required.


    # Extract the events that have a target message - in this case dat and time
    $EventLogMessage -match '(\d+/\d+/\d+) \d+:\d+:\d+ m'

    This instance of SQL Server has been using a process ID of 1516 since 6/12/2017 12:32:31 AM (local) 6/12/2017 4:32:31 AM (UTC). This is an informational message only;
    no user action is required.


    # Extract just the date
    $fmt = 'M\/d\/yyyy'
    $culture = [Globalization.CultureInfo]::InvariantCulture

    (Get-WinEvent -LogName Application | Select -First 3 ) | ? {
    $_.Message -match '(\d+/\d+/\d+)'
    } | select @{n='CaptureDate';e={[DateTime]::ParseExact($matches[1], $fmt, $culture)}}


    CaptureDate
    -----------
    6/12/2017 12:00:00 AM
  • In reply to postanote:

    Thanks, I am gonna try your suggestion. Meanwhile this is the code I'm running:

    $UnPlannedShutDownText =Get-EventLog -LogName "System" -ComputerName $servername -Source "EventLog" -EntryType "Error" -Message "The previous system shutdown at*"
    foreach($Crash in $UnPlannedShutDownText)
    {
    $E_ID= "0000"
    $msg =$crash.Message
    $separator = "The previous system shutdown at "," on "," was unexpected."
    $option = [System.StringSplitOptions]::RemoveEmptyEntries
    $foo = ($msg.split($separator,$option))
    #$foo[0]
    #$foo[1]
    $date = $foo[1] + ' ' + $foo[0]
    #$date
    #$EventTimedt = $([string]::Format('convert(datetime,{0}{1}{2},101)',"'",$Date,"'"))
    $eventtimedt=$eventtimedt -replace('‎','')

    $commandText = "INSERT HS_STG_ISODownTime (Servername,TimeOfEvent,EventID,TypeOfEvent,Message)
    VALUES ('$servername', '$EventTimedt','$E_ID','Unplanned Shutdown','$msg')"
    $commandText
    }
    $command = $conn.CreateCommand()
    $command.CommandText = $commandText
    $command.ExecuteNonQuery()
    }
  • In reply to PareshMotiwala:

    I want to find the exact time when the previous unplanned system shutdown was.
    When a system shuts down ungracefully, it leaves (and cannot leave) any error message in the event log till much after it restarts with a message "The previous system shutdown at 1:39:06 PM on 07/13/2017 was unexpected."
    This is the only place where I can find this date.