DISTINCT to ShortDateString, not DISTINCT to the DateTime; How?

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHEREBranchID=@.prmBranchID ANDProposal_Date=@.prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

Try this -

Select Distinct Convert(char(10), Proposal_Date, 101)

It will eliminate the time part from the selection

Regards

Ash

|||

This one will solve your problem:

SELECT

DISTINCTCONVERT(NCHAR(8), Proposal_Date, 112)AS Proposal_Date

FROM

Proposals

You can look up from Books Online for the date, convert functions to help you understand more about the date and time. If you need more help, please post again.

|||

Hello,

Yeah that was what I neeed to know, and it solved the problem.

(apparently, I'm still new to T-SQL :)

Thanks.

|||

I know your problem is resolved but try the link below for more about the SQL Server DateTime convert function codes. And if you want Short Datetime you use SmallDateTime data type it gives you DateTime less seconds because it has less resolution. Hope this helps.

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

DISTINCT MonthName for a lot of dates....

Hi all,
I have a table with several rows, each has a datetime field.
I want to query this table, ideally with my stored procedure and return just
a set of month names/numbers if possible, but I keep going around in circles
either getting ALL of my dates back with the names in a new column, or only
the month names, but order incorrectly...
table structure:
PregnancyLog
LogID int
LogDateTime datetime
sample data
LogID, LogDateTime
1,29/01/05
2,30/01/05
3,01/02/05
4,03/02/05
5,04/02/05
6,11/03/05
7,12/03/05
8,23/04/05
9,12/08/05
Expected results
MonthName, MonthNumber
January, 1
February, 2
March, 3
April, 4
August, 8
Any help would be appreciated - my only current resolution would be to
create a view of my data which gets me the month names, and then do a
distinct on that with the stored procedure, but I'd rather just do it once
in the stored procedure if possible.
Regards
Rob"Rob Meade" wrote ...

> Any help would be appreciated
I hate it when this happens...looks like I might have sussed it myself...
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
Does that look acceptable to anyone? It gives me the results I wanted but I
just wanted to make sure..
Regards
Rob|||On Thu, 24 Nov 2005 23:20:43 GMT, Rob Meade wrote:

>"Rob Meade" wrote ...
>
>I hate it when this happens...looks like I might have sussed it myself...
>SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
>FROM PregnancyLog
>GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
>ORDER BY MONTH(LogDateTime)
>Does that look acceptable to anyone? It gives me the results I wanted but
I
>just wanted to make sure..
>Regards
>Rob
>
Hi Rob,
Looks good.
Here's an (untested) alternative:
SELECT DISTINCT DATENAME(month, LogDateTime) AS MonthName,
MONTH(LogDateTime)
FROM PregnancyLog
ORDER BY MONTH(LogDateTime)
Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
I'm not sure of that.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> Looks good.
Thank you :o)

> Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
> I'm not sure of that.
Cheers for that Hugo, it worked a treat, I left the MONTH(LogDateTime) in,
and added an alias of MonthNumber as I use this in the application.
But its still less code than I had - many thanks :o)
Regards
Rob|||Hi Hugo,
Any ideas how I would add a "count" to the end of the result set of the
number of log items for each month returned by the existin query...
Ie...
MonthName MonthNumber Counter
January 1 2
February 2 6
March 3 15
Any help would be really appreciated, I've tried adding COUNT(LogID) to my
query, but then I get message telling me that things need adding to the
aggregate function or the group by clause, which I did try adding again but
then I have to lose the order by or else I get EVERY row
again...nightmare..
Any help appreciated.
Regards
Rob|||On Fri, 25 Nov 2005 23:17:45 GMT, Rob Meade wrote:

>Hi Hugo,
>Any ideas how I would add a "count" to the end of the result set of the
>number of log items for each month returned by the existin query...
>Ie...
>MonthName MonthNumber Counter
>January 1 2
>February 2 6
>March 3 15
>Any help would be really appreciated, I've tried adding COUNT(LogID) to my
>query, but then I get message telling me that things need adding to the
>aggregate function or the group by clause, which I did try adding again but
>then I have to lose the order by or else I get EVERY row
>again...nightmare..
>Any help appreciated.
>Regards
>Rob
>
Hi Rob,
If you need to add a count (or any other aggregate function), then you
can't use my shorter version; you'll have to return to your original
version with GROUP BY.
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
COUNT(LogID) AS Counter
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
should work. If not, you'll need to provide more information, as
described in www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
> COUNT(LogID) AS Counter
> FROM PregnancyLog
> GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
> ORDER BY MONTH(LogDateTime)
> should work. If not, you'll need to provide more information, as
> described in www.aspfaq.com/5006.
Hi Hugo,
Worked a treat, many thanks - I thought I tried exactly that, but obviously
not, when I tried it, SQL moaned that I needed to add LogDateTime to the
GROUP BY...
Typical that I'd only just posted to see if I could get a few others to look
in this thread from yesterday as I wasn't sure if you'd return to this
message - and you've already solved it - lol - I'll get flamed now for
posting needlessly...hehe..sorry all :o)
Thanks muchly for the help - the website I'm creating is all about my new
born son, so its kinda important to me - thus appreciate the help even more
than usual :o)
Regards
Rob|||On Fri, 25 Nov 2005 23:31:09 GMT, Rob Meade wrote:
(snip)
> I'll get flamed now for
>posting needlessly...hehe..sorry all :o)
Hi Rob,
If you insist, I think I can arragne you being flamed. Do you want me to
call Celko over? ;->
Congratulations on your boy. Don't spend all your time building the
website - spend plenty time enjoying him. They grow up so fast.....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Displaying rows by month

Hi All,
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:

Jan 2003
(Rows whose date is in Jan 2003)
Feb 2003
(Rows whose date is in Feb2003)
.
.
.
Jan 2004
(Rows whose date is in Jan 2004)
Feb 2004
(Rows whose date is in Feb2004)
.
.
so on...

Can any body give my SQL query to get the desired results.
Thanks a lot,
Kumar.For January, 2003:

SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 1 AND YEAR(DateColumn) = 2003

For February, 2003:
SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 2 AND YEAR(DateColumn) = 2003

and so on...|||you need to use just simple order by this datetime field
if I understand your question

displaying milliseconds in VB 6 program

Hi,
I am trying to get my VB program to recognize milliseconds when it is
returned as a datetime field from sql. I can format it to the seconds but
can't find out to include the milliseconds. Any help would be appreciated.
EllieReturn a CHAR/VARCHAR from SQL Server (using CONVERT()) and explicitly cast
the result as a string in VB (CStr() I guess). You could also write a
simple date formatting function in VB, but if you're getting short-circuited
by VB somehow and it trims them off, only if you return the milliseconds
separately. Some of these articles might be helpful:
http://www.aspfaq.com/2460
http://www.aspfaq.com/2313
http://www.aspfaq.com/2093
"Ellie" <nospam@.nospam.net> wrote in message
news:O88yFBXkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am trying to get my VB program to recognize milliseconds when it is
> returned as a datetime field from sql. I can format it to the seconds but
> can't find out to include the milliseconds. Any help would be appreciated.
> Ellie
>

Displaying data using correct culture

I'm working on a report that displays datetime columns and no matter what I set as my currentculture the datetime columns always use the US format: mm/dd/yyyy.

On the report designer I see there's a "Language" property but I cannot find how to set this from the code.

Note that I'm not talking about localizing the report interface. I know there's adowloadable language pack for this.
What I need is the that the data is displayed using the correct formating for numbers and dates for the current active locale.

Any idea?

Yeah, that's Globalization (as you know)... are you returning a DateTime field in a GridView and supplying the DataFormatString yourself... or are you doing a CONVERT(VARCHAR, MyDate, 101) in SQL? (Because if SQL is the one formatting it, you're getting a string... not a DateTime)

Thanks,

|||

I'm using a ReportViewer in LocalMode and the stored proc that returns data does not format it in any way:

select
a.Date,...
from table a
where ...

Where a.Date is a datetime field.

|||

HI,vmasanas :

As localization is not a built-in feature for Reporting Services, people have tried a a variety of techniques for localizing reports. One technique is to use the LocID propertie to create a version of the RDL in each language. Another approach is to have a single report and create a custom assembly to load the strings for each label. Here is an relatively easy technique for providing localized reports using hidden parameters.

First, you will need to create a table to hold the translations. The following T-SQL will create the table and add a few labels for translating the Product Line Sales sample that is included with the product.

CREATE TABLE [dbo].[Translations](
[Label] [nvarchar](150) NOT NULL,
[Language] [nvarchar](10) NOT NULL,
[Translation] [nvarchar](150) NOT NULL
)
GO
INSERT [dbo].[Translations]
VALUES ('Top Stores','fr-fr','Les meilleurs magasins')
INSERT [dbo].[Translations]
VALUES ('Top Employees','fr-fr','Les meilleurs employés')
INSERT [dbo].[Translations]
VALUES ('Top Stores','en-US','Top Stores')
INSERT [dbo].[Translations]
VALUES ('Top Employees','en-US','Top Employees')
INSERT [dbo].[Translations]
VALUES ('Top Stores','de-DE','Oberseite Speicher')
INSERT [dbo].[Translations]
VALUES ('Top Employees','de-DE','Obere Angestellte')
GO


Now, create a query that will return the set of labels for a given language. Add a dataset named 'Labels' that has the following query:

SELECT Label, Language, Translation
FROM Translations
WHERE (Language = @.Language)

By default, a new report parameter will be created named Language. If you would like to automatically bind the translations to the user's language, you can delete this parameter and bind it to User!Language in the dataset properties dialog. However, for testing purposes it is easier to just type it in for preview.

Next, you will need to create a hidden, multi-valued parameter called 'Labels'. Set the Available Values to the Labels dataset, the Value field to 'Labels' and the Label field to 'Translation'. Set the default values to the same dataset and the Value field to 'Labels'. This is important as you don't have access to the available values from within the report, only the actual values. When the user runs the report, the parameter value will contain all of the labels.

Now, add a function to the report (from the code tab of the Report->Report Properties menu item)

Public Function GetLabel(P as Parameter, Label as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If (P.Value(i) = Label) Then Return P.Label(i)
Next i
Return Label
End Function

This function will find the translated label within the supplied multi-valued parameter. If the label is not found, the passed in value is returned. This is important as you may have a user language for which you have not created the translations.

The only thing left is to change the static labels in your report to use this new function. For example, to translate the Top Employees label, use the function

=Code.GetLabel(Parameters!Labels,"Top Employees")

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be

of assistance

|||

Rex,

thanks for you comments, but what I was asking for is slightly different. What I need is that special data types are formated accordingly to the current culture. For example if I get this data:

SELECT aDate, aNumber FROM ...

I'd expect that when the user is using a en-US locale the date is displayed as mm/dd/yyyy and the number "##,###.##". Otherwise, if current culture is es-ES I'd expect to see dd/mm/yyyy and "##.###,##" respectively.

Either I'm doing something wrong or I'm missing a point because this does not work for me. I have a language selector on the page and no matter what I use I always see the data formated as en-US. And, no, I don't do any formating, convertion or modification on these fields. They come right from the db.

|||

As for theReportingService 2005, it does support some localization
features, they include:

1)the localization of the built-in UI components, such as SSRS's
htmlviewer, report designer

2) some simple localization on the SSRS report's data

For 1), the SSRS has done the work for us already, for example, when we
visit the html report, the UI elements on the htmlviewer(button or other UI
element's text) will render the localized representation according to
client-side browser's user-language setting.

For 2), if we want to do some simple localization on the static data/text
displayed on our report, we can dynamically format them according to the
"User!Language" parameter in our report expression. Or you can even build
custom assembly that has custom code logic to generate localizaed text(from
net resource ) accordin to this parameter.

You can find all the localization support of SSRS 2005 in the BOL:

#International Considerations forReporting Services
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

While leveraging User!Language certainly will work in terms of allowing you localize your reports, doing so is admittedly a pain in the tail – you essentially have to replace all your label text with expressions that call into code that you write to do "label localization" ala:

= MyNameSpace.MyConvertingClass.MyConverter("someLabelName", User!Language)

Your code takes the name of a label and the language to use, then convert the date or the symbol to be the the currect one and return it

|||

HI,vmasanas:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

|||

Rex,

thanks for your support but I think we are talking of different things here.

What I'm trying to get is that a datetime column displays it's data using a format accordingly to the current culture selected by the user at the time.

When I'm designing a report I see a property called "Language" when, if changed, displays data correctly. What I would need is access to this property at runtime so the report is configured dinamically depending on the user preferences.

I've not been able to figure where in the object hierarchy I can get access to this property.

|||

I had a similar problem and here is how to solve it:

CultureInfo ci;
// Format the current date and time in various ways.

// Display the thread current culture, which is used to format the values.
ci = Thread.CurrentThread.CurrentCulture;
ci = new CultureInfo("en-IE");
Thread.CurrentThread.CurrentCulture = ci;
Thread.CurrentThread.CurrentUICulture = ci; //I guess you did'nt set this one

Hanin

|||

I wish you were right but no :(

Protected Overrides Sub OnInit(ByVal eAs System.EventArgs)
' Set the current culture
Thread.CurrentThread.CurrentUICulture = PageCulture
Thread.CurrentThread.CurrentCulture = PageCulture
' Localize portalsettings
Services.Localization.Localization.LocalizePortalSettings()
MyBase.OnInit(e)
End Sub

I'm trying to use this inside aDotNetNuke module but seems there's something missing here since I've never had any problem with localization inside DNN.

display TODAY's records...

I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.

I tried this code but didn't work..

[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]

? Try: SELECT * FROM files_news WHERE news_date_time >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND news_date_time < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1) ORDER BY news_date_time DESC -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jassim Rahma@.discussions.microsoft.com> wrote in message news:a33dde1c-a329-4bf2-a66b-f2b842fdfd8c@.discussions.microsoft.com... I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time. I tried this code but didn't work.. [code]SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);[/code]|||use northwind
select * from orders
where convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualify
convert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||? The only problem with that method is that it's quite bad for performance. An index on "orderdate" will not be usable by the query engine to help satisfy the query. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <joeydj@.discussions.microsoft.com> wrote in message news:d496ce31-0a56-41f7-902f-bcd35c0ab29f@.discussions.microsoft.com...use northwindselect * from orderswhere convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualifyconvert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||Conversion will lead to bad execution plans or even table scans. You should always use the built-in functionality of the appropiate data types to achieve your goals and the best queryplan.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

i see.

thanks

|||

SELECT*FROM Employees

WHERECONVERT(varchar(15), hiredate, 112)=CONVERT(varchar(15),GETDATE(), 112)

Adamus

|||? Once again: Do not use that if you care about performance! Assuming that an index exists on "hiredate" in your example, SQL Server will not be able to use it. Please see my previous reply to this thread for an example of how to correctly phrase the query so that it can use an index. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:00a1169a-7acd-4c96-b6db-b130024a5e77@.discussions.microsoft.com... SELECT * FROM Employees WHERE CONVERT(varchar(15), hiredate, 112) = CONVERT(varchar(15), GETDATE(), 112) Adamus|||

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

|||? No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com... There's a performance issue converting a date into a string and then comparing a string? Can this performance difference even be measured? Adamus|||

NNTP User wrote:

?

No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

<Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com...

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place.

Good luck,

Adamus

|||

Yes. See the last few messages in this thread: http://groups.google.com/groups/search?q=%22function+to+return+today+at+midnight%22 And see http://groups.google.com/groups/search?q=strik+kass+%22small+improvement%22 Steve Kass Drew University www.stevekass.com Adamus Turner@.discussions.microsoft.com wrote:
> There's a performance issue converting a date into a string and then
> comparing a string?
>
> Can this performance difference even be measured?
>
> Adamus
>
>

|||? Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place. Good luck, Adamus No, nothing is removed. The index is simply not usable. I highly recommend that you do some background reading on indexes and how they work; "Inside SQL Server 2000" by Kalen Delaney would be a very good place to start. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--

display TODAY's records...

I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.

I tried this code but didn't work..

[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]

? Try: SELECT * FROM files_news WHERE news_date_time >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND news_date_time < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1) ORDER BY news_date_time DESC -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jassim Rahma@.discussions.microsoft.com> wrote in message news:a33dde1c-a329-4bf2-a66b-f2b842fdfd8c@.discussions.microsoft.com... I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time. I tried this code but didn't work.. [code]SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);[/code]|||use northwind
select * from orders
where convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualify
convert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||? The only problem with that method is that it's quite bad for performance. An index on "orderdate" will not be usable by the query engine to help satisfy the query. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <joeydj@.discussions.microsoft.com> wrote in message news:d496ce31-0a56-41f7-902f-bcd35c0ab29f@.discussions.microsoft.com...use northwindselect * from orderswhere convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualifyconvert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||Conversion will lead to bad execution plans or even table scans. You should always use the built-in functionality of the appropiate data types to achieve your goals and the best queryplan.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

i see.

thanks

|||

SELECT*FROM Employees

WHERECONVERT(varchar(15), hiredate, 112)=CONVERT(varchar(15),GETDATE(), 112)

Adamus

|||? Once again: Do not use that if you care about performance! Assuming that an index exists on "hiredate" in your example, SQL Server will not be able to use it. Please see my previous reply to this thread for an example of how to correctly phrase the query so that it can use an index. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:00a1169a-7acd-4c96-b6db-b130024a5e77@.discussions.microsoft.com... SELECT * FROM Employees WHERE CONVERT(varchar(15), hiredate, 112) = CONVERT(varchar(15), GETDATE(), 112) Adamus|||

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

|||? No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com... There's a performance issue converting a date into a string and then comparing a string? Can this performance difference even be measured? Adamus|||

NNTP User wrote:

?

No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

<Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com...

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place.

Good luck,

Adamus

|||

Yes. See the last few messages in this thread: http://groups.google.com/groups/search?q=%22function+to+return+today+at+midnight%22 And see http://groups.google.com/groups/search?q=strik+kass+%22small+improvement%22 Steve Kass Drew University www.stevekass.com Adamus Turner@.discussions.microsoft.com wrote:
> There's a performance issue converting a date into a string and then
> comparing a string?
>
> Can this performance difference even be measured?
>
> Adamus
>
>

|||? Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place. Good luck, Adamus No, nothing is removed. The index is simply not usable. I highly recommend that you do some background reading on indexes and how they work; "Inside SQL Server 2000" by Kalen Delaney would be a very good place to start. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--

display short datetime

Hi

I need to set the default value of an "End Date" parameter to the date of today.
Now I use the expression =Now(), but then the timestamp is also displayed. That I don't want to happen!

I tried formatting the datetime but then it becomes a string, and converting it back to a date leaves also a timestamp but with midnight time.

Is there a way to format a date parameter?

Hi,

Did you try : Format(mydate.Value,"dd/MM/yyyy") or Cdate(Format(mydate.Value,"dd/MM/yyyy").ToString) ?

Regards

Ayzan

|||

Both expressions above don't work because my parameter is of the type datetime and the expressions return a string. So I get an error that the parameter has another type than it expected.

Still thanks for pointing me the CDate function. It has alot of functions and that's the function that got me the wanted result.

To display only the date part of a datetime value retrieved by the function Now() you need the following expression:
=CDate(Now()).Today

Display result in hours, weeks and month

Hello!
I have data stored in a fld which is a datetime difference in hours. I
need to display it in 'Hours' (if it is less than 24), 'Ws' (if it is
greater than 168) and in 'Month' (if it is greater than 720). Is there
an easy to do it?
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***You forgot to mention dates
take a look at this, you might want to include years also
create table #timeStuff (TimeField int)
insert into #timeStuff
select 5 union all
select 55 union all
select 125 union all
select 1225 union all
select 555 union all
select 721 union all
select 719
select TimeField,case
when TimeField < 24 then 'hours'
when TimeField between 24 and 168 then 'days'
when TimeField between 169 and 719 then 'ws'
when TimeField > 720 then 'months'
end
from #timeStuff
drop table #timeStuff
http://sqlservercode.blogspot.com/|||Would this work?
select TimeField,case
when TimeField < 24 then TimeField * 1.0
when TimeField between 24 and 168 then Timefield/24.0
when TimeField between 169 and 719 then Timefield/(24.0*7.0)
when TimeField > 720 then Timefield/(24.0*30.0)
end as result
from #timeStuff
(using DDL from second message:
create table #timeStuff (TimeField int)
insert into #timeStuff
select 5 union all
select 55 union all
select 125 union all
select 1225 union all
select 555 union all
select 721 union all
select 719
The issue here is that you are storing an interval, not hard dates, but
you want to figure out the number of months in hours. Since some
months are 30 days and some are 31 days (february-28 days), you won't
get an exact result here.
The other calculations should work since there are always 24 hours in a
day and 7 days in a w.
Christian|||No. This is what I want based on your data.
5 = 5 hurs
55 = 2 days 7 hrs
125 = 5 days 2 hrs
1225 = 1 month ws and hrs
555 = 3 ws and hours
and so on ...
*** Sent via Developersdex http://www.examnotes.net ***|||drop table #timeStuff
go
create table #timeStuff (hours int)
insert into #timeStuff
select 5 union all
select 55 union all
select 125 union all
select 1225 union all
select 555 union all
select 721 union all
select 719
go
this should give you what you want, though I don't know how you want to
calculate months since that is a variable amount of time (ws are exactly
7 days (well, except for leap years, but for all intents and purposes :))
select hours as totalHours, hours/168 as ws, (hours % 168) / 24 as days,
((hours % 168) % 24) as hours
from #timestuff
/*
totalHours ws days hours
-- -- -- --
5 0 0 5
55 0 2 7
125 0 5 5
1225 7 2 1
555 3 2 3
721 4 2 1
719 4 1 23
*/
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OlHMYqKOGHA.2036@.TK2MSFTNGP14.phx.gbl...
> No. This is what I want based on your data.
>
> 5 = 5 hurs
> 55 = 2 days 7 hrs
> 125 = 5 days 2 hrs
> 1225 = 1 month ws and hrs
> 555 = 3 ws and hours
> and so on ...
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks a lot everyone!!!!
*** Sent via Developersdex http://www.examnotes.net ***

Display of date time inforamtion - some columns are NULL some are not

I have a column in a table that is a datetime data type. Some columns
are NULL some are not.
So, a sampling of data could include:
NULL
2005-06-06 12:32:53.000
2005-04-12 11:32:53.000
NULL
NULL
2005-12-22 12:32:53.000
When I select from this column, if the value is NULL, I need to replace
NULL with the word 'No'. If the value is not NULL, then I need to
display the date - so my output needs to look like this:
No
06/06/2005
04/12/2005
No
No
12/22/2005
I know how to convert the date - the problem I am having is converting
the NULL datetime to characters and including the logic to account for
NULLs in the first place.
I suspect that I need a CASE statement, but I am not sure how to
accomplish this.
Thanks-SELECT COALESCE(CONVERT(CHAR(10), datecolumn, 101), 'No')
FROM table
However, I recommend against using ambiguous formats like m/d/y for display.
<wxbuff@.aol.com> wrote in message
news:1138713642.280962.50970@.g43g2000cwa.googlegroups.com...
>I have a column in a table that is a datetime data type. Some columns
> are NULL some are not.
> So, a sampling of data could include:
> NULL
> 2005-06-06 12:32:53.000
> 2005-04-12 11:32:53.000
> NULL
> NULL
> 2005-12-22 12:32:53.000
> When I select from this column, if the value is NULL, I need to replace
> NULL with the word 'No'. If the value is not NULL, then I need to
> display the date - so my output needs to look like this:
> No
> 06/06/2005
> 04/12/2005
> No
> No
> 12/22/2005
> I know how to convert the date - the problem I am having is converting
> the NULL datetime to characters and including the logic to account for
> NULLs in the first place.
> I suspect that I need a CASE statement, but I am not sure how to
> accomplish this.
> Thanks-
>|||
select coalesce(convert(varchar(10),thedatecolu
mn,101),'No')
However, you may be better off doing the formatting in your client|||Aaron -
Perfect! I am still pretty new at this and was unfamiliar with the
COALESCE function. I see from the BOL that it replaces more
complex CASE statements so I am gratified to know that I was thinking
down the right path. Thank you for helping - it will save
me a great deal of time.
Danielle

Display null values

Hello,

I'm facing a problem in my reporting.

I have a Customer table where is record various events like CustomerEventId, DateTime, StatusId, StatusTime, GroupId, ...

I also have a status table (Id, Description) and a group table (Id, Description).

I want to create a report where for a selected date range (From ... To ...) i can see (grouped by date) all status's the customer

went in. The possible status are :

Id Description

-

1 status 1

2 status 2

3 status 3

4 status 4

My query looks something like this :

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers inner join status on customers.StatusId = status.id

inner join group on customers.GroupId = group.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

My reports has 3 parameters (From date, To date, Group)

In my report i have a table with two groups : GroupByDate (grouped on DATEVAL) and GroupByStatus

now my problem : let's say i have values for statusid 1,2 and 4

then my report will only display those 3 status.

How can i display the status where there is no data for :

now it shows :

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 4 8 66

I would like it to show:

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 3 0 0

Status 4 8 66

Anybody (i hope i have provide enough details ...)

Vinnie

Hello Vinnie,

You're going to need to modify your dataset to return those records (in your example data, "Status 3") with NULL for the rest of the values. Then you can replace the NULL's with 0, either in your SQL query or in the table.

Use an outer join to get all the status records back whether or not there are matching customer records. Something like this:

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers

inner join group on customers.GroupId = group.id

right outer join status on customers.StatusId = status.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

Hope this helps.

Jarret

display msgbox in browser

hi..

i have the following embedded code in my rdl file..

Function validateDate (ByVal startDt as datetime, ByVal endDt as datetime)

if (startDt > endDt)
System.Windows.Forms.MessageBox.Show("Date Range From must be earlier or the same as Date Range To")
End if

End function

this code works in VS IDE but the msgbox would not appear when view using a browser...

how can i get the msgbox to appear when view using a browser?

thanksss

Code execution will happen at the server, not at the client. Client only receives the output of the renderer. The MessageBox might be showing up at the server, but that doesn't help the client.

There is no good way to cause such a dialog to display at the client.

|||

thanks mike for your reply. is there any workabout to this?

is there any error handling i can do to alert the users?

thanks!

Display date in reports

How to display only the date part on reports from DateTime fields?

= format(fields!mydatefield.value,"mm/dd/yyyy")

or something like that.

|||Also how do I display the long form of DateTime?|||use the vb.net formatdate function... I'm sure you can lookit up quite easliy.|||Thanks I got it.

For Short form : =FormatDateTime(Fields!InputDate.Value, DateFormat.ShortDate)
For Long form : =FormatDateTime(Fields!InputDate.Value, DateFormat.LongDate)
For complete Date and Time : =FormatDateTime(Now, DateFormat.LongDate) & " " & FormatDateTime(Now,DateFormat.LongTime)

display data in report grouped by datetime

Hello,

I have the following situation :

I have a reportmodel where i have the a datetime value (dd/mm/yyyy hh:mms), a salespersonid , a salespersondescription, and an amountsold.

i would like to display a report where i can see per hour (start worktime is 8 am and end is 8 pm) which salesperson sells the most. the objectif of this report is to see in what period of the day the sales are highest.

Can anybody provide me some tips how to accomplish this.

Vinnie

You might be able to take advantage of the DATEPART function to group your sales figures. I'm not sure I have the correct visualization of your report, but you might be able to start with a query like:

Code Snippet

declare @.reportModel table
( salesPersonId integer,
salesPersonDescription varchar(20),
amountSold decimal (9,2),
salesDateTime datetime
)
insert into @.reportModel
select 1, 'The Boss', 29.95, '6/15/7 7:45' union all
select 1, 'The Boss', 10.95, '6/15/7 9:21' union all
select 1, 'The Boss', 149.99, '6/15/7 10:15' union all
select 2, 'Salesman #2', 17.45, '6/15/7 8:05' union all
select 2, 'Salesman #2', 21.25, '6/15/7 8:15' union all
select 2, 'Salesman #2', 79.99, '6/15/7 8:59' union all
select 2, 'Salesman #2', 9.95, '6/15/7 9:09' union all
select 2, 'Salesman #2', 19.95, '6/15/7 9:21' union all
select 2, 'Salesman #2', 21.45, '6/15/7 10:29'
--select * from @.reportModel

select datepart (hh, salesDateTime) as HourOfDay,
cast(sum (amountSold) as decimal(15,2)) as HourlySalesTotal,
count(*) as numberOfSales,
salesPersonId,
salesPersonDescription
from @.reportModel
group by datepart (hh, salesDateTime),
salesPersonDescription,
salesPersonId

which is capable of delivering results such as:

Code Snippet

HourOfDay HourlySalesTotal numberOfSales salesPersonId salesPersonDescription
-- -- - - -
7 29.95 1 1 The Boss
8 118.69 3 2 Salesman #2
9 29.90 2 2 Salesman #2
9 10.95 1 1 The Boss
10 21.45 1 2 Salesman #2
10 149.99 1 1 The Boss