|
|
-
|
|
Both the functions DATENAME and DATEPART are used to extract the informations from the date. However there is a difference between the two in terms of return datatype. DATENAME function always return the varchar datatype whereas DATEPART functions return Integer datatype Consider the following examp......
|
|
-
|
|
Someone in the forums asked for finding out a string in which each character is same. Consider the following example
declare @t table(data varchar(20))
insert into @t
select '222222222222' as data union all
select '666666663466' union all
select 'aaaaaaaa'+CHAR(32) union all
select 'jjkjkhdg'......
|
|
-
|
|
Sometimes developers use ordinal number of the column instead of actual column name in the SELECT statement.
For example, consider the following statement
SELECT emp_id, first_name,dob, address from employees
When a front end application executes this statement it receives a resultset from the......
|
|
-
|
|
Modulus operator is used to find the remainder after the division. All programming languages have specific operators to find a remainder like mod, rem, etc. In SQL Server, we can use modulus operator %. However we can also use general approach to find a remainder. Here are two methods
Method 1 : U......
|
|
-
|
|
In Part I of this series SQL Server's equivalent of MS Access functions, I have posted some direct equivalents. In this post, I post other functions that dont have direct equivalent
Choose : Returns a value from the list based on a given position
declare @list varchar(1000)
declare @select varcha......
|
|
-
|
|
Sometimes when you want to migrate from MS Accesss to SQL Server, you need to rewrite the queries compatible to SQL Server. As you know the basic systax is same but the functions used in Access should be replaced with their equivalent in SQL Server.
Here are some Access functions and their equival......
|
|
-
|
|
A Leap year contains an extra day so there are 366 days. There are many ways to find out whether the year is a Leap year. If the value of is_leap_year is 1 it is a Leap year otherwise it is not a Leap year.
Method 1 : General method
declare @year int
set @year=2000
select
case
when @y......
|
|
-
|
|
In the Query Analyser, set the Result mode to Text (Press CTRL+T) and run the following code
set nocount on
select
space(17-len(replicate(char(37),no)))+ replicate(char(37),no*2-1)
from
(
select top 10 row_number() over (order by name) as no from sysobjec......
|
|
-
|
|
Pinal Dave in his blog posted this about using a column name and column number in the Order by clause. He made some valid points on why the second method should not be used. However, there is a case in which second method can be useful
I had already blogged about Dynamic PIVOT in which you can pas......
|
|
-
|
|
In the SQL Server forums, I see questions complaining that BULK INSERT is not taking care of check constraints. There are also some other questions related to BULK INSERT. Here I summarise them and explain
1 Enabling Check contraint - Use the option check_constraints
create table test
(id int,
......
|
|