@AuraEnabled public static List&lt;StaffReport__c&gt; getUserReports(String UserId, Date startDate, Date endDate) {             return [Select Date__c from StaffReport__c where User__c =: UserId AND Date__c &gt;=: startDate AND Date__c &lt;=: endDate ORDER BY Date__c ]; }  Here i am passing startDate date and endDate in Date format, But it is appending timestamp in Query like 2019-06-01 00:00:00  I need to remove timestamp from Date and use it in Query  I am Using below methods to remove  Datetime.newInstance(sd.year(), sd.month(), sd.day()).format('yyy-MM-dd') this will result 2019-06-01 but cant assign it to Date type  but at the End Date__c is Date type need to convert it from Datetime to Date, Again appending 00:00:00 Any solution for this? Thanks in Advance.
122501
The following code returns the time of the current date:  System.debug('Current date: ' + Date.today().toStartOfMonth());   Output:     2018-08-01 00:00:00   How to remove time from date?
9891
I have a series of ranges with start dates and end dates. I want to check to see if a date is within that range.  Date.before() and Date.after() seem to be a little awkward to use. What I really need is something like this pseudocode:  boolean isWithinRange(Date testDate) {     return testDate &gt;= startDate &amp;&amp; testDate &lt;= endDate; }   Not sure if it's relevant, but the dates I'm pulling from the database have timestamps.
54518
I would like to use the  package to format date(time) ranges.  For example, \DTMdaterange{2017-08-05}{2017-09-10} would output 2017-08-05/09-10 in ISO format, or something like August 5 to September 10, 2017 in US English.  I looked into implementing it myself, but find it a bit hard.  Is there any package that already includes this functionality aside from ?  I thought  might be a good candidate, but cannot find anything useful (I am mostly interested in formatting dates; times are not necessary).
121927
When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For example:  SELECT CAST(GETDATE() AS VARCHAR) -- May  8 2015  9:13AM   Is there a way to change the default style used? For example, I would like to use style 126 so that the results look more like this:  SELECT CAST(GETDATE() AS VARCHAR) -- 2015-05-08T09:14:19.437   If possible, I would like to only change the default style for the current process or statement; however, I am also willing to change it at the database or schema level.  I should note: I am aware of CONVERT and the ability to manually specify the style. However, in this particular situation, I am unable to use CONVERT. I am CASTing a SQL_VARIANT parameter to a scalar-valued function as VARCHAR.
18077
Given the following components  DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999'   What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'?  Some things which don't work are listed below.    SELECT @D + @T       Operand data type date is invalid for add operator.     SELECT CAST(@D AS DATETIME2(7)) + @T       Operand data type datetime2 is invalid for add operator.     SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)      The datediff function resulted in an overflow. The number of dateparts   separating two date/time instances is too large. Try to use datediff   with a less precise datepart.   * The overflow can be avoided in Azure SQL Database and SQL Server 2016, using .    SELECT CAST(@D AS DATETIME) + @T       The data types datetime and time are incompatible in the add operator.     SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)      Returns a result but loses precision 2013-10-13 23:59:59.997
13404
The  explains how to put a Datetime query in a SOQL query.    SELECT Id FROM Account WHERE CreatedDate &gt; 2005-10-08T01:02:03Z   I need to just put in a date for this query. Something like:   SELECT Id FROM Account WHERE CreatedDate &gt; 2005-10-08   But that throws an error. Any ideas?
21450
Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases  (PIVOT, ROWS to COLUMNS, TRANSPOSE &amp;c.) and all I get is  TSQL this, TSQL that and or Oracle this, Oracle that, proprietary function this, proprietary function that well, you get the picture.  I have a simple table structure - if it was any simpler, it'd be a list :-) - i.e. it's two columns.  stock_date       sku ----------       --- 2016-06-01         0 2016-06-02       123 2016-06-03       123 2016-06-04       123 2016-06-06       123 2016-06-07       123 2016-06-03       245 &lt;... &amp;c...&gt;   There may be many thousands of sku's but the date range will be at most a month or so, but can vary, so the code needs to cater for that.   So, I want to make the stock_date field a column.   sku     1   2   3   4   5   6   7 ---     -   -   -   -   -   -   -  123     0   Y   Y   Y   0   Y   Y 245     0   0   Y.... &amp;c.   Ideally, the code would be dynamic and would work with any server (even MySQL).   But if it has to use common table expressions and/or window functions, that's  OK but not proprietary crosstab or pivot functions.
60004
Is it possible to convert a timestamp to a date format in a query?  This is the code I am using.  $query = db_select('my_submissions', 's'); $query-&gt;fields('s', array('sid','name','company','email','phone','is_customer','submitted','count')); $entries = $query-&gt;execute();  $rows = array(); foreach ($entries as $entry) {   $row = array_map('check_plain', (array) $entry);   $row['submitted'] = $row['submitted'] ? date('d.m.Y H:i', $row['submitted']) : "";   $rows[] = $row; }
9895
So what I need is a limitation for the user of the funded contracts. So this is what I am trying to implement. I am trying to let the user enter dates within the year 2014, which will bring up how many funded contracts are there for each month. I ONLY want up to the last day of the previous month and the passed months within the year 2014. So for example we are in June, if the user inputs any dates that are before June he can see those months of how many funded contracts are in there - but not any contract from June.   So the user should not be able to see July until we hit August which we can see July from there. Here is my query.  I feel that my where clause for the GetDate is incorrect. Any help will be appreciated             Alter Proc spGetAdminTotalYTD             (@Begin_Date  DATETIME,              @End_Date DATETIME,             @program int=null) As      Begin Declare @year int Set @year = 2014          SELECT  d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 1 THEN 1 ELSE NULL END) January             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 2 THEN 1 ELSE NULL END) Feburary             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 3 THEN 1 ELSE NULL END) March             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 4 THEN 1 ELSE NULL END) April             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 5 THEN 1 ELSE NULL END) May             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 6 THEN 1 ELSE NULL END) June             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 7 THEN 1 ELSE NULL END) July             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 8 THEN 1 ELSE NULL END) August             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 9 THEN 1 ELSE NULL END) September             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 10 THEN 1 ELSE NULL END) October             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 11 THEN 1 ELSE NULL END) November             , COUNT(CASE WHEN  MONTH(c.Funded_date) = 12 THEN 1 ELSE NULL END) December,             COUNT(1) AS YTD         FROM tdealer a JOIN tContact b ON a.contact_id = b.contact_id               JOIN tContract  c ON a.dealer_id = c.dealer_id JOIN tCompany d               ON  c.company_id = d.company_id         WHERE YEAR(c.Funded_date) = @Year              AND c.Funded_date &gt;= DATEADD(MONTH, DATEDIFF(MONTH, 0,GETDATE())-4,0)              AND c.Funded_date &lt;  DATEADD(MONTH, DATEDIFF(MONTH,-1,GETDATE())-4,0)              AND c.program_id = @program              AND c.funded_date Between @Begin_Date And @End_Date              GROUP BY     d.name,     a.dealer_code,     b.last_name,     b.city,     b.state,     b.phone     END
99102
Im trying to format this String into DateTime.  String     2019-01-03T00:26:01.711-02:00   Code   String data = '2019-01-03T00:26:01.711-02:00';  Datetime dt = DateTime.parse(data);   Exception     System.TypeException: Invalid date/time: 2019-01-03T00:26:01.711-02:00
44986
I have a String representation of a date that I need to create a Date or Calendar object from. I've looked through Date and Calendar APIs but haven't found anything that can do this other than creating my own ugly parse method. I know there must be a way, does anyone know of a solution?
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;');
105187
I want to delete the records from Data Extension based on Date Field in my DE i.e. I want to delete record which were created date before 30 days.But I didn't find date manipulation function in SSJS like dateadd() in ampscript
15824
SELECT GETDATE()   Returns: 2008-09-22 15:24:13.790  I want that date part without the time part: 2008-09-22 00:00:00.000  How can I get that?
88277
I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what.  I have a spreadsheet, exported from our exchange server, that contains a column with dates on. They have come out in US format even though I'm in the UK.   The column in question looks like this  04/08/2012 04/09/2009 04/01/2010 04/21/2011 04/05/2012 08/30/2009 08/29/2010 08/28/2011   In Excel, I have highlighted the column and selected Format Cells.... In this dialog box, I have selected the Date, selected English (United States) as the locale and chosen the matching date format from the list. I hit OK and try to sort the data by this column.   In the sort dialogue I choose this column, select sort on Values but the order only gives me options for A to Z, not oldest to newest as I would expect.  This in turn sorts the date data by the first two digits.  I am aware I could re-format this data to ISO and then the A to Z sort would work but I shouldn't have too, I'm obviously missing something. What is it?  EDIT: I messed up the bounty but this should have gone to @r0berts answer, his first suggestion of Text to Columns with no delimiter and choosing 'MDY' as the type of data works. Additionally, if you have a time (i.e. 04/21/2015 18:34:22), you need to first get rid of the time data. However after that the method suggested by @r0berts works fine.
105534
I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID.  ID    date          Frequency 1     2014-05-18      5 1     2014-05-19      4 1     2014-05-20      25 2     2014-05-20      7 3     2014-05-18      4 3     2014-05-20      1 4     2014-05-18      6   I'm looking to insert the dates for each ID that aren't currently there (assuming that the date ranges from 2014-05-18 to 2014-05-20) and add a value of 0 in Frequency for those.  For example, you can see that  ID 1 has an observation for all three dates;  ID 2 is missing 2014-05-18 and 2014-05-19;   ID 3 is missing 2014-05-19;   ID 4 is missing 2014-05-19 and 2014-05-20.  Therefore, the output should look like this  ID    date          Frequency 1     2014-05-18      5 1     2014-05-19      4 1     2014-05-20      25 2     2014-05-18      0 2     2014-05-19      0 2     2014-05-20      7 3     2014-05-18      4 3     2014-05-19      0 3     2014-05-20      1 4     2014-05-18      6 4     2014-05-19      0 4     2014-05-20      0   I've attempted the following from . However, that will only add dates with 0 Frequency that aren't present throughout the entire table instead of on an ID level.   DECLARE @s DATE = '20140518', @e DATE = '20140520';  SELECT c.date, Frequency = COALESCE(a.Frequency,0) FROM dbo.Calendar AS c LEFT OUTER JOIN dbo.table AS a ON c.date = s.[date] WHERE c.d &gt;= @s AND c.d &lt; DATEADD(DAY, 1, @e);
101689
I have a table tabName with the following rows:  id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open) 1          2015-05-10 00:00:00   1970-01-01 ..       open 2          2015-05-10 ..         1970-01-01 ..       new 3          2015-05-10            2015-05-12          resolved 4          2015-05-11 ..         1970-01-01 ..       open 5          2015-05-11 ..         1970-01-01 ..       new 6          2015-05-11            2015-05-11          resolved 7          2015-05-12 ..         1970-01-01 ..       open 8          2015-05-12 ..         1970-01-01 ..       new 9          2015-05-12            2015-05-12          resolved   NOTE:  1970-01-01 .. is default time for all the tickets which are still open.  I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.  I framed a query like  SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1) FROM tabName WHERE DATE_FORMAT(Resolved, '%d-%m') &gt; DATE_FORMAT(Created, '%d-%m')       AND YEAR(Created) = '2015' GROUP BY createdd;   The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.  Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.  Updated: As suggested by Abhik, i have tried in sqlfiddle site.  Here is what i created:  CREATE TABLE test    (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));    INSERT INTO test       (id,'created','resolved',status`) VALUES       (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),    (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),    (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),    (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),    (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),    (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),    (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),    (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),    (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')    ;   `   When i run  ` select created_date, @open:= @open+open_ticket-resolved as open from(  select   date_format(created,'%d-%m') as created_date ,  sum( case when status='open' then 1 else 0 end )+  sum( case when status='new' then 1 else 0 end )+  sum( case when status='resolved' and ( date(resolved) > date(created) or  date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,  sum(case when status = 'resolved' and date(resolved) = date(created) then 1   else 0 end) as resolved  from test  where  YEAR(Created) = '2015'  group by created_date )x,  (select @open:=0)r order by created_date ; `  I got following output  created_date   open 10-05   3 11-05   5 12-05   8  Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.