I have started work on a existing database where the DOB field has not been setup correctly and is a varchar which has allowed many invalid date formats:

I have been in the process of converting a lot of dates to the format dd/mm/yyyy

This i just a verbose of my queries I have used in the process:

 

-- Change DateFormat - to /

UPDATE user SET DOB = REPLACE(DOB,'-','/')
WHERE DOB NOT REGEXP
'(0?[1-9]|1[0-2])/(0?[1-9]|[1-2][1-9]|3[0-1])/([1-3][0-9]{3,3})'
AND DOB NOT LIKE '%/%' AND DOB LIKE '%-%'
AND LENGTH(DOB) = 10


-- Change DDMMYYYY DateFormat to DD/MM/YYYY

UPDATE user SET DOB = CONCAT(SUBSTRING(DOB,1,2),'/',SUBSTRING(DOB,3,2),'/',SUBSTRING(DOB,5,4))
WHERE DOB NOT REGEXP
'(0?[1-9]|1[0-2])/(0?[1-9]|[1-2][1-9]|3[0-1])/([1-3][0-9]{3,3})'
AND DOB NOT LIKE '%/%' AND DOB NOT LIKE '%-%'
AND DOB NOT LIKE '%.%' AND DOB NOT LIKE '% %'
AND LENGTH(DOB) = 8



-- Finding Invalid Date Formats

SELECT * FROM user WHERE DOB NOT REGEXP
'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'


-- Finding Invalid Date Formats that don't conform to dd/mm/yyyy

SELECT * FROM user
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND LENGTH(DOB) <> 10
AND DOB NOT REGEXP '2?/'


--- converted ddmmyyyy to dd/mm/yyyy

UPDATE user SET DOB = CONCAT(SUBSTRING(DOB,1,2),'/',SUBSTRING(DOB,3,2),'/',SUBSTRING(DOB,5,4))
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND LENGTH(DOB) <> 10
AND DOB NOT REGEXP '2?/'
AND DOB REGEXP '[0-9]{8}'


SELECT * FROM user
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND LENGTH(DOB) <> 10
AND DOB NOT REGEXP '2?[/]'




--- converted ddmmyy to dd/mm/19yy

UPDATE user SET DOB = CONCAT(SUBSTRING(DOB,1,2),'/',SUBSTRING(DOB,3,2),'/','19',SUBSTRING(DOB,5,2))
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND LENGTH(DOB) <> 10
AND DOB NOT REGEXP '2?/'
AND DOB REGEXP '[0-9]{6}'


--- finding dates with alpha's

SELECT * FROM user
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND DOB REGEXP '[A-Z]{1}'

----

removing invalid chars

UPDATE user
SET DOB = REPLACE(DOB,' ',''), DOB =REPLACE(DOB,'.',''), DOB = REPLACE(DOB,',','')
,DOB = REPLACE(DOB,'`',''), DOB =REPLACE(DOB,'//','/'), DOB = REPLACE(DOB,',','')
, DOB = REPLACE(DOB,'O','0')



-- Finding Valid Date Formats

SELECT * FROM user WHERE DOB REGEXP
'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'


-- convert dd/mm/yy assuming dob is in the 1900s

UPDATE user SET DOB = CONCAT(SUBSTRING(DOB,1,2),'/',SUBSTRING(DOB,4,2),'/','19',SUBSTRING(DOB,7,2)) 
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND DOB LIKE '%/%'
AND LENGTH(DOB) = 8

-- remove DOB with . at the end

UPDATE user SET DOB = SUBSTRING(DOB,1,10)
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND DOB LIKE '%.%'
AND DOB NOT LIKE '%/%'
AND DOB LIKE '%.'


--- replacing DOB's with '.' to '/'

UPDATE user SET DOB = REPLACE(DOB,'.','/')
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND DOB LIKE '%.%'
AND DOB NOT LIKE '%/%'
AND DOB REGEXP '^[0-9]'



----


UPDATE user
SET DOB = REPLACE(UPPER(DOB),'JANUARY','01'),
 DOB = REPLACE(UPPER(DOB),'FEBURARY','02'),
 DOB = REPLACE(UPPER(DOB),'MARCH','03'),
 DOB = REPLACE(UPPER(DOB),'APRIL','04'),
 DOB = REPLACE(UPPER(DOB),'MAY','05'),
 DOB = REPLACE(UPPER(DOB),'JUNE','06'),
 DOB = REPLACE(UPPER(DOB),'JULY','07'),
 DOB = REPLACE(UPPER(DOB),'AUGUST','08'),
 DOB = REPLACE(UPPER(DOB),'SEPTEMBER','09'),
 DOB = REPLACE(UPPER(DOB),'OCTOBER','10'),
 DOB = REPLACE(UPPER(DOB),'NOVEMBER','11'),
 DOB = REPLACE(UPPER(DOB),'DECEMBER','12')

------------
UPDATE user
SET DOB = REPLACE(UPPER(DOB),'JAN','01'),
 DOB = REPLACE(UPPER(DOB),'FEB','02'),
 DOB = REPLACE(UPPER(DOB),'MAR','03'),
 DOB = REPLACE(UPPER(DOB),'APR','04'),
 DOB = REPLACE(UPPER(DOB),'MAY','05'),
 DOB = REPLACE(UPPER(DOB),'JUN','06'),
 DOB = REPLACE(UPPER(DOB),'JUL','07'),
 DOB = REPLACE(UPPER(DOB),'AUG','08'),
 DOB = REPLACE(UPPER(DOB),'SEP','09'),
 DOB = REPLACE(UPPER(DOB),'OCT','10'),
 DOB = REPLACE(UPPER(DOB),'NOV','11'),
 DOB = REPLACE(UPPER(DOB),'DEC','12')

----------


UPDATE user SET DOB = REPLACE(DOB,'ST','')
,DOB = REPLACE(DOB,'ND','')
,DOB = REPLACE(DOB,'RD','')
,DOB = REPLACE(DOB,'TH','')


------------

SELECT * FROM user
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND DOB NOT REGEXP '2?/'


-------
-- only display dates with 6 numbers ie ddmmyy

select * from user
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND LENGTH(DOB) <> 10
AND DOB NOT REGEXP '2?/'
AND DOB REGEXP '[0-9]{6}'
 

-- swapping dates from mm/dd/yyyy to dd/mm/yyyy

UPDATE user SET DOB = CONCAT(SUBSTRING(DOB,4,2),'/',SUBSTRING(DOB,1,2),'/',SUBSTRING(DOB,7,4))
WHERE DOB NOT REGEXP '(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)'
AND DOB REGEXP '3?[/]'
AND LENGTH(DOB) = 10
 

 

 

If you want to update the data in a table by getting information from another table.  You can acheive this by using an update inner join query.

For example if i wanted to update the phone numbers in my table using a list of contacts from a secondary table, the UPDATE INNER JOIN Query would be like the one below:

UPDATE c
SET c.PhoneNumber = c2.PhoneNumber
FROM Contact AS c
INNER JOIN ContactTable2 c2 on c.Id = c2.Id
WHERE DateCreated > GETDATE()-60

I have come across this error when using a Indentity In a stored procedure.

In a stored procedure you need to define the scope indentity in a variable before you can use it in a select statement

So instead of

SELECT * FROM table WHERE id = SCOPE_IDENTITY()

You need to use 

 

SET @var = SCOPE_IDENTITY();

SELECT * FROM table WHERE id = @var

 

 @@IDENTITY  will return the last indentity across the whole database whilst SCOPE_IDENTITY() will return only the indentity related to the scope of the operation

To Search for special characters in SQL Server for example _

you must encapsulate the special character in a []

 

For example SELECT * FROM MyTable WHERE MyColumn LIKE '%[_]%'

will bring up results containing _

To get the column names of a table in MSSQL 

SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

This is basically the MSSQL equivalent for The MySQL Command DESCRIBE TableName which displays the table details

 

I often use this so i can copy and paste into another query etc.

SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

then i have the commas for use in any INSERT or SELECT etc

 

This example shows you how to obtain a maximum value from a related table when doing a SQL JOIN

In this example I would like to find the most recent transaction for each customer in my system:

SELECT FirstName,LastName,WorkPhone,HomePhone,MobilePhone,Address1,Address2,City,State,ZipCode,EmailAddress,CreditLimit,betType,SelectionName,betTime
FROM Customers c
LEFT OUTER JOIN Transactions t on c.CustomerId = t.CustomerId and t.Transaction = (SELECT MAX(TransactionTime) FROM Transactions tran WHERE tran.pin = c.pin)

to find the first transaction you would perform change the MAX to a MIN

 

USING A CUSROR

DECLARE @FirstName varchar(255), @LastName varchar(255)

DECLARE user_cursor CURSOR FOR

SELECT FirstName,LastName FROM Users

OPEN user_cursor

FETCH NEXT FROM user_cursor

INTO  @FirstName, @LastName

WHILE @@FETCH_STATUS = 0

BEGIN

print @FirstName

FETCH NEXT FROM user_cursor INTO  @FirstName, @LastName

END

CLOSE user_cursor

DEALLOCATE user_cursor

 

USING A TEMPORARY TABLE/VARIABLE

SET NOCOUNT ON
DECLARE @Users TABLE (
 RowID int IDENTITY(1, 1),
 FirstName varchar(255),
 LastName varchar(255)
)

DECLARE @NumberOfRecords int, @RowCount int
DECLARE @FirstName varchar(255)

INSERT INTO @Users(FirstName,LastName)
SELECT FirstName,LastName FROM Users

SET @NumberOfRecords = @@ROWCOUNT
SET @RowCount = 1


WHILE @RowCount <= @NumberOfRecords
BEGIN        

  -- Do Operations

SET @RowCount = @RowCount + 1
END