Working with date time in sql server

It is common to handle the date time in sql server. Common scenario where date time is used are :

(1) Get the records between two dates (Start date and End date).
(2) Fetch information according to condition where information should be in specific dates.

There are come examples given below where user may check the format of date.

Increase the date, month, year in datetime with query

We need to change the date in varchar format and then need to tell the format of date time in which we want to show the date.

We can increment the day of the specific date by adding the 1 in that date. check example below:

select convert(varchar, getdate() + 1, 1)

It will have output: 09/07/10

To increase the month and year we need to concatenate the day month and year separately.

We can concatenate the format with below query:

				
select right('0' + rtrim(month(getdate())),2) + '/' 
+ right('0' + rtrim(day(getdate())),2) + '/' + rtrim(year(getdate()))

It will have the output: 09/06/2010

If we want to increase the month then we will add the increment amount in front o that variable.

				
select right('0' + rtrim(month(getdate())+2),2) + '/' 
+ right('0' + rtrim(day(getdate())),2) + '/' + rtrim(year(getdate()))

We have added in month. Check the bold character. now it will have the output: 11/06/2010

Replace function use with datetime

In some conditions we need to remove the "/" sign from the date. Then we may use the Replace function. In replace function we will provide the value from where we need to remove the "/" character. We will write the query like below:

				
select replace(convert(varchar, getdate(),101),'/','')

In this query we are replacing the "/" sign from the date with empty space. It will have output: 09062010

If we want to remove the "/" from date and ":" (colon) from the time then we will write the following query:

select replace(convert(varchar, getdate(),101),'/','') + 
replace(convert(varchar, getdate(),108),':','')

With 108, first we are getting the time and afterward replacing the ":" with the empty space.

Search records between two dates

Syntax:

select * from table-name where CreatedOn between 'Start date' and 'End date'



select * from tbl_articles where CreatedOn between '11/06/2010' and '11/07/2010'

Nov and 7 Nov records also.

Search records of specific date

SELECT * FROM tbl_article WHERE CONVERT(CHAR(10),CreatedOn,120) = '2010-04-09'

It will return the all records where date is equal to '2010-04-09'

Click here to go on website