You must Sign In to post a response.
  • Category: .NET

    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

    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
    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
    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
    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
    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]


  • Sign In to post your comments