I am passing {4/1/2017 12:00:00 AM} from my C# code to SQL Server as a parameter of a stored procedure. And in the stored procedure, I want to convert this into MMddyyyy format for data processing. I tried many suggestions from Google but nothing works for me.  Throwing this error:     The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
122443
I need to convert a value which is in a DateTime variable into a varchar variable formatted as yyyy-mm-dd format (without time part). How do I do that?
92463
How do I convert a DateTime structure to its equivalent  formatted string representation and/or parse this string representation back to a  structure? The RFC-3339 date-time format is used in a number of specifications such as the .
26106
I have webservice which is passed an array of ints. I'd like to do the select statement as follows but keep getting errors. Do I need to change the array to a string?  [WebMethod] public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate) {         command.CommandText = @"SELECT id,                             startDateTime, endDateTime From                             tb_bookings WHERE buildingID IN                             (@buildingIDs) AND startDateTime &lt;=                             @fromDate";      SqlParameter buildID = new SqlParameter("@buildingIDs", buildingIDs); }
40138
What's the proper way to convert from a scientific notation string such as "1.234567E-06" to a floating point variable using C#?
96922
Why when I save a value of say 40.54 in SQL Server to a column of type Real does it return to me a value that is more like 40.53999878999 instead of 40.54?  I've seen this a few times but have never figured out quite why it happens.  Has anyone else experienced this issue and if so causes it?
112692
In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'?  Duplicated .
31971
In an extract I am dealing with, I have 2 datetime columns. One column stores the dates and another the times as shown.  How can I query the table to combine these two fields into 1 column of type datetime?  Dates  2009-03-12 00:00:00.000 2009-03-26 00:00:00.000 2009-03-26 00:00:00.000   Times  1899-12-30 12:30:00.000 1899-12-30 10:00:00.000 1899-12-30 10:00:00.000
6181
Something very odd is happening here.   I have a query that looks like this.  SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date &gt; @mydate and identifier = 0000000000   When run as a raw query it returns data just fine.  When i place it in a stored procedure that alters the where clause it throws this error.  Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2] The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.   So here is the oddity. I go through all the possible data for that where clause with a query like this.  SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000   and  SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000   And this is what i get back.  17 12 9 19 8 14 6 16 11 13 7 10 0 18 5 15 4   Nowhere does it have anything remotely too large for a SMALLINT. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.  I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.
44272
EDIT:  I found out that I can get it to compile if I cast the IMetadataType object to the TMetadata type.  Why do I need to do this?  EDIT #2:  The "Values" property is a .NET dictionary of type &lt;TMetadata, TData&gt;.  I have this generic method:  private void FillMetadata&lt;TMetadata, TData&gt;     (Metadata&lt;TMetadata, TData&gt; oMetadata) where TMetadata : IMetadataType {     IMetadataType o;     oMetadata.Values.Add(o, (TData)(object)GetValue()); }   I have stripped the implementation to simplify it (I actually use a real object, not the IMetadataType declared here).  My question is, why doesn't this compile?  The compile error is on the Add() method:  "cannot convert from 'IMetadataType' to 'TMetadata'."  Isn't that what the "where" clause on the method is for?  What am I missing?
33272
There is this example code, but then it starts talking about millisecond / nanosecond problems.  The same question is on MSDN, .  This is what I've got so far:  public Double CreatedEpoch {   get   {     DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime();     TimeSpan span = (this.Created.ToLocalTime() - epoch);     return span.TotalSeconds;   }   set   {     DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime();     this.Created = epoch.AddSeconds(value);   } }
76797
I'm using a DateTime in C# to display times. What date portion does everyone use when constructing a time?  E.g. the following is not valid because there is no zero-th month or zero-th day:  // 4:37:58 PM DateTime time = new DateTime(0, 0, 0, 16, 47, 58);   Do I use COM's zero date?  // 4:37:58 PM DateTime time = new DateTime(1899, 12, 30, 16, 47, 58);   Or perhaps SQL Server's?  //4:37:58 PM DateTime time = new DateTime(1900, 1, 1, 16, 47, 58);   I realize it's arbitrary, since I'll be ignoring the date portions in code, but it would still be nice to be able to use:  DateTime duration = time2 - time1;     Answer  I think I like MinValue   DateTime time = DateTime.MinValue.Date.Add(new TimeSpan(16, 47, 58));     Note: I can't use a TimeSpan, because that doesn't store times of the day. And the reason I know that is because there's no way to display its contents as a time.  Which is to say that TimeSpan records a span of time, not a time of day, e.g.:  TimeSpan t = new TimeSpan(16, 47, 58); t.ToString();   returns a span of time in the format hours:minutes:seconds, e.g.:  16:47:58   rather than a time:  4:47:58 PM    (United States) 04:47:58 nm   (South Africa) 4:47:58.MD    (Albania) 16:47:58      (Algeria) 04:47:58 م    (Bahrain) PM 4:47:58    (Singapore) 下午 04:47:58  (Taiwan) 04:47:58 PM   (Belize) 4:47:58 p.m.  (New Zealand) 4:47:58 μμ    (Greece) 16.47.58      (Italy) 오후 4:47:58   (Korea) 04:47:58 ب.ظ  (Iran) ਸ਼ਾਮ 04:47:58   (India) 04:47:58 p.m. (Argentina) etc   In other words, there is a difference between a timespan, and a time. And also realize that TimeSpan doesn't provide a mechanism to convert a span of time into a time of day - and there is a reason for that.
99429
Is there a fairly easy way to convert a datetime object into an RFC 1123 (HTTP/1.1) date/time string, i.e. a string with the format  Sun, 06 Nov 1994 08:49:37 GMT   Using strftime does not work, since the strings are locale-dependant. Do I have to build the string by hand?
75001
As the topic suggests I wish to be able to pass table names as parameters using .NET (doesn't matter which language really) and SQL Server.  I know how to do this for values, e.g. command.Parameters.AddWithValue("whatever", whatever) using @whatever in the query to denote the parameter. The thing is I am in a situation where I wish to be able to do this with other parts of the query such as column and table names.  This is not an ideal situation but it's one I have to use, it's not really prone to SQL injection as only someone using the code can set these table names and not the end-user. It is messy however.  So, is what I am asking possible?   EDIT: To make the point about SQL injection clear, the table names are only passed in by source code, depending on the situation. It is the developer who specifies this. The developer will have access to the database layer anyway, so the reason I am asking is not so much for security but just to make the code cleaner.
36947
Apparently if I want to format a date to my own format ie. 1 June 2016 10:44 am I can only do this with outputText but output text does not respect the users time zone so everything is GMT. If I use outputField it automatically converts it to the users time zone but I have no control over the format it displays it in. Does someone know how to get my custom format to display in the users time zone? I don't care which I use I just want to show it my way in the proper time zone  This respects users time zone but I cannot control formatting  &lt;apex:outputField value="{!c.CreatedDate}"/&gt;   or This does not respect users time zone but I can control formatting   &lt;apex:outputText value="{0,date,dd MMM YYYY h:mm a}"&gt;   &lt;apex:param value="{c.CreatedDate}"/&gt; &lt;/apex:outputText&gt;   This method is called from the controller when page is called and populates the variable csComments with records  public void ShowComments() {   disp = true;   RetrieveAcctInfo();   caseNo = System.currentPagereference().getParameters().get('cn');   System.debug('Case No: ' + caseNo);   cd = [select id, AccountId, Priority from Case where CaseNumber=:caseNo];   if (acctid != cd.AccountId) { disp = false; errMsg = 'Access Restricted'; }    else {     csComments = [select Comment__c, CreatedDate from CSPComments__c where CaseCom__c=:cd.id ORDER BY CreatedDate DESC];     ccCount = csComments.size();   }   initpri = new List&lt;SelectOption&gt;();     initpri.add(new SelectOption('None',' -- Select Priority --', true));   initpri.add(new SelectOption('Level 1','1 - System Down'));   initpri.add(new SelectOption('Level 2','2 - Funtioning with Errors'));   initpri.add(new SelectOption('Level 3','3 - System Issue'));   initpri.add(new SelectOption('Level 4','4 - Non Critical Issue'));   inpri = cd.Priority;   curpri = inpri; }   This is the part of the page that is showing the results. The manager wants my date to be in the approved style of 1 June 2016 11:26 am    &lt;apex:pageBlock id="cl" rendered="{!disp}"&gt;    &lt;!-- Retrieved {!ccCount} comments&lt;br /&gt;  --&gt;     &lt;table&gt;&lt;tr&gt;&lt;th&gt;Date/Time&lt;/th&gt;&lt;th&gt;Comment&lt;/th&gt;&lt;/tr&gt;       &lt;apex:repeat value="{!csComments}" var="c"&gt;         &lt;tr style="padding:4px 0px 4px 0px"&gt;           &lt;td padding-right="4px"&gt;             &lt;!-- &lt;apex:outputText value="{0,date,d MMM YYYY h:mm a}"&gt;&lt;apex:param value="{!c.CreatedDate}"/&gt;&lt;/apex:outputText&gt;  --&gt;               &lt;apex:outputField value="{!c.CreatedDate}" /&gt;             &lt;/td&gt;           &lt;td&gt;&lt;textarea class="dtarea" rows="4" cols="60" disabled="true"&gt;{!c.Comment__c}&lt;/textarea&gt;&lt;/td&gt;         &lt;/tr&gt;     &lt;/apex:repeat&gt;     &lt;/table&gt;     &lt;/apex:pageBlock&gt;
28038
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure?   For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it.   SQL Server 2005 is my only applicable limitation I think.  create procedure getDepartments   @DepartmentIds varchar(max) as   declare @Sql varchar(max)        select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'   exec(@Sql)
119807
I am trying to retrieve list items using date range based on date column. It implicit both 'Fromdate' and 'Todate'.My CAML query is also querying on two other conditions as well. I am using the below query but it is not retrieving the data.when I remove the date range from query data is retrieved fine. But i want date range as well in my query how would i do this. Little help would work for me.   camlQuery.set_viewXml('&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;And&gt;&lt;And&gt;&lt;Neq&gt;&lt;FieldRef Name="ID" /&gt;&lt;Value Type="Number"&gt;0&lt;/Value&gt;&lt;/Neq&gt;&lt;Eq&gt;&lt;FieldRef Name="_ModerationStatus" /&gt;&lt;Value Type="ModStat"&gt;0&lt;/Value&gt;&lt;/Eq&gt;&lt;Geq&gt;&lt;FieldRef Name="Created" /&gt;&lt;Value Type="DateTime"&gt;' + fromDate + '&lt;/Value&gt;&lt;/Geq&gt;&lt;Leq&gt;&lt;FieldRef Name="Created" /&gt;&lt;Value Type="DateTime"&gt;' + toDate + '&lt;/Value&gt;&lt;/Leq&gt;&lt;/And&gt;&lt;/And&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name="_ModerationStatus" Ascending="True" /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;&lt;/View&gt;');
10301
I was wondering if there was an easy way in SQL to convert an integer to its binary representation and then store it as a varchar.  For example 5 would be converted to "101" and stored as a varchar.