Apart from Java technology we need to have a good hold on database concepts as a Java Techie. No real time project is complete without a database. Many a time we come across recurring database logics and queries in small or big project. So in this section we will learn about solutions of various recurring problems as part of Database Logics & Queries.
How to get rows starting from 10 to 30 from database ?
If you are using Hibernate to get records
OR
If you are using mySQL
How to update Column datatype in SQL Server?
How to add additional Column manually in SQL Server database?
How to convert Date formats of SQL Server as per our need?
Suppose we want Date format other than provided by SQL Server by default, we can use the following query :
Converting DATETIME to VARCHAR :
Converting VARCHAR to DATETIME :
Converting VARCHAR to DATETIME :
here 101 is the style of DATE in SQL Server. It represents date in mm/dd/yyyy(’05/04/2013′) format.
Similarly there are many date styles in SQL Server. Useful when we need different format to show in UI. Also useful when there is an exception in your java code because of the different date formats.
What Database Logics & Queries will you apply to convert Text to Integer in SQL Server?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
OR
What is the difference between CAST & CONVERT ?
CONVERT is SQL Server specific, CAST is ANSI.
Convert provides more flexibility than Cast. So if you are trying to know which one to use, I would suggest Cast unless you have a specific reason to use Convert. As a suggestion;
Dates, times : CONVERT
Decimals, numerics : CAST
What are commonly used DATE & TIME functions in SQL Server ?
-
GETDATE()
- returns exact date time from the system. It does not accept any parameter.
-
DATEADD()
- -used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.
DATEADD(datepart, number, date) :
-
DATEPART()
- used when we need a part of date or time from a datetime variable. We can use DATEPART()only with select command.
-
DATEDIFF()
- is very common function to find out the difference between two DateTime elements.
-
DATENAME()
- most useful function to find out the date name from the datetime value.
-
DAY()
- to get the day from any date time object.
-
MONTH()
- to get the month from any date time object.
-
YEAR()
- to get the year from any date time object.
How to apply search query logic in Hibernate using ‘like’ irrespective of case-sensitiveness ?
OR
Suppose a date is given. What Database Logics & Queries will you apply to find the day of week for the given date ?
Suppose a date 2014-01-15 is given. We are interested in getting date of Friday in this week. we can do this by :
above query will result in 2014-01-17 00:00:00.000
Here in DATEADD function’s last parameter 4 represents day of week.
values starts from 0 to 6 for Monday to Sunday respectively.