none
U-SQL type DateTime? conversion issues

    Question

  • I have a column in my extract that is of type DateTime? that is nullable.  Later in my script when selecting records and modifying the results I cannot parse the DateTime? column without an error.  I can get it to work if I set the column as a String instead of DateTime?.  But if I use DateTime? I get errors like: 

    'System.DateTime?' does not contain a definition for 'Year' and no extension method 'Year' accepting a first argument of type 'System.DateTime?' could be found (are you missing a using directive or an assembly reference?)

    What is the correct way to parse a DateTime? data type? My example code is:

    @outputRecords = SELECT modificationtime.Year AS Date, COUNT(*) AS RowCount FROM @PRRecords AS r GROUP BY modificationtime.Year;

    I've also tried DateTime.Parse(modificationtime).ToString("yyyy") and more.  Thanks!

    Wednesday, August 15, 2018 8:41 PM

Answers

All replies

  • Did you try modificationtime.Value.Year?
    • Marked as answer by FrankMn Thursday, August 16, 2018 3:08 PM
    Thursday, August 16, 2018 12:45 AM
  • Edit: I found my original job that I tried using modification.Value and it failed because I did modification.Value.ToString("yyyy") which gave this error:

    {
    	"jobId": "https://greatclipsbidev.azuredatalakeanalytics.net/jobs/6376c521-47b2-4f6b-a580-644dc91cc3e8?api-version=2017-09-01-preview",
    	"errors": {
    		"diagnosticCode": 195887121,
    		"severity": "Error",
    		"component": "RUNTIME",
    		"source": "User",
    		"errorId": "E_RUNTIME_USER_EXPRESSIONEVALUATION",
    		"message": "Error while evaluating expression modificationtime.Value.ToString(\"yyyy\")",
    		"description": "Inner exception from user expression: Nullable object must have a value.\nCurrent row dump: \tmodificationtime:\tNULL\r\n",
    		"resolution": "",
    		"helpLink": "",
    		"details": "==== Caught exception System.InvalidOperationException\n\n   at ___Scope_Generated_Classes___.SqlFilterTransformer_6.Process(IRow inRow, IUpdatableRow outRow) in d:\\data\\yarnnm\\local\\usercache\\cdcd8535-153c-4744-a96b-5cdfae939afc\\appcache\\application_1534287928056_108909\\container_e291_1534287928056_108909_01_000001\\wd\\__ScopeCodeGen__.dll.cs:line 60",
    		"userData": " UserData 195887121[$0 = modificationtime.Value.ToString(\"yyyy\"), $1 = \tmodificationtime:\tNULL\r\n]"
    	}
    }


    But I tried using just .Value.Year in a new script...and it worked. So for some reason when adding Value.ToString("yyyy") it can't handle null values.  But using Value.Year works, so thank you!!!!!



    • Edited by FrankMn Thursday, August 16, 2018 3:08 PM
    Thursday, August 16, 2018 2:38 PM