Bala Singam

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

SQL statement to update top N records

update daily_sales
set is_calc='Y'
from daily_sales
inner join (select top 100 * from daily_sales where product_category='CA') a
on a.unique_record_id=daily_sales.unique_record_id
and a.milestone_code=daily_sales.milestone_code

Source is SQLServerCentral

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

Simple statement to delete record a table with condition from other related table.

delete prod
from excp_amended_daily_sales_deposits prod
inner join excp_amended_daily_sales ds
on ds.id=prod.id
where  ds.status <> 'Active'
and ds.reason_code='51'

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

SELECT  SERVERPROPERTY('productversion'),
            SERVERPROPERTY ('productlevel'),
             SERVERPROPERTY ('edition')

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

Yesterday I been requested by one of our client to write a database back up script so that they can schedule it as SQL Server job. As usual i google it and found plenty of solutions but I was looking for something simple and workable. Found it here

This is one line T-SQL code to back up any given database however I added 2 extra line code to have the date on the back up file name.

declare @filename as varchar(100)
SET @fileName = 'd:\' + 'Skanda' + '_' + CONVERT(VARCHAR(20),GETDATE(),112)</code> + '.BAK'
BACKUP DATABASE Skanda TO DISK = @fileName

The a backup file with following format will created Skanda_20100214.BAK

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

This small tutorial is to show how to get particular date part of from a date value field in SQL Server query.

select sales_date,
Day(sales_date) as Day,
Month(sales_date) as Month,
Year(sales_date) as Year,
Day(getDate()) as Current_Day,
Month(getDate()) as Current_Month,
Year(getDate()) as Current_Year
from sales_detail

Result

In above query my date field is sales_date , using Day , Month and Year function i can get the particular date part by passing sales_date as parameter. This is to get date part value from a particular field value.

To get date part from current date is almost same only passing the current date as parameter. To get current date use getDate() function which will return the current date.