Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of Nate_LR
Nate_LR asked on
JavaScriptASP.NETMicrosoft SQL Server 2008
7 Comments1 Solution397 ViewsLast Modified:
I'm writing an Asp.net 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 asp.net 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"];
                fieldPtMarkers.Add(fp);
            }
            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);
fDate.toDateString()
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 
		(SELECT 		
		CAST(ISNULL(fp1.Loc_Date, '') AS DATE) AS locDate....)
SELECT 
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 
		(SELECT 		
		CAST(ISNULL(fp1.Loc_Date, '') AS DATE) AS locDate....)
SELECT 
fp.locDate....

And then updating my asp.net 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
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answers