Category: .NET

31 Aug 2014 06:07

Joined:	23/08/2010
Level:	Gold
Points:	553

Points:	3

Select Query for Dates

EmpID,Name,PermissionDateFrom,PermissionDateTo,PermissionHours
1,TestUser,01-01-2014,07-02-2014,01:00 Hrs

From the above table content, I need to write a query that TestUser have permission the date 10-01-2014.

The Dates from jan 1 to Feb 7. So 10th of jan is between the dates.

I give input as 10Jan, it should fetch the above row. so i can get the 01:00 Hrs of permission as result.

#750646

31 Aug 2014 07:31

Joined:	06/01/2008
Level:	Gold
Points:	2968

Points:	2

try below code :


select * from TableName where '10-01-2014' between permissionDateFrom and permissionDateTo

If it helps you then Rate this.

Best Regards,
Manoranjan Sahoo
http://www.dotnetsquare.com
Blog: http://blog.msahoo.net

#750648

31 Aug 2014 13:33

Joined:	08/07/2010
Level:	Gold
Points:	6925

Points:	3

Please try the below code and for more information about how to convert from datetime to varchar format and from datetime format to varchar refer below links:

select * from [Table]
where convert(date,'10-01-2014',105) between convert(date,PermissionDateFrom,105) and convert(date,PermissionDateTo,105)

In the above code we are passing three parameters to the convert function. first parameter is indicating that we need the date from the convert function as output after conversion. I am assuming that both the columns PermissionDateFrom,PermissionDateTo are of varchar type. the number 105 specifies that the date is in dd-mm-yyyy format.

w3schools.com/sql/func_convert.asp

stackoverflow.com/questions/22512480/convert-varchar-column-to-datetime-format-sql-server

Please try the code and it should work fine.

Miss. Jain
Microsoft Certified Technology Specialist in .Net

#750657

31 Aug 2014 19:13

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi,

If you want to get the result between 2 dates then use between operator between 2 dates to get your desired output.


Select * from tablename
Where datecol between @fromdate and @todate

Based on your date field datatypes you can convert into appropriate data type.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#750671

31 Aug 2014 21:04

Joined:	05/11/2013
Level:	Gold
Points:	1551

Points:	2

Hi,

Use the below query to select the records between two specified dates-

Select * from tblname where datereq between @fromdatre and @todate

Thanks,
Ashutosh Jha
http://tricksroad.com

#750675

31 Aug 2014 22:23

Joined:	13/06/2007
Level:	Gold
Points:	21314

Points:	2

It is bit simple just use Between operator and you can get what you want


SELECT PermissionHours
FROM Table1
WHERE '10-01-2014' between PermissionDateFrom and PermissionDateTo

hope it helps

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

Select Query for Dates

Email subscription

About dotnetspider.com

Quick Links

General

Services