|
|
-
|
|
This post follows my previous two posts about datetimehttp://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspxhttp://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-ii.aspxNow we will see some example querie......
|
|
-
|
|
This post follows my blog post Understanding datetime column part I
Date FormatsUnambiguous formats YYYYMMDD YYYYMMDD HH:MM:SS YYYY-MM-DDTHH:MM:SS Ambiguous formats DD/MM/YYYY MM/DD/YYYY DD-MM-YYYY MM-DD-YYYY YYYY-MM-DD etc
Someone wants to input dates in DD/MM/YYYY format
Declare @test table(......
|
|
-
|
|
There are N number of questions asked in the forums about handling dates in query Most of the people who ask questions dont understand how datetime column works in SQL Server Some of the questions frequently asked are about
1 using dates in the WHERE caluse 2 formatting dates using SQL 3 inserting......
|
|
-
|
|
People use Dynamic SQL for various purposes One of the disadvantages of it is SQL injection that can attack your database by doing the things that you didn't want to do Consider a case where usage of Dynamic SQL in unavoidable You have a table that records the daily activities of the employees Your ......
|
|
-
|
|
Here is the script that takes backup of all databases excluding system databases
The file name would be databasename_yyyymmdd where yyyymmdd is the formatted value of current date
create procedure Backup_all_databases
as
Declare @sql varchar(8000)
set @sql=''
select @sql=@sql+
'
Backup dat......
|
|
-
|
|
In this post Extract only numbers from a String, I described a method of extrating numbers from a string using split and concatenate technique
In this post Regular expression, I described a method to extract whole numbers if a specific digit is found Consider that you have a situation where you ne......
|
|
-
|
|
Can we use the temporary table created in one procedure from another procedure?
The answer depends on how you call the procedure Take this example Create a temporary table in a procedure
create procedure proc_1
as
create table #t(i int)
insert into #t select 10
select i from #t
Now create......
|
|
-
|
|
BULK INSERT is used to insert data to a table from a text file. Only problem with it is that it is not possible to specify the columns that you want data to be imported. For example the table has 4 columns whereas there are data for 2 columns in the text file. Consider the following dataSource data ......
|
|
-
|
|
Often I see people asking questions in the forums about finding the database name where the particular procedure exists You can use many methods to get a solution (cursor, undocumented procedure,dynamic sql,etc) The simplest are 1 Use Undocumented procedure
EXEC sp_msforeachdb
'if exists(sele......
|
|
-
|
|
When one of posters asked a question about this, someone posted the following reply
UPDATE msdb.dbo.sysjobs SET enabled = 0
As sysjobs is a system and updating it is generally not advised, the following can be used instead
declare @sql varchar(max)
set @sql=''
select
@sql=@sql+' ......
|
|