date range query not returning anticipated results

Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>

Date Picker control returning date is US format

SQL 2005 SP2 + hotfix build 3152
I'm a newbie to reporting services and I am just learning and testing at the
moment. I am based in the UK.
I have set up a report with two parameters that are both dates.
When running the report in the IDE or http://ServerName/Reports dates picked
from the calendar control return as DD/MM/YYYY
When I run the report through http://Servername/ReportServer the date is
returned to the web page as MM/DD/YYYY.
I have found that if I choose a date like 02 Feb so I can run the report and
then use the date picker again without exiting, it then returns DD/MM/YYYY.
My client PC is set to UK English and I have checked my IE settings (I have
tried firefox too). The server is all set to UK english. I have checked the
settings on IIS as well.
I have tried forcing the culture to "en-GB" in web.config.
It's interesting to note that the report services log files (eg
ReportServerWebApp__03_16_2007_14_29_30.log) contains en-US.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
9.00.3152.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles\ReportServerWebApp__03_16_2007_14_29_30.log</Path>
<SystemName>myservername</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
<OSVersion>5.2.3790.131072</OSVersion>
</Header>On 16 mar, 16:05, "Paul Cahill" <a...@.anon.com> wrote:
> SQL2005 SP2 + hotfix build 3152
> I'm a newbie to reporting services and I am just learning and testing at the
> moment. I am based in the UK.
> I have set up a report with two parameters that are both dates.
> When running the report in the IDE orhttp://ServerName/Reportsdates picked
> from the calendar control return as DD/MM/YYYY
> When I run the report throughhttp://Servername/ReportServerthe date is
> returned to the web page as MM/DD/YYYY.
> I have found that if I choose a date like 02 Feb so I can run the report and
> then use thedate pickeragain without exiting, it then returns DD/MM/YYYY.
> My client PC is set to UK English and I have checked my IE settings (I have
> tried firefox too). The server is all set to UK english. I have checked the
> settings on IIS as well.
> I have tried forcing the culture to "en-GB" in web.config.
> It's interesting to note that the report services log files (eg
> ReportServerWebApp__03_16_2007_14_29_30.log) contains en-US.
> <Header>
> <Product>MicrosoftSQLServer Reporting Services Version
> 9.00.3152.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>GMT Standard Time</TimeZone>
> <Path>C:\Program Files\MicrosoftSQLServer\MSSQL.3\Reporting
> Services\LogFiles\ReportServerWebApp__03_16_2007_14_29_30.log</Path>
> <SystemName>myservername</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
> <OSVersion>5.2.3790.131072</OSVersion>
> </Header>
Exactly the same problem here, it is caused by SP2 and his new date-
picker control. Anyone knows if MS is working to correct this?. Thanks.

Date formatting

Is there a function to return the date in the format of "4 July 2007" instead. I am returning rows which contain dates in the format "20/07/2007 10:48:16". Is there any function that convert, or will I have to write the code myself? I dont mind writing the code, but its a friday evening, and I am lazy and tired :-)
You can get close enough with CONVERT

eg, convert(varchar, getdate(),106)

To get the full month name though i believe you'd have to write something bespoke.

HTH.
|||

Yes. You have to write your own..

Select Cast(day(getdate()) as Varchar)

+ ' '

+ datename(month, getdate())

+ ' '

+ Cast(year(getdate()) as varchar)

|||As Mani indicated, create your own function, pass in the date, and get it back just like you want it.

Date format when submitting queries

Hi, when submitting a query on a date field I need to query the date in american format (mm/dd/yy). When returning the date as part of another query it displays it in British format (dd/mm/yy) which is what I want. Regional settings etc are correct - what else should I be looking for?

Thanks,
Timthe great things about dates is that you can put them and view them in all types of ways.

If you want to be sure you are always in correct format just pick apart the date and using datepart. and you can grab the month, day, year, hour, minute and seconds and put them in any order. So have fun with it, dates just take a little time to get used to but they offer lots of help with date functions. DATEDIFF, DATEADD, DATEPART, DATENAME

ex:SELECT cast(DATEPART(day, GETDATE())as varchar(2))
+'/'+
cast(DATEPART(month, GETDATE())as varchar(2))
+'/'+
cast(DATEPART(year, GETDATE())as varchar(4))|||What interface are you using? Query analyzer? Access? VB?

If you run the following code in Query Analyzer, does it display March 4 ro April 3?

select Convert(varchar(20), cast('3/4/2003' as Datetime))

blindman|||Just try this
select convert( varchar(40),dateformat_field,101)|||i think he wants to enter the date in british format while entering it in the query.|||that's my problem too, i can convert the date as dd/mm/yyyy format when display, but when input the data, users are used dd/mm/yyyy format too, so when data is saved, error message will pop up. How can I convert the dd/mm/yyyy format into the mm/dd/yyyy format. Thanks for the help!|||that's my problem too, i can convert the date as dd/mm/yyyy format when display, but when input the data, users are used dd/mm/yyyy format too, so when data is saved, error message will pop up. How can I convert the dd/mm/yyyy format into the mm/dd/yyyy format. Thanks for the help!|||SET DATEFORMAT dmy

:)