Easy: –start of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) –end of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) EDIT: The below will handle the Sunday date issue. DECLARE @input varchar(10) –SET @input=”9/9/2012″ — simulates a Sunday SET @input = GETDATE() –start of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, CASE DATEPART(dw,@input) WHEN 1 … Read more
select * from mytable where year(Columnx) = 2010 Regarding index usage (answering Simon’s comment): if you have an index on Columnx, SQLServer WON’T use it if you use the function “year” (or any other function). There are two possible solutions for it, one is doing the search by interval like Columnx>=’01012010′ and Columnx<=’31122010′ and another … Read more
In my opinion, a dynamic PL/SQL block is somewhat obscure. While is very flexible, is also hard to tune, hard to debug and hard to figure out what’s up. My vote goes to your first option, EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job; Both uses bind variables, but first, for me, is more redeable and … Read more
Sure, just add the same calculation to the GROUP BY clause: select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))), sum(mwspp.QtyRequired) from manufacturingweekshortagepartpurchasing mwspp where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366 group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) order by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) … Read more
Since the release of Oracle 12c it is now easier to rearrange columns logically. Oracle 12c added support for making columns invisible and that feature can be used to rearrange columns logically. Quote from the documentation on invisible columns: When you make an invisible column visible, the column is included in the table’s column order … Read more
Just a suggestion when it comes to indexes on datetime in msql is the index footprint impacts search times (Yes this seems obvious…but please read onward). The importances to this when indexing on the datetime say for instance ‘2015-06-05 22:47:20.102’ the index has to account for every place within the datetime. This becomes very large … Read more
Each tablespace has one or more datafiles that it uses to store data. The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile. To find out if the actual limit is 32gb, run the following: … Read more
Types like sql.NullInt64 do not implement any special handling for JSON marshaling or unmarshaling, so the default rules apply. Since the type is a struct, it gets marshalled as an object with its fields as attributes. One way to work around this is to create your own type that implements the json.Marshaller / json.Unmarshaler interfaces. … Read more
I was looking for an answer on the same subject, about “bulk/batch” update. People often describe the problem by comparing it with insert clause with multiple value sets (the “bulk” part). INSERT INTO mytable (mykey, mytext, myint) VALUES (1, ‘text1’, 11), (2, ‘text2’, 22), … Clear answer was still avoiding me, but I found the … Read more
ANY (or its synonym SOME) is a syntax sugar for EXISTS with a simple correlation: SELECT * FROM mytable WHERE x <= ANY ( SELECT y FROM othertable ) is the same as: SELECT * FROM mytable m WHERE EXISTS ( SELECT NULL FROM othertable o WHERE m.x <= o.y ) With the equality condition … Read more