get the date and time for today at midnight and add to it
Date now midnight time: Select DATEADD(d,0,DATEDIFF(d,0,GETDATE())) ADD 600 Minutes: Select DATEADD(mi,600,DATEDIFF(d,0,GETDATE()))
sql-server-2008
SQL Server 2008 SSMS won’t remember password
There is a solution for this in SSMS 2012 that worked for me. Microsoft now provides a mechanism for removing a server from the list of remembered servers, and removing the offending server from the list will allow you to save the password the next time you connect to it: In the Connect to Database … Read more
SQL Server Agent Job Notify multiple operators on failure
Question: Is it possible to setup a notification email being sent to multiple operators for that specific job? I don’t believe this is possible. Certainly looking at the structure of [msdb].[dbo].[sysjobs] the various operator_id columns are in this table itself which would support the idea that 1 to many is not possible. But some alternatives … Read more
Why are CLR Types derived from generics not supported in SQL Server 2008 and later?
One other suggestion: Verify that the type was defined as a class, is not primitive, nested or generic PS: As far as “worked on MSSQL 2005 but not MSSQL 2008” – I quote Tom Petty: “You got lucky, babe!” 😉
Full join multiple tables on same columns
There are several ways, but I think the following is the most popular: select allk.k, t1.c1, t2.v2, t3.v3, t4.v4 from (select k from t1 union select k from t2 union select k from t3 union select k from t4 ) allk left outer join t1 on allk.k = t1.k left outer join t2 on allk.k … Read more
In TSQL, how to evaluate an expression and assign it to a BIT field?
You can do this with a CASE statement: DECLARE @bitvar BIT DECLARE @search_term varchar(128) set @search_term = ‘abc’ SET @bitvar = CASE WHEN (@search_term = ‘abc’) THEN 1 ELSE 0 END select @bitvar
How to execute function in SQL Server 2008
It looks like there’s something else called Afisho_rankimin in your DB so the function is not being created. Try calling your function something else. E.g. CREATE FUNCTION dbo.Afisho_rankimin1(@emri_rest int) RETURNS int AS BEGIN Declare @rankimi int Select @rankimi=dbo.RESTORANTET.Rankimi From RESTORANTET Where dbo.RESTORANTET.ID_Rest=@emri_rest RETURN @rankimi END GO Note that you need to call this only once, … Read more
Cast collation of nvarchar variables in t-sql
SELECT CAST(‘abc’ AS varchar(5)) COLLATE French_CS_AS
Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
In SSMS, “Query” menu item… “Results to”… “Results to File” Shortcut = CTRL+shift+F You can set it globally too “Tools”… “Options”… “Query Results”… “SQL Server”.. “Default destination” drop down Edit: after comment In SSMS, “Query” menu item… “SQLCMD” mode This allows you to run “command line” like actions. A quick test in my SSMS 2008 … Read more
Update only time from my Datetime field in sql
UPDATE MyTable SET MyDate = DATEADD(HOUR, 4, CAST(CAST(MyDate AS DATE) AS DATETIME)) Or this UPDATE MyTable SET MyDate = DATEADD(HOUR, 4, CAST(FLOOR(CAST(MyDate AS FLOAT)) AS DATETIME))