SQL SERVER

You need to return NULL only if the value of your data is a certain value. How do you do this? There are three different ways. NULLIF DECLARE @1 char (1) SELECT @1 = 'D' SELECT NULLIF (@1 ,'D' ) REPLACE This should not really be used, I just added it here to demonstrate that you can in fact use it. DECLARE @1 char (1) SELECT @1 = 'D' SELECT REPLACE (@1, 'D' ,NULL) CASE With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway. DECLARE @1 char (1) SELECT @1 = 'D' SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END --No else needed SELECT CASE WHEN @1 'D' THEN @1 END And this is how you test for a range. --Null DECLARE @1 char (1) SELECT @1 = 'D' SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END --E DECLARE @1 char (1) SEL...

How to return random results from SQL SERVER

Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code. SQL SERVER 2000 has a neat little function called NEWID() that can help you out. Run the code below from Query Analyzer and keep hitting F5. You will see that the results will be different every time. USE pubs SELECT top 4 * FROM dbo.authors ORDER BY NEWID ()

Date formatting in SQL Server

Declare @d datetime select @d = getdate () select @d as OriginalDate, convert ( varchar ,@d,100) as ConvertedDate, 100 as FormatValue, 'mon dd yyyy hh:miAM (or PM)' as OutputFormat union all select @d, convert ( varchar ,@d,101),101, 'mm/dd/yy' union all select @d, convert ( varchar ,@d,102),102, 'yy.mm.dd' union all select @d, convert ( varchar ,@d,103),103, 'dd/mm/yy' union all select @d, convert ( varchar ,@d,104),104, 'dd.mm.yy' union all select @d, convert ( varchar ,@d,105),105, 'dd-mm-yy' union all select @d, convert ( varchar ,@d,106),106, 'dd mon yy' union all select @d, convert ( varchar ,@d,107),107, 'Mon dd, yy' union all select @d, convert ( varchar ,@d,108),108, 'hh:mm:ss' union all select @d, convert ( varchar ,@d,109),109, 'mon dd yyyy hh:mi:ss:mmmAM (or PM)' union all select @d, convert ( varchar ,@d,110),110, 'mm-dd-yy' union all select @d, convert ( varchar ,@d,...

SQL:Format Data From Numberformat To Timeformat

Sometimes you have data that's in a number format and you need to show it in a time format. Instead of 2.25 you need to show 02:15 In that case you can use a combination of CONVERT and DATEADD Run the examples below to see how it works DECLARE @a DECIMAL (6,2) SELECT @a = 3.00 SELECT @a AS OldValue, CONVERT ( CHAR (5), DATEADD (ss, @a * 3600, 0), 108) AS TimeFormatted SELECT @a = 3.15 SELECT @a AS OldValue, CONVERT ( CHAR (5), DATEADD (ss, @a * 3600, 0), 108) AS TimeFormatted SELECT @a = 3.25 SELECT @a AS OldValue, CONVERT ( CHAR (5), DATEADD (ss, @a * 3600, 0), 108) AS TimeFormatted SELECT @a = 3.75 SELECT @a AS OldValue, CONVERT ( CHAR (5), DATEADD (ss, @a * 3600, 0), 108) AS TimeFormatted

Check If Temporary Table Exists

When working with temporary tables in SQL Server, the common conundrum is often determining whether a temp table exists before performing operations on it. In this blog post, we’ll delve into the nuances of checking the existence of temp tables and explore the potential pitfalls and solutions. The Basics: Local Temp Tables To check if a local temp table exists, one commonly used approach is: IF OBJECT_ID ( 'tempdb..#temp' ) IS NOT NULL BEGIN PRINT '#temp exists!' END ELSE BEGIN PRINT '#temp does not exist!' END This method utilizes the OBJECT_ID function to verify the existence of the temp table in the tempdb database. The Undocumented Second Parameter An alternative way involves an undocumented optional second parameter: IF OBJECT_ID ( 'tempdb..#temp' , 'u' ) IS NOT NULL BEGIN PRINT '#temp exists!' END ELSE BEGIN PRINT '#temp does not exist!' END However, it’s important to no...

Sort A SQL Server Table With CASE Or CHARINDEX

Let's say you have a table with states in your database, you only have 3 values NY, ME and SC. You want to order the result like this: first NY followed by SC and ME last. You can do that in two different ways One: use a case statement in your order by Two: use Charindex in your order by Let's see how that works CREATE TABLE #TEST ( STATE CHAR (2)) INSERT INTO #TEST SELECT 'ME' UNION ALL SELECT 'ME' UNION ALL SELECT 'ME' UNION ALL SELECT 'SC' UNION ALL SELECT 'NY' UNION ALL SELECT 'SC' UNION ALL SELECT 'NY' UNION ALL SELECT 'SC' -- order by using CASE SELECT * FROM #TEST ORDER BY CASE STATE WHEN 'NY' THEN 1 WHEN 'SC' THEN 2 ELSE 3 END --Order by using CHARINDEX SELECT * FROM #TEST ORDER BY CHARINDEX (STATE, 'NY-SC-ME' ) --or without NY since CHARINDEX will return 0 for NY and it will be first SELECT * FROM #TEST ORDER BY CHARINDEX (STATE, 'SC-ME' ...

COALESCE And ISNULL Differences

Run the following block of code -- The result is 7, integer math SELECT 15 / ISNULL ( CONVERT ( INT ,NULL), 2.00) --The result is 7.5, which is correct SELECT 15 / COALESCE ( CONVERT ( INT ,NULL), 2.00) You will see that the result is not the same ISNULL does integer math while COALESCE does not COALESCE correctly promotes its arguments to the highest data type in the expression list. ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int COALESCE looks at 2.00 and 15 and then promotes the integer to decimal Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more Here we have 4 variables and all except for one are null DECLARE @Var1 VARCHAR (20) DECLARE @Var2 VARCHAR (20) DECLARE @Var3 VARCHAR (20) DECLARE @Var4 VARCHAR (20) SELECT @Var4 = 'ABC' --This will return ABC SELECT COALESCE (@Var1,@Var2,@Var3,@Var4)

How To Split column In SQL

Suppose you have a column in a table that has multiple pieces of information in it that I need to break out into various columns. The column is random but the values I need to separate out are the number and the UN number as below: 245 HELIUM, COMPRESSED 2.2 UN1046 I need to separate the 2.2 and the UN1046 into different columns. How do I parse this? CREATE TABLE Inventory (ItemDescription VARCHAR (99)) INSERT Inventory VALUES ( '245 HELIUM, COMPRESSED 2.2 UN1046' ) INSERT Inventory VALUES ( '24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' ) INSERT Inventory VALUES ( '24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' ) INSERT Inventory VALUES ( '24adada5 HEdUM, sdsdsd 446.0 UN9988' ) SELECT RIGHT (ItemDescription, PATINDEX ( '% %' , REVERSE (ItemDescription))-1) AS COL1, LTRIM ( REVERSE ( LEFT ( REVERSE ( LEFT (ItemDescription,( LEN (ItemDescription)- PATINDEX ( '% %' , REVERSE (ItemDescription))))), PATINDEX ( '% %' ...

Formatting Data By Using CHARINDEX And SUBSTRING

Let's say you have names stored in the format [XYZ, ABC] but would like it to be [ABC XYZ] How do you accomplish that in SQL? SQL provides 2 useful functions (CHARINDEX And SUBSTRING) Run the code below to see how they work CREATE TABLE Names (ID INT identity not null,NameField VARCHAR (50), ProperNameField VARCHAR (50)) INSERT INTO Names SELECT 'XYZ, ABC ',NULL UNION ALL SELECT 'Smith, John' ,NULL UNION ALL SELECT 'Jackson, Michael' ,NULL UNION ALL SELECT 'Gates, Bill' ,NULL UPDATE Names SET ProperNameField = SUBSTRING (NameField, CHARINDEX ( ',' ,NameField) + 2, LEN (NameField) - CHARINDEX ( ',' ,NameField)) + ' ' + LEFT (NameField, CHARINDEX ( ',' ,NameField)-1) SELECT * FROM Names

Pad Numbers By Using CASE, LEFT And CONVERT

Let's say you have a table with integer values below 100000 and you want them in the same format with leading zeros. For example 500 has to be 000500 and 1 has to be 000001 How do you do this in SQL server? Below are two ways to accomplish this. CREATE TABLE #ValueTable (value INT) INSERT INTO #ValueTable SELECT 1 UNION ALL SELECT 500 UNION ALL SELECT 4000 UNION ALL SELECT 50000 --Use CASE, LEN and CONVERT to pad the values SELECT value, CASE LEN (value) WHEN 1 THEN '00000' + CONVERT ( VARCHAR ,value) WHEN 2 THEN '0000' + CONVERT ( VARCHAR ,value) WHEN 3 THEN '000' + CONVERT ( VARCHAR ,value) WHEN 4 THEN '00' + CONVERT ( VARCHAR ,value) WHEN 5 THEN '0' + CONVERT ( VARCHAR ,value) ELSE CONVERT ( VARCHAR ,value) END AS Formattedvalue FROM #ValueTable --Use LEFT, LEN and CONVERT to pad the values SELECT value, LEFT ( '000000' ,(6 - LEN (value ))) + CONVERT ( VARCHAR ,value) AS Formattedvalue FROM #ValueTab...

Find all Primary and Foreign Keys In A Database

To find all your foreign and primary keys in your database run the code below. The ouput will return the primary key, primary key table, foreign key, foreign key table. Primary keys that don't have foreign keys will have N/A in the foreign key output USE Northwind SELECT tc.TABLE_NAME AS PrimaryKeyTable, tc.CONSTRAINT_NAME AS PrimaryKey, COALESCE (rc1.CONSTRAINT_NAME, 'N/A' ) AS ForeignKey , COALESCE (tc2.TABLE_NAME, 'N/A' ) AS ForeignKeyTable FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

Use OBJECTPROPERTY To Find Out Table Properties

How many times did you open up a table in design view to find out if a table has a delete trigger a clustered index an identity column a primary key a timestamp a unique constraint etc etc? There is an easier way to find out all this info by using the OBJECTPROPERTY function You will have to get the object id from the table to pass it into the OBJECTPROPERTY function. To get the object id you can use the OBJECT_ID function. Run the code below in the Northwind database to see how it works USE Northwind DECLARE @objectID INT SELECT @objectID = OBJECT_ID ('customers') select OBJECTPROPERTY (@objectID, 'TableDeleteTrigger' ) AS 'Table Has A Delete Trigger' , OBJECTPROPERTY (@objectID, 'TableHasClustIndex' ) AS 'Table Has A Clustered Index' , OBJECTPROPERTY (@objectID, 'TableHasIdentity' ) AS 'Table Has An Identity Column' , OBJECTPROPERTY (@objectID, 'TableHasPrimaryKey' ) AS 'Table Has A Primary Key' , OBJEC...

SQL String Functions Tutorial

SQL Server offers a plethora of string functions that empower developers to manipulate and transform text data efficiently. In this comprehensive guide, we will explore various string functions, unraveling their functionalities with examples. 1. CHARINDEX The CHARINDEX function retrieves the index of a specified character within a given string. The syntax is as follows: ` SELECT CHARINDEX ( 'S' , 'MICROSOFT SQL SERVER 2000' ) as Result ; Result: 6 2. LEFT The LEFT function extracts a specified number of characters from the left side of a string. Here’s how it works: ` SELECT LEFT ( 'MICROSOFT SQL SERVER 2000' , 4 ) as Result ; Result: MICR 3. RIGHT Conversely, the RIGHT function extracts characters from the right side of a string: ` SELECT RIGHT ( 'MICROSOFT SQL SERVER 2000' , 4 ) as Result ; Result: 2000 4. LEN To determine the length of a string, the LEN function comes in handy: ` SELECT LEN ( 'MICROSOFT SQL ...

To Get The Current Identity Value From A Table

Let's first create our two simple tables CREATE TABLE TestOne (id INT identity,SomeDate DATETIME ) CREATE TABLE TestTwo (id INT identity,TestOneID INT ,SomeDate DATETIME ) --Let's insert 4 rows into the table INSERT TestOne VALUES ( GETDATE ()) INSERT TestOne VALUES ( GETDATE ()) INSERT TestOne VALUES ( GETDATE ()) INSERT TestOne VALUES ( GETDATE ()) Here are diffrent ways to check for the current value --1 @@IDENTITY SELECT @@IDENTITY --this returns 4 --2 DBCC CHECKIDENT DBCC CHECKIDENT (TestOne, NORESEED) after running DBCC CHECKIDENT the message returned is Checking identity information: current identity value '4', current column value '4'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. --3 MAX function SELECT MAX (id) FROM TestOne you can also check with the MAX function but this is not recommended becuase you might get some other identity value that is not yours but from a different user --4 TOP 1 an...

How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME ) INSERT #TestDeleteNull VALUES ( GETDATE ()) INSERT #TestDeleteNull VALUES ( GETDATE ()) INSERT #TestDeleteNull VALUES (NULL) --Check what's in the table SELECT * FROM #TestDeleteNull --This won't work DELETE #TestDeleteNull WHERE SomeDate = NULL --(0 row(s) affected) --This is the way DELETE #TestDeleteNull WHERE SomeDate IS NULL --(1 row(s) affected) --Check again SELECT * FROM #TestDeleteNull

SQL Case Statements

SQL Case statement can be used in 2 forms: SQL Case statement with simple expression to compare and get results. SQL Case statement with search or comparison expression to get results. 1. SQL Case statement with simple expression: You can use default Northwind Database to test the functionality and syntax of SQL case statement. Syntax: USE NORTHWIND SELECT 'Title' = CASE TITLEOFCOURTESY WHEN 'MS.' THEN 'MISSUS' WHEN 'MRS.' THEN 'MISTRESS' WHEN 'MR.' THEN 'MISTER' WHEN 'DR.' THEN 'DOCTOR' ELSE '' END , TITLEOFCOURTESY, FIRSTNAME, LASTNAME FROM EMPLOYEES Output: Title TitleOfCourtsey FirstName LastName Missus Ms. Nancy Davolio Doctor Dr. ...

SQL SubSelect-SubQueries

A sub query or sub select is a select statement that returns a single value output result and it can be nested inside other subquery or any SELECT , INSERT , DELETE OR UPDATE statement . Example: USE NORTHWIND SELECT P.PRODUCTNAME, ( SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORYID = P.CATEGORYID) FROM PRODUCTS P Subquery used in the above example returns the category name of each product in every tuple. Example: SELECT P.PRODUCTNAME, P.UNITPRICE, P.CATEGORYID FROM PRODUCTS P WHERE P.PRODUCTID = ( SELECT PRODUCTID FROM PRODUCTS WHERE PRODUCTNAME= 'VEGIE-SPREAD' ) Here subquery returns product id as single value to the main SQL query. Example: SELECT C.CATEGORYNAME, ( SELECT TOP 1 P.PRODUCTNAME FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC ), ( SELECT TOP 1 P.UNITPRICE FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC ), ( SELECT MAX (P.UNITPRICE) FROM PRODUCTS P WHERE P.C...

SQL Count Distinct

SQL Count Statement is used to count the total number of records saved in a particular table. SQL Count Statement returns total records including duplicates. SQL Count (*) Statement does not take any other argument such bas column name to evaluate the result. It counts all columns whether they have duplicates or nulls. E.g.: USE NORTHWIND SELECT COUNT (*) FROM ORDERS Above SQL Query will return 830. SQL Count (ALL [Column Name]) Statement returns the number of records including duplicates but excluding null value columns E.g.: SELECT COUNT (ALL CUSTOMERID) FROM ORDERS SQL Count (DISTINCT [Column Name]) Statement returns unique, non null records. E.g.: SELECT COUNT (DISTINCT CUSTOMERID) FROM ORDERS Above SQL Query will return 89.

SQL Server 2005 Cast-Convert Date Time Format

Using CAST function in SQL Server 2005: Syntax: CAST (expression as datatype) CAST function accepts expression name as column name or any SQL function that return expression or value and declare the datatype of the expression into which you want to cast/convert it. Following are the SQL Server 2005 query syntax to cast DateTime into varchar: E.g.: USE Pubs select cast(pubdate as varchar) from titles or select cast(getdate() as varchar) Following are the MS SQL Server 2005 query Syntax to convert Time format: SQL query to convert Time format into hh:mm:ss: select convert(varchar, getdate(), 108) SQL query to convert Time format into hh:mi:ss:mmm(24h): select convert(varchar, getdate(), 114) You can use datetime type column name instead of getdate() function. E.g.: USE PUBS select convert(varchar,pubdate,108) from titles or select convert(varchar,pubdate,114) from titles

How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

Let's say you have a proc like this CREATE PROC prUpdateTable @Salesman int = - 1 AS .......... If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do? You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this UPDATE table SET Column = COALESCE ( NULLIF ( @variable ,- 1 ), Column ) Here is some code to demonstrate that CREATE TABLE #foo ( id int , salesman int ) INSERT #foo VALUES ( 1 , 1 ) INSERT #foo VALUES ( 2 , 1 ) INSERT #foo VALUES ( 3 , 1 ) INSERT #foo VALUES ( 4 , 1 ) SELECT * FROM #foo ------------- 1 1 2 1 3 1 4 1 DECLARE @salesman int SELECT @salesman = 5 --Column value will change to 5 UPDATE #foo SET salesman = COALESCE ( NULLIF ( @salesman ,- 1 ), salesman ) WHERE ID = 1 -...

How To Use Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause. For self join in sql you can try the following example: Create table employees: emp_id emp_name emp_manager_id 1 John Null 2 Tom 1 3 Smith 1 4 Albert ...

Delete Duplicate rows from the table.

- March 28, 2008

Delete Duplicate rows from the table. Suppose there is a table called "EmployeeTable" which have some duplicate records. There is a three way to delete the duplicate rows. First way to delete duplicate rows : Select distinct * into Emp_Temp_Table from EmployeeTable In the above line we are inserting all the distinct row of the "EmployeeTable" to another table "Emp_Temp_Table" (Emp_Temp_Table will create automatically when you use the above query.) Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table). drop table EmployeeTable sp_rename 'Emp_Temp_Table',EmployeeTable' Then Delete the orginal table and rename the clone tab...

How To Copy Data and Structure of One Table To Another New Tables

- March 27, 2008

select * into newtable from originaltable where 1=1

what Is Cursor

The basic syntax of a cursor is: DECLARE @AuthorID char(11) DECLARE c1 CURSOR READ_ONLY FOR SELECT au_id FROM authors OPEN c1 FETCH NEXT FROM c1 INTO @AuthorID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @AuthorID FETCH NEXT FROM c1 INTO @AuthorID END CLOSE c1 DEALLOCATE c1 The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set. The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cu...

What is Triggers

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created. The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached. SET NOCOUNT ON CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10)) go CREATE TRIGGER tr_Source_INSERT ON Source FOR INSERT AS PRINT GETDATE() go INSERT Source (Sou_Desc) VALUES ('Test 1') -- Results -- Apr 28 2001 9:56AM

Using EXISTS

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this. Here's a simple example from the pubs database using EXISTS: if EXISTS (select * from authors where au_id = '172-32-1176') Print 'Record exits - Update' ELSE Print 'Record doesn''t exist - Insert' The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.

Returning Complex Data from User-Defined Functions

Returning multiple values from a UDF can be done fairly easily in SQL Server, but we must return those values as a virtual table. The following very simple ParseEmail() function accepts an email address as an argument, parses it, and returns the username and domain separately: create function EmailParse (@email varchar (1000)) returns @t table (UserName varchar (20), Domain varchar (20)) as begin declare @i int select @i = charindex( '@' , @email,1); if (@i > 1) insert into @t values ( left (@email,@i-1), substring (@email,@i+1,20)) else insert into @t values ( null , null ) return end how to use select username, domain from EmailParse('test@test.com')

What is Stored procedure?

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs. They allow modular programming. They allow faster execution. They can reduce network traffic. They can be used as a security mechanism.

What are the differences between UNION and JOINS?

A join selects columns from 2 or more tables. A union selects rows.

How many types of Joins?

oins can be categorized as: Inner joins (the typical join operation, which uses some comparison operator like = or ). These include equi-joins and natural joins. Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables. Outer joins. Outer joins can be a left, a right, or full outer join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause: LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matchin...

Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms

# Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

SELECT CEILING (7.1)

Write a query to convert all the letters in a word to upper case