The last argument of CONVERT seems to determine the format used for parsing. Consult MSDN docs for CONVERT. 111 – the one you are using is Japan yy/mm/dd. I guess the one you are looking for is 103, that is dd/mm/yyyy. So you should try: SELECT convert(datetime, ’23/07/2009′, 103)
CASE WHEN last_name IS NULL THEN ” ELSE ‘ ‘+last_name END
Try this function CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime AS BEGIN DECLARE @RoundedTime smalldatetime DECLARE @Multiplier float SET @Multiplier= 24.0/@RoundTo SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar,@Time,121) AS datetime) AS float) * @Multiplier,0)/@Multiplier RETURN @RoundedTime END select dbo.roundtime(’13:15′,0.5) The 1st param is the time to be rounded and the 2nd will be base on your list … Read more
Best practice it to list your columns so you’re independent of table changes (new column or column order etc) insert into table1 (field1, field3) values (5,10) However, if you don’t want to do this, use the DEFAULT keyword insert into table1 values (5, DEFAULT, 10, DEFAULT)
You could use a CURSOR in this case: DECLARE @id INT DECLARE @name NVARCHAR(100) DECLARE @getid CURSOR SET @getid = CURSOR FOR SELECT table.id, table.name FROM table OPEN @getid FETCH NEXT FROM @getid INTO @id, @name WHILE @@FETCH_STATUS = 0 BEGIN EXEC stored_proc @varName=@id, @otherVarName=”test”, @varForName=@name FETCH NEXT FROM @getid INTO @id, @name END CLOSE … Read more
Itzik Ben-Gan’s example on how to build dynamic PIVOT, I highly recommend his Inside Microsoft SQL Server 2008: T-SQL Programming book — Creating and Populating the Orders Table USE tempdb; GO IF OBJECT_ID(‘dbo.Orders’) IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( orderid int NOT NULL PRIMARY KEY NONCLUSTERED, orderdate datetime NOT NULL, … Read more
Note: An incorrect revision of this answer was edited out. Please review all answers. A subselect in the WHERE clause to retrieve the greatest BALANCE aggregated over all rows. If multiple ID values share that balance value, all would be returned. SELECT ID, BALANCE FROM CUSTOMERS WHERE BALANCE = (SELECT MAX(BALANCE) FROM CUSTOMERS)
Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this: SELECT REPLACE(REPLACE(@str, CHAR(13), ”), CHAR(10), ”)
SQL is a declarative language; you write a query that logically describes the result you want, but it is up to the optimizer to produce a physical plan. This physical plan may not bear much relation to the written form of the query, because the optimizer does not simply reorder ‘steps’ derived from the textual … Read more
SQL Server 2012 and 2014 supports IIF(cont,true,false) function. Thus for minimal selection you can use it like SELECT IIF(first>second, second, first) the_minimal FROM table While IIF is just a shorthand for writing CASE…WHEN…ELSE, it’s easier to write.