Get the time of a datetime using T-SQL?
Just to add that from SQL Server 2008, there is a TIME datatype so from then on you can do: SELECT CONVERT(TIME, GETDATE()) Might be useful for those that use SQL 2008+ and find this question.
sql-server-2000
How can I insert binary file data into a binary SQL field using a simple insert statement?
I believe this would be somewhere close. INSERT INTO Files (FileId, FileData) SELECT 1, * FROM OPENROWSET(BULK N’C:\Image.jpg’, SINGLE_BLOB) rs Something to note, the above runs in SQL Server 2005 and SQL Server 2008 with the data type as varbinary(max). It was not tested with image as data type.
SQL where datetime column equals today’s date?
Looks like you’re using SQL Server, in which case GETDATE() or current_timestamp may help you. But you will have to ensure that the format of the date with which you are comparing the system dates matches (timezone, granularity etc.) e.g. where convert(varchar(10), submission_date, 102) = convert(varchar(10), getdate(), 102)
SQL Server 2000: How to exit a stored procedure?
You can use RETURN to stop execution of a stored procedure immediately. Quote taken from Books Online: Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed. Out of paranoia, … Read more
Get structure of temp table (like generate sql script) and clear temp table for current instance
You need to use quotes around the temp table name and you can delete the temp table directly after using drop table …. select * into #myTempTable — creates a new temp table from tMyTable — some table in your database exec tempdb..sp_help ‘#myTempTable’ drop table #myTempTable
Is SQL IN bad for performance?
There are several considerations when writing a query using the IN operator that can have an effect on performance. First, IN clauses are generally internally rewritten by most databases to use the OR logical connective. So col IN (‘a’,’b’,’c’) is rewritten to: (COL = ‘a’) OR (COL = ‘b’) or (COL = ‘c’). The execution … Read more
Is there a way to persist a variable across a go?
Use a temporary table: CREATE TABLE #variables ( VarName VARCHAR(20) PRIMARY KEY, Value VARCHAR(255) ) GO Insert into #variables Select ‘Bob’, ‘SweetDB’ GO Select Value From #variables Where VarName=”Bob” GO DROP TABLE #variables go
Declaring a default constraint when creating a table
Do it inline with the column creation: [load_date] SMALLDATETIME NOT NULL CONSTRAINT [df_load_date] DEFAULT GETDATE() I have used square brackets rather than quotes as many readers won’t work with QUOTED_IDENTIFIERS on by default.
How do I drop a function if it already exists?
IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N’function_name’) AND xtype IN (N’FN’, N’IF’, N’TF’) ) DROP FUNCTION function_name GO If you want to avoid the sys* tables, you could instead do (from here in example A): IF object_id(N’function_name’, N’FN’) IS NOT NULL DROP FUNCTION function_name GO The main thing to catch is … Read more
Is there a way to list open transactions on SQL Server 2000 database?
For all databases query sys.sysprocesses SELECT * FROM sys.sysprocesses WHERE open_tran = 1 For the current database use: DBCC OPENTRAN