CODEWITHSUNDEEP
jqueryasp.netsql-server-2008jquery-ui-datepicker
Mervin
Mervin

Reputation: 725

Converting a string into date format in sql server 2008 while running the query in ASP.NET

I am using SqlDataSource to pull in information from a table such that only the records which fall between a specific date range should be displayed in a grid.The date range values are picked up using the Jquery UI datepicker which returns the date as a string.

Now the question is how do I convert that string into a date format so that I can compare it with the date value coming in from the database .

The code I currently have is :

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:LicensingConnectionString %>" 


            SelectCommand="SELECT [School Name], [School City], [School State], LoginName, [Current Sales], Commission, [Pay Period start date], [Pay Period End date] FROM commissions WHERE ([Pay Period start date] &lt;= @Txt_selected_start_date) AND ([Pay Period End date] &lt;= @Txt_selected_end_date)">
            <SelectParameters>
                <asp:parameter  
                    Name="Txt_selected_start_date" Type="String" />
                <asp:Parameter Name="Txt_selected_end_date"  Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

where Txt_selected_end_date and Txt_selected_start_date are the values from the textboxes populated with the dates from the jquery UI date picker.

Any inputs or suggestions would be great !

Upvotes: 1

Views: 1263

Answers (2)

littlechris
littlechris

Reputation: 4184

I have never used SqlDataSource, but this is how to do it in the sql statement

You can use Cast or Convert to convert a string to a date in T-SQL.

Using convert for example

Convert(Datetime, @Txt_selected_end_date, 101)

The last param is date format. 101 is mm/dd/yyyy

Upvotes: 0

Guffa
Guffa

Reputation: 700670

You should change the type of your parameters to datetime instead of string, so that you compare datetime values in the database. Otherwise the database will do the conversion from string to datetime and then you have very little control over it.

Send the values from the datepicker back to the server in the postback, for example by placing them in hidden fields. Then parse the strings into DateTime values in the code behind, and put the values in the parameters.

Upvotes: 2

Related Questions