March 13, 2012 at 7:31 am
Hello all, have a stored proc that filters on date time however, the data it retrives is our side the parameters.
Like for example
I create a table with 1 collumn
Create table test (dttm datetime)
insert in to this table 15 rows
insert into test select getdate()
go 5
insert into test select getdate()-10
go 5
insert into test select getdate()-15
go 5
Next run query to returin all rows that are greater than say yesterday, i expect to recieve 5 results but i actually recieve all 15
querys tried
select * from test
where dttm >'02/03/2012'
select * from test
where convert(varchar,dttm,103) >'02/03/2012'
This works
select from t1t
where dttm >'2012-03-02'
but i want to be able to pass a date tme in DD\MM\YYYY
Can anyone explain why at least the Convert isnt working 🙂 :w00t:
***The first step is always the hardest *******
March 13, 2012 at 7:38 am
try set dateformat dmy at the top of the script
March 13, 2012 at 8:08 am
DOH why dindt i think of that worked perfect thanks 🙂
***The first step is always the hardest *******
March 13, 2012 at 8:10 am
your login will have a language of english which is US so dates are in the format MDY, if you change it to british english it changes it to dmy, but you should always be using ISO date formats so that you dont run into issues like this
March 13, 2012 at 8:24 am
SGT_squeequal (3/13/2012)
DOH why dindt i think of that worked perfect thanks 🙂
You really should consider passing the datetime in using the ISO standard format. SQL will properly recognize the date regardless of language setting on the server or connection.
March 13, 2012 at 8:33 am
Lynn Pettis (3/13/2012)
SGT_squeequal (3/13/2012)
DOH why dindt i think of that worked perfect thanks 🙂You really should consider passing the datetime in using the ISO standard format. SQL will properly recognize the date regardless of language setting on the server or connection.
Yep, use YYYY-MM-DD or YYYYMMDD (ISO 8601 🙂
or any unambiguous form: DD MMM YYY, MMM DD YYYY
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply