An issue selecting date

Hi,

I have a query to get a specific date range, the problem is that the date is stored in the database like this:

February 7, 2014 4:27:32 PM EST

when I do my query like this:

select State, Priority, id, Headline, Submit_Date 
from ClearQuestReport 
where Assigned_To = 'Misty Phillippi' 
and id like '%049%' and (Submit_Date >= '01/01/2014' 
and Submit_Date <= '02/15/2014') 

Open in new window


I know that I have a record with my query criteria but it is not giving me that result because of the date

State                             Priority                                     id                                    headline                                                                          Submit_Date
In_Progress      2-Give High Attention      ent00093049      Clear and Simple Letters to be built Post Go Live      February 7, 2014
4:27:32 PM EST

I am not sure how to make the date changed to pull that record.

Thanks,
Lulu
lulu50Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
What is the data type of the "Submit_Date" column?  Is it a datetime or varchar?
lulu50Author Commented:
yes it is
lulu50Author Commented:
nvarchar(255)    Submit_Date
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

lulu50Author Commented:
It will not compare, unless I changed the data type to datetime
Brian CroweDatabase AdministratorCommented:
That's pretty much the least efficient (nicest way I can say it) way I've ever seen to store a date.  That's equivalent to storing the number 1235 as "one thousand two hundred thirty five".

Unless you want to build a complicated function to parse each part and deal with the time zone you can try this.

DECLARE @DumbDateString NVARCHAR(255) = 'February 7, 2014 4:27:32 PM EST';

SELECT CONVERT(DATETIME, REVERSE(SUBSTRING(REVERSE(LEFT(@DumbDateString, 3) + SUBSTRING(@DumbDateString, CHARINDEX(' ', @DumbDateString), LEN(@DumbDateString))), 5, 255)), 100)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Erick WCommented:
Brian beat me to the answer. Having dates stores in a string format is one of the most challenging things to query on.
Scott PletcherSenior DBACommented:
You've got to convert it to get an accurate comparison.  But then you have to make sure your query doesn't fail because the date is invalid.  I'm using a cross apply just to assign an alias name to the converted/cast date rather than having to repeat the expression in multiple places.  I also changed the comparison to >= and < rather than between, to make sure that time elements don't cause issues with extra rows or two few rows.

The performance will be relatively poor, but you have no choice:

select State, Priority, id, Headline, Submit_Date
from ClearQuestReport
cross apply (
    select case when isdate(Submit_Date) = 0 then null else cast(Submit_Date AS datetime) end as Submit_Date_Converted
) as assign_alias_names
where Assigned_To = 'Misty Phillippi'
and id like '%049%'
and (Submit_Date_Converted >= '20140101' and Submit_Date_Converted < '20140216')
PortletPaulEE Topic AdvisorCommented:
>>"and ( Submit_Date >= '01/01/2014' and Submit_Date <= '02/15/2014' ) "

Please don't assume that MM/DD/YYYY will be recognized as a date, even computers can get confused between that format and DD/MM/YYYY (eg. 04/07/2016 is it in april or july?)

The safest date literal format in SQL Server is YYYYMMDD as used by Scott Pletcher

no points
lulu50Author Commented:
Thank you everyone for your help

Brian - the example that you gave me is great but I need this format (mm/dd/yyyy) instead of this format yyyy-mm-dd


DECLARE @DumbDateString NVARCHAR(255) = 'February 7, 2014 4:27:32 PM EST';

 SELECT CONVERT(DATETIME, REVERSE(SUBSTRING(REVERSE(LEFT(@DumbDateString, 3) + SUBSTRING(@DumbDateString, CHARINDEX(' ', @DumbDateString), LEN(@DumbDateString))), 5, 255)), 100)

output
2014-02-07 16:27:32.000

but what I want is this.

02/07/2015

no need for time.

Thank you
Scott PletcherSenior DBACommented:
The format of the result/output is separate from the internal comparison format.  You change the result format in the SELECT list:

select State, Priority, id, Headline, Convert(varchar(10), Submit_Date, 101) as Submit_Date
from ClearQuestReport
cross apply (
    select case when isdate(Submit_Date) = 0 then null else cast(Submit_Date AS datetime) end as Submit_Date_Converted
) as assign_alias_names
where Assigned_To = 'Misty Phillippi'
and id like '%049%'
and (Submit_Date_Converted >= '20140101' and Submit_Date_Converted < '20140216')
PortletPaulEE Topic AdvisorCommented:
You may always alter the DISPLAY of dates/times using CONVERT(),
or from MS SQL 2012 onward, FORMAT().

e.g. using the format function you no longer have to memorize style numbers

select State, Priority, id, Headline, FORMAT(Submit_Date, 'MM/dd/yyyy') as Submit_Date

        note the 'MM/dd/yyyy' is case sensitive

also see: SQL Server Date Styles (formats) using CONVERT()
Vitor MontalvãoMSSQL Senior EngineerCommented:
lulu50, do you still need help with this question?
lulu50Author Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.