April 23, 2015 at 10:44 am
I am working on a delete statement that has 2 separate conditions. I have to delete from my orders table orders with a date within the range of 10-10-1996 and 10-25-1996 and with a shipped date via uninted package. I am a little confused of the syntax for this. I tried a statement but the syntax is incorrect because when i tested it it says incorrect syntax near the = sign so can someone steer me in the right direction? Thanks
here is the statement i made up
delete LMOrders where OrderDate between '10-10-1996 and 10-25-1996' and ShippedDate ='Uninted Package'
April 23, 2015 at 10:55 am
mistylove98 (4/23/2015)
I am working on a delete statement that has 2 separate conditions. I have to delete from my orders table orders with a date within the range of 10-10-1996 and 10-25-1996 and with a shipped date via uninted package. I am a little confused of the syntax for this. I tried a statement but the syntax is incorrect because when i tested it it says incorrect syntax near the = sign so can someone steer me in the right direction? Thankshere is the statement i made up
delete LMOrders where OrderDate between '10-10-1996 and 10-25-1996' and ShippedDate ='Uninted Package'
Try
delete from LMOrders
where OrderDate between '10-10-1996' and '10-25-1996'
and ShippedDate ='United Package'
April 23, 2015 at 11:03 am
Not quite how I would do it, but give this a try:
delete from
dbo.LMOrders
where
OrderDate between '19961010' and '19961025'
and ShippedDate ='Uninted Package';
April 23, 2015 at 11:39 am
I get this error when i try it that way
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
April 23, 2015 at 11:50 am
mistylove98 (4/23/2015)
I get this error when i try it that wayMsg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Just guessing, but could it be because 'United Package' is a company, not a date? That criteria seems to be comparing apples to motorcycles.
You're either looking for a specific date something shipped or anything where the ShippingCompany (whatever you happen to call it in your data strcutre) = 'United Package'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 23, 2015 at 11:59 am
mistylove98 (4/23/2015)
I get this error when i try it that wayMsg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Is ShippedDate actually a shipping method?
April 23, 2015 at 12:15 pm
Thanks all for the help I feel kinda stupid now. Yes it should not have been shipped date it should have been
delete LMOrders where OrderDate between 10-10-1996 and 10-25-1996 and ShipVia = 2
because 2 is the company united package
but now i ran into a problem where it says 0 rows affected when i know there are 6 rows with this data
April 23, 2015 at 12:19 pm
mistylove98 (4/23/2015)
Thanks all for the help I feel kinda stupid now. Yes it should not have been shipped date it should have beendelete LMOrders where OrderDate between 10-10-1996 and 10-25-1996 and ShipVia = 2
because 2 is the company united package
but now i ran into a problem where it says 0 rows affected when i know there are 6 rows with this data
And you know this because you have run a select query with the exact same criteria?
April 24, 2015 at 2:58 am
mistylove98 (4/23/2015)
Thanks all for the help I feel kinda stupid now. Yes it should not have been shipped date it should have beendelete LMOrders where OrderDate between 10-10-1996 and 10-25-1996 and ShipVia = 2
because 2 is the company united package
but now i ran into a problem where it says 0 rows affected when I know there are 6 rows with this data
The problem is with the way you are specifying your dates. Lynn has already mentinoed it previously.
They need to be '19961010' and '19961025' (with the quotes).
As you've written it above (without quotes) 10-10-1996 will be evaluated as an arithmetic expression resulting in the value -1996, converting this to a date gives the "15 July 1894" . Not quite what you meant I'm sure 🙂
April 28, 2015 at 3:12 pm
Thank you for clarifying for me
April 29, 2015 at 7:47 am
Because you're new to this, I'd like to add one final note about filtering on dates. If OrderDate column is a datatype that contains both date + time (ie: DateTime or SmallDateTime), then use the following syntax instead of the BETWEEN clause. Because, for example, the expression BETWEEN '10-10-1996' and '10-25-1996' would cutoff at 10-25-1996 12:00AM and not include 10-25-1996 10:25AM, so you would instead want to filter rows where OrderDate < 1996-10-26.
delete LMOrders
where OrderDate >= '19961010'
and OrderDate < '19961026'
and ShippedDate ='Uninted Package';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 30, 2015 at 2:57 am
For easier readability, I always write my hard code dates as 'yyyy-mm-dd'.
April 30, 2015 at 6:50 am
Michael Meierruth (4/30/2015)
For easier readability, I always write my hard code dates as 'yyyy-mm-dd'.
99999999 is always assumed by SQL and most programming languages to be in ISO standard YYYYMMDD format. But 2015-05-12 could be interpreted as YYYY-MM-DD or YYYY-DD-MM depending on the RDMS, application software, or localization settings.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 30, 2015 at 7:04 am
Eric M Russell (4/30/2015)
Michael Meierruth (4/30/2015)
For easier readability, I always write my hard code dates as 'yyyy-mm-dd'.99999999 is always assumed by SQL and most programming languages to be in ISO standard YYYYMMDD format. But 2015-05-12 could be interpreted as YYYY-MM-DD or YYYY-DD-MM depending on the RDMS, application software, or localization settings.
As far as I know, the format YYYY-MM-DD is always interpreted as such independent of the localization setting.
April 30, 2015 at 7:35 am
Michael Meierruth (4/30/2015)
Eric M Russell (4/30/2015)
Michael Meierruth (4/30/2015)
For easier readability, I always write my hard code dates as 'yyyy-mm-dd'.99999999 is always assumed by SQL and most programming languages to be in ISO standard YYYYMMDD format. But 2015-05-12 could be interpreted as YYYY-MM-DD or YYYY-DD-MM depending on the RDMS, application software, or localization settings.
As far as I know, the format YYYY-MM-DD is always interpreted as such independent of the localization setting.
As far as runtime SQL parameters go and working with Date or DateTime datatypes, it probably doesn't matter. However, if encoding "date" values in VarChar columns (bad idea!), then definately YYYYMMDD.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply