Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




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.

Sr. no.
Query to check the date
Output
1 select convert(varchar, getdate(), 1)       09/06/10
2 select convert(varchar, getdate(), 2)   10.09.06
3 select convert(varchar, getdate(), 3)   6/9/2010
4 select convert(varchar, getdate(), 4)   06.09.10
5 select convert(varchar, getdate(), 5)   06-09-10
6 select convert(varchar, getdate(), 6)   06 Sep 10
7 select convert(varchar, getdate(), 7)   Sep 06, 10
8 select convert(varchar, getdate(), 10)   09-06-10
9 select convert(varchar, getdate(), 11)   10/09/06
10 select convert(varchar, getdate(), 101)   9/6/2010
11 select convert(varchar, getdate(), 102)   2010.09.06
12 select convert(varchar, getdate(), 103)   6/9/2010
13 select convert(varchar, getdate(), 104)   06.09.2010
14 select convert(varchar, getdate(), 105)   06-09-2010
15 select convert(varchar, getdate(), 106)   06 Sep 2010
16 select convert(varchar, getdate(), 107)   Sep 06, 2010
17 select convert(varchar, getdate(), 108)   13:28:17
18 select convert(varchar, getdate(), 109)   Sep 6 2010 1:28:17:077PM
19 select convert(varchar, getdate(), 110)   09-06-2010
20 select convert(varchar, getdate(), 111)
2010/09/06

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

If we want to search records between two dates the it will be like:

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'

Share this article   |    Print    |    Article read by 5066 times
Author:
Rohit kakria
I am software developer
Related Articles:
Related Interview Questions: