troubleshooting Question

Trouble with Date formatting in Sql Server 2008,, and then Javascript

Avatar of Nate_LR
Nate_LR asked on
ASP.NETJavaScriptMicrosoft SQL Server 2008
7 Comments1 Solution397 ViewsLast Modified:
I'm writing an in C# application that retrieves data from Sql Server 2008 and converts it to JSON so it can be placed on a Google Map.  Trying to figure out the proper way to handle date formatting is driving me crazy.

In Sql Server, the date column is an nvarchar (and there is nothing I can do about that).   Sometimes this column is like 18-OCT-10 17:36, 4/8/2009 11:34:00 AM, 3/3/11, or is just null or blank.  For testing I'm retrieving rows that are 6/15/14.  If I don't do any CASTS or CONVERTS with t-sql and bring it into my application like this...
dsGetFieldPoints.SelectParameters["trackID"].DefaultValue = trkID;
var fieldPts = dsGetFieldPoints.Select(DataSourceSelectArguments.Empty) as DataView;
var fieldPtMarkers = new List<FieldPoint>();

            foreach (DataRowView pt in fieldPts)
                FieldPoint fp = new FieldPoint();
                fp.locDate = (string)pt["locDate"];
            JavaScriptSerializer js = new JavaScriptSerializer();
            var fieldPtsJson = js.Serialize(fieldPtMarkers);
            var fieldPtsJsonFinal = "{\"fieldPoints\": " + fieldPtsJson + "}";
            ClientScript.RegisterStartupScript(this.GetType(), "Google Maps Initialization and Add Pts",
                      string.Format("initializeMap({0}, {1});", photosJson, fieldPtsJsonFinal), true);

In the JSON output the date looks like this - "6/15/14", but when I use javascript....
var fDate = new Date(fMarker.locDate);
I get this Mon  6/14/1914.   So I'm using a With clause in my t-sql statement and I try this....
With fieldPts AS 
		CAST(ISNULL(fp1.Loc_Date, '') AS DATE) AS locDate....)
CAST(fp.locDate AS NVARCHAR) AS locDate....
In the JSON output the date looks like this "2014-06-15", but when I use the javascript it looks like this Sat  6/14/2014.   So I try not converting it back to nvarchar....
With fieldPts AS 
		CAST(ISNULL(fp1.Loc_Date, '') AS DATE) AS locDate....)

And then updating my code to .....
fp.locDate = (DateTime)pt["locDate"];
the JSON output looks like this "\/Date(1402808400000)\/" and the javascript produces undefined  Invalid Date.
In my t-sql I've tried using CONVERT...
convert(date, CAST(ISNULL(fp1.Loc_Date, '') AS DATE), 112) AS locDate
but it doesn't seem to work since I always get the same format of 2014-06-15.   What can I do now?  Thanks
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros