Last post Jan 20, 2016 02:20 PM by PatriceSc
Jan 19, 2016 04:48 PM|ROHITJGC|LINK
I need to write a query like below in C#
Select * from Tablename where [carpark].[dbo].[PERDATA].DATE_ISSUED Between 11/04/1997 and 26/06/1998
so date will be in dd/mm/yyyy format in the sql table date field is of type datetime
Jan 19, 2016 05:12 PM|PatriceSc|LINK
Use rather SQL parameters:
So you'll have something such as:
Select * from Tablename where [carpark].[dbo].[PERDATA].DATE_ISSUED Between @Start and @End. And then you'll provide the date values for those parameters. This way you never have to care about writing down those date using a format suitable for SQL Server.
To be on the safe side SQL Server side doesn't care about the format but about the value. I meant that a single date stored in SQL Server could be shown using multiple formats because each country have its own. Still inside the db this is the same value.
Jan 19, 2016 05:17 PM|a2h|LINK
If you have values in different format then you can convert it using DateTime.ParseExact or Convert Method
//If you have values in different dateformat then you can convert it
string date1 = "11/04/1997";
DateTime formattedtime1 = DateTime.ParseExact(date1, "dd/MM/yyyy", CultureInfo.InvariantCulture);
//Get the value here
string date2 = "26/06/1998";
DateTime formattedtime2 = DateTime.ParseExact(date2, "dd/MM/yyyy", CultureInfo.InvariantCulture);
Jan 20, 2016 12:38 AM|oned_gk|LINK
Maybe your asp.net and sql has deferent datetime format.
Use parameterize query like all above suggestions,
So, you only need to declare datetime varibale in asp.net then pass it.
If there is a time value in date_issued , you can cast it as a date
Select * from Tablename where CAST([carpark].[dbo].[PERDATA].DATE_ISSUED as Date) Between @datestart and @dateend
Jan 20, 2016 01:53 PM|ROHITJGC|LINK
I am trying to do a query to get records between two dates like
Select * from TableName where [carpark].[dbo].[PERDATA].DATE_ISSUED Between '11/11/1997 00:00:00' and '11/01/1998 00:00:00'
DATE_ISSUED is of Datetime datatype , date will be supplied from textbox in dd/mm/yyyy format what is the best way to do this ,using an inline query.
if I enter it as dd/mm/yyyy sql query reads it as mm/dd/yyyy
Jan 20, 2016 02:13 PM|Rion Williams|LINK
You mention that the date would be entered in using the 'dd/MM/yyyy' format. If that is the case, then what you would want to do is actually pass DateTime objects as parameters into your query and executing it that way (using parameterization).
You can see an example of this below :
using(var connection = new SqlConnection("Your Connection String"))
// Build your query (notice the parameters)
var query = "SELECT * FROM TableName WHERE [carpark].[dbo].[PERDATA].DATE_ISSUED BETWEEN @Start AND @End";
using(var command = new SqlCommand(query,connection))
// Parse your DateTime values using the proper format as parameters
var start = DateTime.ParseExact(StartTextBox.Text,"dd/MM/yyyy", null);
var end = DateTime.ParseExact(EndTextBox.Text,"dd/MM/yyyy", null);
// Add your parameters to the query
// Get your results within a reader (or through some other approach)
using(var reader = command.ExecuteReader())
// Read here
Using parameters will not only protect you from nasty things like SQL Injection attacks, but it will also allow .NET to properly format your date time objects into the appropriate SQL for the queries being executed.
Jan 20, 2016 02:20 PM|PatriceSc|LINK
Use Between '1997-11-11' and '1998-01-11'
The YYYY-MM-DD format doesn't depend on SQL Server settings :
set dateformat dmy
set dateformat mdy
shows 1 or 11 for the first column but always 11 for the second column.
I believe you asked the same question previously and I suggested to use parameters and values using their native value (ie here as DateTime values). This way the right thing is done for you. It helps also to avoid SQL injection attacks.
The problem is that programming languages are allowing to give values in an unambigious way. But keep in mind that this is very special to developers and if I'm showing a string such as 01/02/2016 or even 1,234 you have basically no way to know the correct
value if I'm not telling you which country convention should be applied (it could be January 2 or February 1st and 1234 or 1.234 depending on the country convention).
Edit: so it was
http://forums.asp.net/p/2082996/6011947.aspx?Querying+between+Date+ranges where you already had answers. Please avoid to post the same question multiple times.