Assuming SQL Server 2005+, use a recursive query: WITH sample AS ( SELECT CAST(‘2010-12-01’ AS DATETIME) AS dt UNION ALL SELECT DATEADD(dd, 1, dt) FROM sample s WHERE DATEADD(dd, 1, dt) <= CAST(‘2010-12-04’ AS DATETIME)) SELECT * FROM sample Returns: dt ——— 2010-12-01 00:00:00.000 2010-12-02 00:00:00.000 2010-12-03 00:00:00.000 2010-12-04 00:00:00.000 Use CAST/CONVERT to format as … Read more
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
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.
GO is not properly a TSQL command. Instead it’s a command to the specific client program which connects to an SQL server (Sybase or Microsoft’s – not sure about what Oracle does), signalling to the client program that the set of commands that were input into it up till the “go” need to be sent … 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