April 4, 2012 at 2:31 pm
I need to filter results of a query by Start and End Date. A simple select * from TICKET where createDate > 2012-04-02 does not work. Even cutting pasting the precise format from the table and into the where clause yields error.
How do you create a >= and <= filter against a datetime field?
---DDL
create table TICKET (
ticketid int
, createDate datetime
)
insert into TICKET
values
(1, getdate())
,(2, getdate()-1)
,(3, getdate()-2)
,(4, getdate()-3)
,(5, getdate()-4)
,(6, getdate()-5)
,(7, getdate()-6)
,(8, getdate()-7)
,(9, getdate()-8)
,(10,getdate()-9)
,(11,getdate()-10)
April 4, 2012 at 2:38 pm
You have to either use a datetime variable or put your string representation inside single quotes.
select * from TICKET where createDate > '4/1/2012'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2012 at 2:51 pm
Aha. I thought I'd covered that ground...but only with double quotes. Thanks.
April 4, 2012 at 2:55 pm
"where createDate > 2012-04-02" becomes the integer 2006, "where createDate > 2006" and the 2006 gets cast to datetime as 1905-06-30 00:00:00.000, so it's the same as "where createDate > '1905-06-30'".
April 5, 2012 at 2:41 am
Sean Lange (4/4/2012)
You have to either use a datetime variable or put your string representation inside single quotes.
select * from TICKET where createDate > '4/1/2012'
Is it 4th Jan 2012 or 1st Apr 2012?
For string dates I would recommend to use ISO format: YYYY-MM-DD (or juts YYYYMMDD)
or "medium" format: 'DD MMM YYYY'
April 5, 2012 at 7:17 am
Eugene Elutin (4/5/2012)
Sean Lange (4/4/2012)
You have to either use a datetime variable or put your string representation inside single quotes.
select * from TICKET where createDate > '4/1/2012'
Is it 4th Jan 2012 or 1st Apr 2012?
For string dates I would recommend to use ISO format: YYYY-MM-DD (or juts YYYYMMDD)
or "medium" format: 'DD MMM YYYY'
Good point Eugene and one I all to frequently forget when posting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 5, 2012 at 8:14 am
Sean Lange (4/5/2012)
Eugene Elutin (4/5/2012)
Sean Lange (4/4/2012)
You have to either use a datetime variable or put your string representation inside single quotes.
select * from TICKET where createDate > '4/1/2012'
Is it 4th Jan 2012 or 1st Apr 2012?
For string dates I would recommend to use ISO format: YYYY-MM-DD (or juts YYYYMMDD)
or "medium" format: 'DD MMM YYYY'
Good point Eugene and one I all to frequently forget when posting.
i must have been doing this to long as i write YYYY-MM-DD when ever im asked for a date on any form. thanks business dev rules.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 5, 2012 at 8:40 am
capn.hector (4/5/2012)
Sean Lange (4/5/2012)
Eugene Elutin (4/5/2012)
Sean Lange (4/4/2012)
You have to either use a datetime variable or put your string representation inside single quotes.
select * from TICKET where createDate > '4/1/2012'
Is it 4th Jan 2012 or 1st Apr 2012?
For string dates I would recommend to use ISO format: YYYY-MM-DD (or juts YYYYMMDD)
or "medium" format: 'DD MMM YYYY'
Good point Eugene and one I all to frequently forget when posting.
i must have been doing this to long as i write YYYY-MM-DD when ever im asked for a date on any form. thanks business dev rules.
I worked as a consultant several years for a place that had a Companies table, unfortunately they spelled in the possessive (Companys). To this day I have to stop and think about the correct spelling. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 5, 2012 at 5:31 pm
Eugene Elutin (4/5/2012)
Sean Lange (4/4/2012)
You have to either use a datetime variable or put your string representation inside single quotes.
select * from TICKET where createDate > '4/1/2012'
Is it 4th Jan 2012 or 1st Apr 2012?
For string dates I would recommend to use ISO format: YYYY-MM-DD (or juts YYYYMMDD)
or "medium" format: 'DD MMM YYYY'
I believe that only string formats YYYYMMDD and YYYY-MM-DDThh:mm:ss.mil are unaffected by by settings of DATEFORMAT and LANGUAGE.
The examples below show conversion failures for YYYY-MM-DD and DD MMM YYYY date string formats.
set dateformat ydm
set language us_english
go
print 'YYYY-MM-DD fails with dateformat YMD'
SELECT [YYYY-MM-DD] = convert(datetime,'2012-12-15')
go
SELECT [YYYYMMDD] = convert(datetime,'20121215')
go
SELECT [YYYY-MM-DDThh:mm:ss.mil] = convert(datetime,'2012-12-15T00:00:00.000')
GO
SELECT [DD MMM YYYY] = convert(datetime,'12 MAR 2012')
go
set language polish
go
print 'DD MMM YYYY fails with non-english setting'
SELECT [DD MMM YYYY] = convert(datetime,'12 MAR 2012')
Results:
Changed language setting to us_english.
YYYY-MM-DD fails with dateformat YMD
Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
YYYYMMDD
------------------------------------------------------
2012-12-15 00:00:00.000
(1 row(s) affected)
YYYY-MM-DDThh:mm:ss.mil
------------------------------------------------------
2012-12-15 00:00:00.000
(1 row(s) affected)
DD MMM YYYY
------------------------------------------------------
2012-03-12 00:00:00.000
(1 row(s) affected)
Changed language setting to polski.
DD MMM YYYY fails with non-english setting
Server: Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply