w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to formating in SQL Server
I believe that you have uniqu row for class and subject combination. In this case you can use the below query - select class, max(case when subject = 'Chemistry' then total else 0 end) as Chemistry, max(case when subject = 'Biology' then total else 0 end) as Biology, max(case when subject = 'Physics' then total else 0 end) as Physics, max(case when subject = 'Maths' then total else 0 end) as Maths from your_table_name group by class

Categories : SQL

SQL Server: Want to use between clause with dates, but dates in string form (YYYY.MM.DD)
Not good solution, but works (cost much performance). You have formated date in order year, month, day (good order to compare strings, without transformation to datetime), so you can try SELECT * FROM Table WHERE StringDate > '2013.07.10' AND StringDate < '2013.07.14' It returns bad results if there are dates before year 1000 without leading zero ('999.07.14'). But I dont know how it works on big database. SQL Fiddle

Categories : Mysql

If I have a query that compares two dates in SQL Server, what will happen if one of the dates is null?
always UNKNOWN. NULL value must be treated with IS NULL statement Try this example: declare @d1 datetime declare @d2 datetime set @d1 = GETDATE() select case when @d1 > @d2 then 'OK' else 'KO' end

Categories : SQL

SQL Server: Data between two dates
You cant use BETWEEN in case of date even if BETWEEN is inclusive. For such cases it is better to use startdate>='2012-07-08 ' and enddate<=2013-02-06 Because 2013-02-06 will be treated as 2013-02-06 00:00:00 12AM Try this solution for your problem DECLARE @temp AS TABLE(Month_Name varchar(25),ContactName varchar(50)) -- newly added ;WITH cte AS ( SELECT datename(month, @startdate) AS [Month_Name], @startdate AS dat UNION ALL SELECT datename(month, DateAdd(Month, 1, dat)), DateAdd(Month, 1, dat) FROM cte WHERE DateAdd(Month, 1, dat) < @enddate ) INSERT INTO @temp SELECT c.Month_Name,Cs.username FROM cte c CROSS JOIN usertbale Cs ORDER BY c.Month_Name SELECT c.Month_Name,c.username,ISNULL(tt.coun,0) as coun FROM @temp c LEFT JOIN ( SELECT daten

Categories : Sql Server

How To find overlapping Dates in SQL Server
If you have a set of periods and want the overlaps of all of them, then the overlaps are going to be between the maximum starttime and the minimum end time. select cast(starttime as date) as thedate, max(starttime), min(endtime) from #periods group by cast(starttime as date); This assumes that the start and end are on the same date. The first just extracts the date from the datetime values (and this may vary depending on the database). The version here is for SQL Server. You have no overlaps when min(endtime) is less than max(starttime). So, to get dates with an overlap: select cast(starttime as date) as thedate, max(starttime), min(endtime) from #periods group by cast(starttime as date) having min(endtime) > max(starttime); EDIT: If you want overlaps within a site code, just

Categories : Sql Server

Display Dates not in database using sql server
If you are using SQL Server 2005+, you could try using something like DECLARE @StartDate DATETIME = '01 Jan 2013', @EndDate DATETIME = '31 Jan 2013' ;With Dates AS ( SELECT @StartDate RunDate UNION ALL SELECT RunDate + 1 FROM Dates WHERE RunDate + 1 <= @EndDate ) SELECT * FROM Dates d LEFT JOIN YourTable yt ON d.RunDate = yt.YourDate WHERE yt.YourDate IS NULL OPTION (MaxRecursion 0) In this query, we are using a recursive CTE to generate the date series (the OPTION (MaxRecursion 0) is to ensure that you do not receive a recursive error), then we are joining to the table in question, and only returning values that are missing from that table.

Categories : SQL

SQL Server Find Duplicate dates with the same ID
If you need to return the rows, then you want to use a window function: select [uniqueID], [requirementId], [number], [description], [dtmexecuted], [amount] from (select t.*, count(*) over (partition by requirementid, dtmexecuted) as cnt from MyTable t ) t where cnt > 1

Categories : SQL

How to Sum total between two given dates in sql server 2008
SELECT DATEPART(MONTH, invoiceDate), DATEPART(YEAR, invoiceDate), COUNT(1) FROM table1 where invoiceDate between '1/1/2012' and '7/31/2013' GROUP BY DATEPART(MONTH, invoiceDate), DATEPART(YEAR, invoiceDate)

Categories : Sql Server

.NET SQL Server calculating dates across months issue
You can define two variables and assign values like this and use in your where clause. SET @startingMonth= CAST(CAST(YEAR(getdate()) AS varchar) + '-' + CAST(MONTH(getdate()) AS varchar) + '-' + '20' AS DATETIME) IF(MONTH(getdate())<>12) BEGIN SET @endMonth= CAST(CAST(YEAR(getdate()) AS varchar) + '-' + CAST(MONTH(getdate())+1 AS varchar) + '-' + '19' AS DATETIME) END ELSE BEGIN SET @endMonth= CAST(CAST(YEAR(getdate())+1 AS varchar) + '-' + CAST(1 AS varchar) + '-' + '19' AS DATETIME) END

Categories : Dotnet

Handling Dates from Oracle to SQL Server 2005 using OPENQUERY
I do it with this function (partial code): select @StringOut = '{ts ''''' + convert(varchar(20), @DateIn, 20) + ''''' }'; RETURN @StringOut and this goes into the query: and procedure_date_dt = ' + dbo.TimestampString(@date) + ' and event_type = ''''Time Notation'''' and it works when I run it.

Categories : SQL

SQL Server 2008 R2 - Creating a table of dates using a while statement. Looking for better options?
Probably the best way to do this is with a fixed calendar table; go ahead and create a permanent table with all of the business rules you need for handling date logic. As a workaround, you can do something like (assuming you have more than 24 columns in your database): DECLARE @dd DATE SET @dd = '01/04/2013'; SELECT TOP 24 DATEADD(week, 2*rn, @dd) FROM (SELECT rn=(ROW_NUMBER() OVER (ORDER BY name)) -1 FROM sys.columns) c

Categories : SQL

how to get diffrence between two dates using DATEDIFF function dynamically using sql server 2008
DECLARE @Date1 AS DATETIME DECLARE @Date2 AS DATETIME SELECT @Date1 = publish_date FROM book_master WHERE book_name = 'science' SELECT @Date2 = publish_date FROM book_master WHERE book_name = 'maths' SELECT DATEDIFF(DAY,@Date1,@Date2)

Categories : Sql Server

How can I only retrieve rows with dates greater than today's date from an XML column in SQL Server?
You can store today's datetime value in a variable and use sql:variable() in your expression to use the variable in the comparison. DECLARE @Today DATETIME; SET @Today = GETDATE(); WITH XMLNAMESPACES ('http://www.testsite.com/ClientWebinar' as lbx) SELECT * FROM LB_Webinar WHERE LB_Webinar.WebinarXML.exist('/lbx:CLIENT_WEBINAR/lbx:WEBINAR/lbx:WEBINARDATE/text()[. > sql:variable("@Today")]') = 1; Note: the above code works if you change your datetime values to ISO8601 yyyy-mm-ddThh:mi:ss.mmm.

Categories : SQL

SQL Server 2012 Failure: when Importing flat file and DATES are in FORMAT of YYYY.M.DD
You could use a programmers editor, or sed (if you have Cygwin tools installed) to do a regular expression replace on the date strings, since the pattern is quite distinctive (and all you would need to do is replace the period with dashes to do a bulk import, which is what I'm assuming you're trying to do). Failing to do that, is there an OleDB driver for Tableau? You could import the flat files into Tableau, and then from there to SQL Server...

Categories : SQL

SQL Server 2008 - Sum business minutes between two dates taking into account custom holidays and weekends
First, This is the structure I have used, I don't think it will take much adapting to fit it into your structure. (Note I would recommend a lot more fields in your calendar table, but IsWorkingDay is the only one required for this example) SET DATEFIRST 1; CREATE TABLE dbo.Calendar ( [Date] DATE NOT NULL, IsWorkingDay BIT NOT NULL CONSTRAINT PK_Calendar_Date PRIMARY KEY ([Date]) ); -- INSERT DATES IN 2013 (NOT DOING A FULL TABLE AS IT'S JUST AN EXAMPLE) INSERT dbo.Calendar ([Date], IsWorkingDay) SELECT [Date] = DATEADD(DAY, Number, '20130101'), 1 FROM Master..spt_values WHERE Type = 'P' AND Number < 365; -- UPDATE NON WORKING DAYS UPDATE dbo.Calendar SET IsWorkingDay = 0 WHERE DATEPART(WEEKDAY, [Date]) IN (6, 7) OR [Date] IN ('201

Categories : SQL

LINQ to SQL Data Context - Date Properties - Change DateTImeKind Unspecified Dates to Local Dates
The MSDN article talks about comparing client-side dates. Linq To SQL converts your statement to SQL, where the DateTimeKind property isn't used. You should use DateTimeOffset instead of DateTime. This will allow you to use DateTimeOffset methods in SQL and take advantage of the DATETIMEOFFSET type in SQL Server. LINQ to SQL generates SQL statements. System.DateTime and SQL Server's DATETIME types have no timezone information so it makes no sense to try and take into account the timezone. None of the two values contain any timezone, so how can you make any automatic conversions? The two DateTime values in your question come from two different sources. DateTime.Today comes from your local machine, where the timezone is known, so it's DateTimeKind.Local. The database columns though com

Categories : Dotnet

mySQL Select DISTINCT ips based on dates and return dates
Your query is SELECT COUNT(DISTINCT user_ip) FROM campaigns_visitors WHERE campaign_id = ? GROUP BY date This doesn't return date as you have found. You should have more luck with SELECT COUNT(DISTINCT user_ip), date FROM campaigns_visitors WHERE campaign_id = ? GROUP BY date Others are likely to recomment that you use prepared statements and mysqli routines; I have helpfully translated your query to a format that can be prepared. I trust you can construct the PHP to manipulate the changed statement.

Categories : PHP

updating random dates to a single column (dates repeates)
Don't use a subquery. Just set=your_expression, like in the below example. Oracle optimises the subquery and evaluates it only once, but the expression will be evaluated for each row: create table xyz( abc date ); insert into xyz select sysdate from dual connect by level < 6; select * from xyz; ABC -------- 13/07/17 13/07/17 13/07/17 13/07/17 13/07/17 and now: update xyz set abc = to_date(trunc(dbms_random.value(2456428,2456428+76)), 'J'); select * from xyz; ABC -------- 13/06/14 13/07/23 13/07/26 13/06/24 13/07/10

Categories : SQL

In C#, parse out biweekly sets of dates from a collection of dates?
You'll need to figure out the week number for the date in question. For example: var dt = new DateTime(...); // your datetime var cal = new System.Globalization.GregorianCalendar(); var weekNum = cal.GetWeekOfYear(dt, CalendarWeekRule.FirstDay, DayOfWeek.Sunday); There are different strategies for counting weeks of the year, which you specify with a CalendarWeekRule and corresponding DayOfWeek for the starting day of the week. You can read more here. Once you have a week number, you can do some math to make it "bi-weekly": int biweeklyKey = weekNum % 2; The value will be either 0 or 1, which you can use as a key in your algorithm.

Categories : C#

Convert text dates to dates and then keeping the NA values
The problem is that pd.NaT.date() will not raise an error, it will return datetime.date(1, 255, 255), so the part of your code where you catch an exception will never be reached. You'll have to check if the value is pd.NaT and in that case return 'NA'. In all other cases you can safely return obj.date() since the column has datetime64 dtype. def try_convert(obj): if obj is pd.NaT: return 'NA' else: return obj.date() n [17]: s.apply(try_convert) Out[17]: 0 2012-06-28 1 2013-05-21 2 2011-10-14 3 2011-11-11 4 2013-02-07 5 2013-01-02 6 2013-06-24 7 2013-07-15 8 2011-11-04 9 2013-04-29 10 2013-06-24 11 2012-10-22 12 NA 13 NA 14 2011-12-13 Name: 1_2, dtype: object

Categories : Python

How does excel convert Julian dates to calender dates?
41348 is not julian date. Excel needs to use a formula to convert a julian date to a proper date. 183349 is an example of a julian date. 41348 is simply the number of days passed after 01/01/1900. You can add those number of days to 01/01/1900, if I recall correctly, in SQL server to get the desired results. EDIT: Yup, use DATEADD. Add a column with 01/01/1900 UPDATE Table SET NewDate = DATEADD(Days, RawDate-2, '01/01/1900') You need to remove 1 from the RawDate since 1 corresponds to 01/01/1900 and another 1 apparently I'm not sure where, but investigating. On my SQL Server2008R2, apparently it considers 1900 to be a non-leap year. There goes the additional 1 to remove!

Categories : Sql Server

Access SQL - given two dates, return the dates of the previous period
Here's a little cleaner SQL. It assumes that the StartDate is always the first of the month. This calculates the period length, and returns the same length ending the day before the current period starts. SELECT QueryDates.StartDate, QueryDates.EndDate, Dateadd("m", Datediff("m", [EndDate] + 1, [StartDate]), [StartDate]) AS StartDatePrevious, [StartDate] - 1 AS EndDatePrevious FROM QueryDates;

Categories : SQL

Sorting Dates Does Not Return Sorted Dates
.sort() will sort in "dictionary" order, not numeric or date/time order. If the values aren't strings they'll get a default string conversion which for dates does not give you something useful to sort on. But you can supply a custom sort callback that knows how to sort dates: .sort(function(a,b) { return a.getTime() - b.getTime(); }); Demo: http://jsfiddle.net/TUAz9/

Categories : Javascript

Counting dates that fall between two dates in the same column
SQL Fiddle select t1.id, level, count(t2.id) from ( select id, level, tsrange( "time", lead("time", 1, 'infinity') over( partition by id order by level ), '[)' ) as time_range from t1 ) t1 left join t2 on t1.id = t2.id and t1.time_range @> t2."time" group by t1.id, level order by t1.id, level The solution starts creating a range of timestamps using the lead window function. Notice the [) parameter to the tsrange constructor. It means to include the lower and exclude the upper bound. Then it joins the two tables with the @> range operator. It means the range includes the element. It is necessary to left join t1 to have the zero counts.

Categories : Amazon

Getting results between two dates in PostgreSQL including those two dates
use end_date + 1 day and < instead of <= SELECT * FROM tbl WHERE start_date < '2012-04-13'::timestamp AND end_date >= '2012-01-01'::timestamp; I think it should perform better than casting data in you table to date

Categories : Ruby On Rails

Construct an array of dates between start and end dates. (d3.js)
To do it with d3: var dateArray = d3.time.scale() .domain([new Date(2013, 2, 28), new Date(2013, 3, 2)]) .ticks(d3.time.days, 1) Online demo: http://jsfiddle.net/aczuV/1/

Categories : Javascript

Using mysql to query dates that fall between 2 other dates
Assuming start_date and end_date are DATETIME, and you want to compare the date AND time components, you could do something like this: SELECT a.* FROM atable a WHERE a.start_date >= NOW() AND a.end_date <= NOW() + INTERVAL 30 DAYS If start_date and end_date are DATE (rather than DATETIME), you probably only want to compare the DATE portion (without regard to a time component) SELECT a.* FROM atable a WHERE a.start_date >= DATE(NOW()) AND a.end_date <= DATE(NOW()) + INTERVAL 30 DAYS You may actually want a less than comparison (rather than less than or equal to) on the end date. You need to determine what results you want on that edge case. And the interval may need to be specified as 29 days, depending on how you define "the next 30 days". Q: Wh

Categories : Mysql

Compare Two Dates with Two Other Dates in SqlServer 2008
I think you might be looking for something like (dbo.ArrangementPromotion.ActiveFromDate BETWEEN '2013-08-01' AND '2013-08-14') OR (dbo.ArrangementPromotion.ActiveToDate BETWEEN '2013-08-01' AND '2013-08-14' ) OR ('2013-08-01' BETWEEN dbo.ArrangementPromotion.ActiveFromDate AND dbo.ArrangementPromotion.ActiveToDate) OR ('2013-08-14' BETWEEN dbo.ArrangementPromotion.ActiveFromDate AND dbo.ArrangementPromotion.ActiveToDate) Where '2013-08-01' should be @ActiveFrom and '2013-08-14' @ActiveTo

Categories : Asp Net

json formating in c#
What actually happening is you already passing json string to the client, so at client if you give dataType as "json" it will again serialize string and will get above result. fact is this is not invalid json actually, you need it to parse to get json object at client side...using javascript var jsonObject = JSON.parse("{"Test":{"Name":"Test class","X":"100","Y":"200"}}");

Categories : C#

PHP date formating in JS
The output of strtotime is a UNIX timestamp which you can pass to the JavaScript Date object. The Date object assumes milliseconds (not seconds) since UNIX epoch so multiply that number by 1000. In one line, you could: var dob = new Date("<?php echo strtotime('-18 year'); ?>" * 1000); Then format this date: var dateString = dob.getFullYear() + '-' + (dob.getMonth() + 1) + '-' + dob.getDate(); Or to format the date with leading zeros: var dateString = dob.getFullYear() + '-' + ("00" + (dob.getMonth() + 1)).slice(-2) + '-' + ("00" + dob.getDate()).slice(-2); A mostly-PHP solution is to use the date function to format the date: var dateString = "<?php $ts = strtotime('whatever'); echo date('Y-m-d', $ts); ?>";

Categories : Javascript

Formating Images // HTML
Why don't you just float the elements to the left instead of using position absolute Try <div style="float:left;"> instead of <div style = "position: absolute;left:50;">

Categories : HTML

QML formating text in TextEdit
You can still use tags for formatting text inside a TextEdit Element, as shown in the documentation.

Categories : Misc

C# and SerialPort formating output
Just add a Thread.Sleep(50) betwen the send the write and the read command. 50 miliseconds should be enough if not try a longer time. //Do something sp.Write("POS;"); Thread.Sleep(50); string msgPos = sp.ReadExisting(); //Do something else I can't find any command POS; in the manual you posted. Do you mean FBK on page 15?

Categories : C#

Remove textbox formating
You can store the raw value in the TextBox.Tag property, like this: string originalSalaryValue = "123456"; txtBSalary.Tag = originalSalaryValue; txtBSalary.Text = someFormattedVersionOfOriginalSalaryValue; Now when you want to convert the value to a number, then you apply the Int32.Parse() to the Tag value, instead of the Text value, like this: int integerValue = Int32.Parse(txtBSalary.Tag); Read documentation for Control.Tag property.

Categories : C#

PHP Pie Chart Data Formating
Here is my solution: <?php /** * @author lolkittens * @copyright 2013 */ $cases = array( array( 'incident_type' => 'brawl' ), array( 'incident_type' => 'brawl' ), array( 'incident_type' => 'brawl' ), array( 'incident_type' => 'brawl' ), array( 'incident_type' => 'brawl' ), array( 'incident_type' => 'intoxicated_persons' ), array( 'incident_type' => 'intoxicated_persons' ), array( 'incident_type' => 'intoxicated_persons' ), array( 'incident_type' => 'intoxicated_persons' ), array( 'incident_type' => 'intoxicated_persons' ), ); $flat = array(); $unique = array(); $counts = array(); $final = arra

Categories : PHP

Formating big numbers in Java
This is what the BigInteger class (or BigDecimal, for non-integers) is for. These classes store numbers with arbitrary precision, and allow for standard arithmetic operations. You can get the exact value of the number as a string, and then format that as you wish (e.g. trimming the length). (Note that while it may seem like you can use these classes with a NumberFormat instance, this is not recommended as accuracy will be silently lost if the number doesn't fit into a double.) Here's an example of using it: // Create a BigDecimal from the input text final String numStr = editText.getValue(); // or whatever your input is final BigDecimal inputNum = new BigDecimal(numStr); // Alternatievly you could pass a double into the BigDecimal constructor, // though this might already lose precis

Categories : Java

Formating List into string
Sound like you need string.Join var result = string.Join(", ", yourList);

Categories : C#

C/C++ #include formating best practice
This is a subjective answer; if both work then both are correct, however I prefer to have no knowledge of the source tree within the source code, only in the project settings/Makefile, so for me the first option is best: #include "mycode.h"

Categories : C++

Formating bigdecimals sql oracle
Spaces are a curious separator, and not directly supported by to_char(). However, you can format with commas and then replace the commas with spaces: select replace(to_char(12345678.123, '999,999,999,999,999,999.999'), ',', ' ') from dual

Categories : SQL

Formating of persp3d plot
To assert control over the axes, you need to set axes=FALSE (as you were told yesterday) in the persp3d call , and then use the axes3d and axis3d functions with arguments of your choosing. To specify an x axis with values displayed along the lower yz- box segment, use edge= 'x--'. To label the y-axis with rownames, use the 'labels' and 'at' arguments: plot3d ( ..., axes=FALSE, ...) # repositions x axis and draws default z axis axes3d(c('x--','z')) # Use custom labels axis3d(edge= 'y+-', at =seq(500,2000,by=500), labels = rownames(fd)[seq(500,2000,by=500)] ) (Note: To play this SO game properly, you need to give credit to others who have offered help. This is not much different from a question from yesterday and I see no positive response for the helpful contribu

Categories : R



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.