date field select help

Help, I've got a field in this format that I can't break apart.

05/30/2005 00:00 00

I need to select records with this field greater than 02/01/2006 without disturbing the field, can i do this select in SQL ? Even with the hours/seconds in there?

Thanks,

Scooter

select Convert(datetime, STUFF ('05/30/2005 00:00 00',17,1,':'), 101)

from the inside out. . .
replace the 17th character in a given string with a ':'
use convert function to change to a datetime, interpreting the output of the stuff with format 101

|||But how do i select the records greater than 02/01/2006?|||select * from theTable
where Convert(datetime, STUFF (theDateField,17,1,':'), 101) >
convert(datetime, '02/01/2006', 101)|||

How about:

SELECT *

FROM Table1

where cast(substring('05/30/2005 00:00 00',0,11) as datetime) > '05/30/2005'

It's a bit simpler. You don't need to convert the constant '02/01/2006' to a datetime as it is recognised as a valid date time format. Simply replace '05/30/2005 00:00 00' with your field name.

HTH

For more SQL Tips, check out my blog:

|||

doesn't that depend on the localization settings?

in the states 12/1/2006 is December first 2006 . . . but in the uk its January first. . . isnt it?

|||Yes, much simpler, Thank you very much to both of you !!

Date "value" not working on INSERT Query

Hi,

The following INSERT query works in all aspects apart from the date value:

String InsertCmd =string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);

The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").

In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.

What am I doing wrong? Thanks very much.

Regards

Gary

That is because C# replace {1} with txtPaymentDate.Text, so the insert statement is changed to :

INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES (...,13/04/2006,...,...)

Where '...' stand for other texts. If you perform this command in Query Analyzer, you'll find 1900-01-01 is inserted instead of you expected value, as SQL needs a char value for datetime data type, not just the formated date without quotes. What we need to do is embed the date in a pair of quotes, so try to write your insertCmd as:

String InsertCmd =string.Format("SET DATEFORMAT dmy;INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},'{1}',{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);

That works in my testing.

|||Google "parameterized sql query".|||

Thanks, Guys - you confirmed that I was on the right track.

'Found I had a combination of issues. First the issue the Iory Jay pointed out - and I had tried (putting the single quotes around the {1} parameter). Second a MM/dd/yyyy versus dd/MM/yyyy format problem.

I'm in New Zealand, which uses dd/MM/yyy format - and it's seems incredibly hard to solve these sorts of problems without resorting to brute-force programming - which I've had to do.

I guess, someday, I'll find the elegant way to convert from one format to the other - and in the mean time I'll continue to shake my head in wonder that software isn't reverse-engineered to make learning to use it easy - at least for straightforward things like referencing a control on a formview (FormView1.FindControl("controlname") is hardly quick and easy is it?), managing default values in DropDownLists (What a mission!), creating a link to another page (Response.Redirect? Come on!) and inserting a date into a database!

Regards

Gary