I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server? Answer I’m not sure there is an exact match for the format you want. But you can get close with convert() and style 106. Then, replace the spaces:
Weekday number regardless of the NLS settings
I’m looking for the simplest way to determine the weekday number for the DATE value in oracle independent of the NLS settings. Any ideas? Answer ISO weeks start on Monday; they don’t use NLS settings. I think this expression is reliable. To show how the arithmetic works . . . current_date is a Wed…
Select random rows with no duplicates
I need to select a random amount of rows but also ensure that the rows don’t contain duplicate Image values: ImgID Image 1637 PM1371564839.jpg 1638 PM1371564839.jpg 1639 …
Multiple OUTPUT clauses in MERGE/INSERT/DELETE SQL commands?
I have a T-SQL script that implements some synchronization logic using OUTPUT clause in MERGEs and INSERTs. Now I am adding a logging layer over it and I would like to add a second OUTPUT clause to …
LINQ to SQL, select from an inheritted class
Using LINQ to SQL and Object Relational Designing; Let’s say we have a class Animal with two inherited classes, named Cat and Dog. We can load the “Animal” class and its properties easily: However, when trying to load Cat or Dog, dataContext has the attribute of neither Cats nor Dogs. How is…
LINQ to SQL multiple tables left outer join
I have this query in SQL, and I want it to implement it in LINQ using Entity Framework, but how can I apply multiple tables left outer joins? SELECT d.bookingid, d.labid, d.processid, …
SQL select multiple max rows where ID is same
i’ve struggled again and again on this, but can’t get it to work. been on these forums for hours now… Data set: Row Date AccountID Column 1 1 21/02/2013 0:30 A0M8FA1003YP . 2 21/02/2013 0:30 A0M8FA1003YP . 3 21/02/2013 0:30 A0M8FA1003YP . 4 24/09/2007 12:00 A0M8FA1003YP . 5 24/09/2007 12:00 …
Indexing distinct values for a column
I have a table containing a column authors that contains names of authors for different articles (different rows). I want to add a new column that contains a unique index for every author,i.e. let’s say 12 for every occurrence of Dickens (just a random pick) in the author column. Can it be done by a MyS…
How to escape underscore in the string query in hibernate and SQL?
The _ (underscore) given in the SQL query is not honored. Example : SELECT * FROM employee WHERE NAME LIKE ‘%k_p%’; This matches and brings many rows apart from rows which contain k_p Could …
Select count of users registered in each month
Having a very simple table: CREATE TABLE users ( name VARCHAR(50) NOT NULL, registered TIMESTAMP NOT NULL, CONSTRAINT users_pk PRIMARY KEY (name) ); How to select the number of user …