Calculate Age in SQL

FLOOR((CAST(CONVERT(VARCHAR(8),@TodaysDate,112) AS INT)-CAST(CONVERT(VARCHAR(8),@DOB,112) AS INT) ) / 10000)

This script calculate age using date of birth.
Tested in MS SQL 2008

Categories: SQL

Querying values in XML field - MS SQL 2008

In MS SQL 2008, you can use these script to query a value from xml column.

Check existence. This returns 1 if there is an element in xml
[column].exist('//element-name') = 1

Get element.
[column].query('//element-name')

Get value of element. You can define the type of value.
[column].value('(//element-name)[1]','date')

Categories: SQL

BULK INSERT from CSV file

This is an example of BULK INSERT using CSV file.
This script can be used when you want to create a table using records in CSV file.
And I just noticed that the CSV file should be located in SQL server.
I don't know if there is any way to do that without moving the file to SQL server right now.

USE Safrock
CREATE TABLE #tempTable
(timestamp smalldatetime, mobilenumber varchar(50))
BULK INSERT #tempTable
FROM 'c:\SMSResponses-Reply2.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
SELECT * FROM #tempTable
INSERT INTO Log_Customer_Campaign_SMS
(ReceivedTime, Originator, Recipient, Text)
SELECT
timestamp
,mobilenumber
,'61427200812'
,'2'
FROM #tempTable
DROP TABLE #tempTable

Categories: SQL