Money Data Type: date

Showing posts with label date. Show all posts

Monday, March 26, 2012

More date format grief

I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.

Brief summary:
I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.sqlserver.SQLServerDriver.

However, in this instance, the database won't accept dates. It won't
accept dates in the java.sql.Date.toString() format (which is the ANSI
SQL 92 format) and it won't accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is 'Z'.

I find, by reading on Usenet, that SQL Server doesn't have a default
date format. Furthermore, it doesn't take it's date format from
Windows Regional settings.

So how, for the love of God and Little Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call
;;-- Minke BouyedSimon,

I'll agree this is very frustrating, but there is no
easy answer, since there is no international standard for
representation of datetime values. ISO-8601 has a huge
number of options, and SQL Server accepts at least a couple
of the ISO-8601 alternatives.

If you have timezone information in data and want one
product that works with all back ends, then you've probably
got trouble. Not all products support timezones, so your
data will end up with different values on different products.
If you want consistency, either eliminate or convert the
timezone data in your front end, and send every back end you
connect to datetime values as strings of the form
'YYYYMMDD HH:MM:SS' (seconds optional or with fractional
seconds as well).

I thought you could also use '{d YYYY-MM-DD}' and it would
work regardless of settings (unlike 'YYYY-MM-DD' which depends
on date format settings). Not sure about this last bit, though.

Does SQL Server have a default date format? This is several questions:

Q. Does SQL Server display dates as character strings in a
consistent way?
A. SQL Server doesn't display anything. IDEs and front-ends do.

Q. Does SQL Server CAST dates to strings with a consistent format?
A. No. This depends on language settings.

Q. Can SQL Server convert dates to strings with a consistent format?
A. Yes, with CONVERT(varchar..., <format>) and string functions.

Q. Does SQL Server import every ISO-8601-allowed date correctly?
A. No. It does import a few of them correctly and consistently:
YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
As far as I know, there is no timezone support.

Q. Does CONVERT(datetime, ...) with format codes convert
consistently?
A. No. The documentation does not make this clear, but
all the numerical, delimited formats except for the ISO
format with the T depend on the connection's language or
dateformat setting (dateformat overrides language, I believe).

Why doesn't SQL Server consistently convert SQL-92 date strings?
Good question. It converts SQL-92 timestamp (without timezone)
correctly, but not date-only.

It will, if the date format at the time of conversion is mdy,
ymd, or myd, but I don't think that's a great solution.

What's the safest date format to use?
Probably 'YYYYMMDD HH:MM:SS.[fff]', an ISO format, since if
someone truncates it to date-only, it won't break, like the
SQL-92 timestamp form.

-- Steve Kass
-- Drew University
-- Ref: 4BA55F69-6565-4B87-BB19-E223787FDB91

Simon Brooke wrote:
> I'm investigating a bug a customer has reported in our database
> abstraction layer, and it's making me very unhappy.
> Brief summary:
> I have a database abstraction layer which is intended to mediate
> between webapps and arbitrary database backends using JDBC. I am very
> unwilling indeed to write special-case code for particular
> databases. Our code has worked satisfactorily with many databases,
> including many instances MS SQLServer 2000 databases using the
> com.microsoft.sqlserver.SQLServerDriver.
> However, in this instance, the database won't accept dates. It won't
> accept dates in the java.sql.Date.toString() format (which is the ANSI
> SQL 92 format) and it won't accept dates in the ISO8601 format if they
> have a zone offset (which in the general case they do) - even if that
> zone offset is 'Z'.
> I find, by reading on Usenet, that SQL Server doesn't have a default
> date format. Furthermore, it doesn't take it's date format from
> Windows Regional settings.
> So how, for the love of God and Little Fishes, do I persuade a SQL
> Server database to accept ANSI SQL 92 dates, permanently, not on a
> per-session basis?|||Steve Kass <skass@.drew.edu> writes:

> Simon Brooke wrote:
> > I have a database abstraction layer which is intended to mediate
> > between webapps and arbitrary database backends using JDBC. I am very
> > unwilling indeed to write special-case code for particular
> > databases. Our code has worked satisfactorily with many databases,
> > including many instances MS SQLServer 2000 databases using the
> > com.microsoft.sqlserver.SQLServerDriver.

> > However, in this instance, the database won't accept dates. It won't
> > accept dates in the java.sql.Date.toString() format (which is the ANSI
> > SQL 92 format) and it won't accept dates in the ISO8601 format if they
> > have a zone offset (which in the general case they do) - even if that
> > zone offset is 'Z'.
> > I find, by reading on Usenet, that SQL Server doesn't have a default
> > date format. Furthermore, it doesn't take it's date format from
> > Windows Regional settings. So how, for the love of God and Little
> > Fishes, do I persuade a SQL
> > Server database to accept ANSI SQL 92 dates, permanently, not on a
> > per-session basis?

> Q. Does SQL Server import every ISO-8601-allowed date correctly?
> A. No. It does import a few of them correctly and consistently:
> YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.

Yes, but, actually, that's not a valid ISO-8601 format, because it
doesn't include a timezone. Furthermore, I don't have the luxury of
being able to generate custom code for every database. Surely it must
be _possible_ to persuade SQL Server to conform to ANSI 92?

> Why doesn't SQL Server consistently convert SQL-92 date strings?
> Good question. It converts SQL-92 timestamp (without timezone)
> correctly, but not date-only.

No, it doesn't. That is where all this grief started: we've been
sending that to SQL Server for years and in every other installation
it has worked, but now we have a customer using MS SQL Server 2000 who
is having that fail consistently and repeatedly on one of their boxes
(they have another box running identical software on which it is not
failing, and on our box which we've one everythintg possible to make
identical it doesn't fail). I've done everything I can to find a
difference in setup between the boxes and so far I've failed.

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call
;;-- Minke Bouyed|||
Simon Brooke wrote:
> Steve Kass <skass@.drew.edu> writes:
>
>>Simon Brooke wrote:
>>
>>>I have a database abstraction layer which is intended to mediate
>>>between webapps and arbitrary database backends using JDBC. I am very
>>>unwilling indeed to write special-case code for particular
>>>databases. Our code has worked satisfactorily with many databases,
>>>including many instances MS SQLServer 2000 databases using the
>>>com.microsoft.sqlserver.SQLServerDriver.
>
>>>However, in this instance, the database won't accept dates. It won't
>>>accept dates in the java.sql.Date.toString() format (which is the ANSI
>>>SQL 92 format) and it won't accept dates in the ISO8601 format if they
>>>have a zone offset (which in the general case they do) - even if that
>>>zone offset is 'Z'.
>>>I find, by reading on Usenet, that SQL Server doesn't have a default
>>>date format. Furthermore, it doesn't take it's date format from
>>>Windows Regional settings. So how, for the love of God and Little
>>>Fishes, do I persuade a SQL
>>>Server database to accept ANSI SQL 92 dates, permanently, not on a
>>>per-session basis?
>
>>Q. Does SQL Server import every ISO-8601-allowed date correctly?
>>A. No. It does import a few of them correctly and consistently:
>> YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
>
> Yes, but, actually, that's not a valid ISO-8601 format, because it
> doesn't include a timezone. Furthermore, I don't have the luxury of
> being able to generate custom code for every database. Surely it must
> be _possible_ to persuade SQL Server to conform to ANSI 92?
My reference is ISO8601:2000E (December, 2000), and I don't see
where a timezone is required. Do you have the paragraph number?

Section 5.4 describes point-in-time representations, and says "The
zone designator is empty if use is made of the local time of the
day in accordance...", referring to earlier sections that give
offer hhmm, hh:mm, hhmmss, hh:mm:ss, hh:mm,m, hhmm,m, hh, etc.,
etc., as possible date formats.

It also gives Basic (no hyphens) and extended (with hyphens) formats
for everything, without as far as I can see mandating one or the
other. It would be nice if SQL Server understood them all, but it
does understand the one with hyphens and a T (ISO allows the T to be
omitted if no ambiguity results, though I couldn't see where
any would regarding other ISO formats - probably missed something
crazy like week numbers in BC years that used a T.)

SQL Server also understands the one with no hyphens or T.
It looks ok in ISO to omit date separators but include time
separators.
>>Why doesn't SQL Server consistently convert SQL-92 date strings?
>>Good question. It converts SQL-92 timestamp (without timezone)
>>correctly, but not date-only.
>
> No, it doesn't. That is where all this grief started: we've been
> sending that to SQL Server for years and in every other installation
> it has worked, but now we have a customer using MS SQL Server 2000 who
> is having that fail consistently and repeatedly on one of their boxes
> (they have another box running identical software on which it is not
> failing, and on our box which we've one everythintg possible to make
> identical it doesn't fail). I've done everything I can to find a
> difference in setup between the boxes and so far I've failed.

My slip. SQL Server doesn't understand SQL-92

TIMESTAMP '2003-02-22 23:34:43.123' at all, as in

CAST(TIMESTAMP '2003-02-22 23:34:43.123' as DATETIME)

but I doubt you are construction CAST(TIMESTAMP ...
expressions. SQL Server uses
{ts '1996-12-19 11:11:11.000'} to represent
a timestamp literal, and interprets it unambiguously,
as far as I know, as it does the date literal format of
{d '1996-12-19'}

Without the {ts ... }, these strings alone, like all
numeric delimited date formats, when implicitely
converted to dates follow the relative positions of
d and m in the dateformat setting in effect implicit
from the language selection or explicitly set.

set dateformat dmy
go
declare @.d datetime
set @.d = {ts '1996-12-19 11:11:11.000'}
select @.d
go
declare @.d datetime
set @.d = {d '1996-12-19'}
select @.d
go
declare @.d datetime
set @.d = '1996-12-19 11:11:11.000'
select @.d
go
declare @.d datetime
set @.d = '1996-12-19'
select @.d

I don't know why you would have trouble with this if the
settings were right, but maybe there's some driver parameter
buried in the registry, or some other setting that's not
obvious. Perhaps someone wanted us_english but dmy, and
got the bright idea of modifying the syslanguages table!

Does that bum server error out on this??

set dateformat dmy
declare @.d datetime
set @.d = '2003-02-19'

If the server is installed as us_english, and no one
has changed the dateformat setting or modified syslanguages,
I think it should work and might be a case for product
support. On the other hand, I wouldn't
want a product that depended on the language of installation.

SK|||Simon

This may be useless, but you don't seem to have a lot SQL Server
2000-specific info.
SQL server interprets data based on a 'collation' which is set at
install time, and can be overridden manually in an SQL statement.
To find the default collation for the database, the user will need to
right click on the SQL server instance in Enterprise Manager and
choose 'properties'. The default collation is displayed as part of
the basinc database information.
This can only be changed if the databases on the server are rebuilt.
Some information below from the SQL server 'man pages'

I haven't had the problems you describe, but if there is a
configuration difference between two installs, which is causing the
problem you describe, this is likely to be it.
From what I understand, you are saying that one installation processes
the dates OK, and the other does not. So SQL Server 2000 will do the
job, it is just not configured correctly on one of the servers.

good luck

Ben McIntyre

<snip>
-----------------------

Collation Options for International Support
In Microsoft SQL Server 2000, it is not required to separately
specify code page and sort order for character data, and the collation
used for Unicode data. Instead, specify the collation name and sorting
rules to use. The term, collation, refers to a set of rules that
determine how data is sorted and compared. Character data is sorted
using rules that define the correct character sequence, with options
for specifying case-sensitivity, accent marks, kana character types,
and character width. Microsoft SQL Server 2000 collations include
these groupings:

Windows collations
Windows collations define rules for storing character data based on
the rules defined for an associated Windows locale. The base Windows
collation rules specify which alphabet or language is used when
dictionary sorting is applied, as well as the code page used to store
non-Unicode character data. For more information, see Collations.

SQL collations
SQL collations are provided for compatibility with sort orders in
earlier versions of Microsoft SQL Server. For more information, see
Using SQL Collations.

Changing Collations After Setup
When you set up SQL Server 2000, it is important to use the correct
collation settings. You can change collation settings after running
Setup, but you must rebuild the databases and reload the data. It is
recommended that you develop a standard within your organization for
these options. Many server-to-server activities can fail if the
collation settings are not consistent across servers.

----------------------
How To

How to rebuild the master database (Rebuild Master utility)
To rebuild the master database

Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This
is located in the Program Files\Microsoft SQL Server\80\Tools\Binn
directory.

In the Rebuild Master dialog box, click Browse.

In the Browse for Folder dialog box, select the \Data folder on the
SQL Server 2000 compact disc or in the shared network directory from
which SQL Server 2000 was installed, and then click OK.

Click Settings. In the Collation Settings dialog box, verify or change
settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not
match the collation selected during setup. You can select the same
settings used during setup or select new collation settings. When
done, click OK.

In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.

Note To continue, you may need to stop a server that is running.
</snip>|||ben_spam@.mailcity.com (Ben McIntyre) writes:

> Simon
> This may be useless, but you don't seem to have a lot SQL Server
> 2000-specific info.
> SQL server interprets data based on a 'collation' which is set at
> install time, and can be overridden manually in an SQL statement.
> To find the default collation for the database, the user will need to
> right click on the SQL server instance in Enterprise Manager and
> choose 'properties'. The default collation is displayed as part of
> the basinc database information.
> This can only be changed if the databases on the server are rebuilt.
> Some information below from the SQL server 'man pages'
> I haven't had the problems you describe, but if there is a
> configuration difference between two installs, which is causing the
> problem you describe, this is likely to be it.
> From what I understand, you are saying that one installation processes
> the dates OK, and the other does not. So SQL Server 2000 will do the
> job, it is just not configured correctly on one of the servers.

OK, thanks for that. The collation sequence on the database on our
test box (which _does_ work) is SQL_Latin1_General_CP1_CI_AS. My
customer isn't yet in this morning so I can't ring and check what his
is.

I'll post a resolution once I get it sorted in case anyone searches
google any time in the future for a similar problem.

Just so that, in future, I know the general solution, which 'bit' of the
collation name is it which affects date sequencing? I mean, for
example, if in future I have a similar problem with a customer not in
the Latin1 area, what collation advice to I offer?

Cheers

Simon

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

[ This mind intentionally left blank ]|||> Just so that, in future, I know the general solution, which 'bit' of the
> collation name is it which affects date sequencing?

You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
might want to post the problem at hand again (It has been "aged out").

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver

"Simon Brooke" <simon@.jasmine.org.uk> wrote in message
news:87n0dtot6r.fsf@.gododdin.internal.jasmine.org. uk...
> ben_spam@.mailcity.com (Ben McIntyre) writes:
> > Simon
> > This may be useless, but you don't seem to have a lot SQL Server
> > 2000-specific info.
> > SQL server interprets data based on a 'collation' which is set at
> > install time, and can be overridden manually in an SQL statement.
> > To find the default collation for the database, the user will need to
> > right click on the SQL server instance in Enterprise Manager and
> > choose 'properties'. The default collation is displayed as part of
> > the basinc database information.
> > This can only be changed if the databases on the server are rebuilt.
> > Some information below from the SQL server 'man pages'
> > I haven't had the problems you describe, but if there is a
> > configuration difference between two installs, which is causing the
> > problem you describe, this is likely to be it.
> > From what I understand, you are saying that one installation processes
> > the dates OK, and the other does not. So SQL Server 2000 will do the
> > job, it is just not configured correctly on one of the servers.
> OK, thanks for that. The collation sequence on the database on our
> test box (which _does_ work) is SQL_Latin1_General_CP1_CI_AS. My
> customer isn't yet in this morning so I can't ring and check what his
> is.
> I'll post a resolution once I get it sorted in case anyone searches
> google any time in the future for a similar problem.
> Just so that, in future, I know the general solution, which 'bit' of the
> collation name is it which affects date sequencing? I mean, for
> example, if in future I have a similar problem with a customer not in
> the Latin1 area, what collation advice to I offer?
> Cheers
> Simon
> --
> simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
> [ This mind intentionally left blank ]|||Ben McIntyre (ben_spam@.mailcity.com) writes:
> SQL server interprets data based on a 'collation' which is set at
> install time, and can be overridden manually in an SQL statement.
> To find the default collation for the database, the user will need to
> right click on the SQL server instance in Enterprise Manager and
> choose 'properties'. The default collation is displayed as part of
> the basinc database information.

Collation apply to string columns, not to datetime columns.

The two commands that affect how strings is interpreted are
SET DATEFORMAT and SET LANGUAGE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.corners tone.se> writes:

> > Just so that, in future, I know the general solution, which 'bit' of the
> > collation name is it which affects date sequencing?
> You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
> might want to post the problem at hand again (It has been "aged out").

Ouch, I feared that.

Briefly, I have a piece of cross-platform Java code which is used in
production environments against at least five different database
backends. Many installations use SQL Server and have been running
reliably since 1998, and several installations use SQL Server 2000
with the com.microsoft.sqlserver.SqlServerDriver satisfactorily.

Yesterday, one of our customers reported a problem and on
investigation we found that their (new) installation wasn't accepting
dates properly. It would not accept the date 28th August 2003 at all,
and when (at my suggestion) they tried 4th August 2003, they got back
8th April 2003, which showed we had a date format problem.

The code asks the database for the column type of each column and
formats the data appropriately; because SQL Server doesn't support
date fields it responds that the date/time fields which on other
databases would be date fields are of type java.sql.Types.TIMESTAMP,
and consequently my code formats them as ANSI 92 timestamp format,
namely

yyyy-mm-dd hh:mm:ss.fffffffff

As I say, we've got loads of SQL Server installations which are
working quite happily with this. We've got exactly one which isn't. We
haven't been able to reproduce the bug on our test machine. We haven't
been able to identify any difference in configuration between the
machine that doesn't work and ones which do.

I'm very unwilling indeed to write special purpose code for different
database backends as it will lead to maintenance problems (I know this,
because we have one special purpose hack to work around an Oracle
misfeature). I'd like to resolve this problem if I can by specifying
the required SQL Server configuration.

We've today sent the customer a patch which dumps and deletes the
database and recreates it with the collation which we have on our
test box but it sounds from what you are saying as though this is
unlikely to work.

Can you offer any other suggestions?

Many thanks

Simon, not much impressed by Microsoft at the best of times.

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; Friends don't send friends HTML formatted emails.|||[microsoft.public.sqlserver.setup removed - can't send to two mail
servers at once, unfortunately...]

Simon,

Can you see what

DBCC USEROPTIONS

returns on the connection that is failing? And if there are no
differences from other servers, whether the syslanguages table has not
been modified?

Simon Brooke wrote:
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.corners tone.se> writes:
>
>>>Just so that, in future, I know the general solution, which 'bit' of the
>>>collation name is it which affects date sequencing?
>>
>>You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
>>might want to post the problem at hand again (It has been "aged out").
>
> Ouch, I feared that.
> Briefly, I have a piece of cross-platform Java code which is used in
> production environments against at least five different database
> backends. Many installations use SQL Server and have been running
> reliably since 1998, and several installations use SQL Server 2000
> with the com.microsoft.sqlserver.SqlServerDriver satisfactorily.
> Yesterday, one of our customers reported a problem and on
> investigation we found that their (new) installation wasn't accepting
> dates properly. It would not accept the date 28th August 2003 at all,
> and when (at my suggestion) they tried 4th August 2003, they got back
> 8th April 2003, which showed we had a date format problem.
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types.TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
> yyyy-mm-dd hh:mm:ss.fffffffff
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't. We
> haven't been able to reproduce the bug on our test machine. We haven't
> been able to identify any difference in configuration between the
> machine that doesn't work and ones which do.
> I'm very unwilling indeed to write special purpose code for different
> database backends as it will lead to maintenance problems (I know this,
> because we have one special purpose hack to work around an Oracle
> misfeature). I'd like to resolve this problem if I can by specifying
> the required SQL Server configuration.
> We've today sent the customer a patch which dumps and deletes the
> database and recreates it with the collation which we have on our
> test box but it sounds from what you are saying as though this is
> unlikely to work.
> Can you offer any other suggestions?
> Many thanks
> Simon, not much impressed by Microsoft at the best of times.|||Hi,

Simon Brooke wrote:

[...]

> So how, for the love of God and Little Fishes, do I persuade a SQL
> Server database to accept ANSI SQL 92 dates, permanently, not on a
> per-session basis?

Back when I was a ASP programmer the way do deal with this was to format the
date like "dd-MMM-yyyy", where "MMM" is the three-letter abbreviation of
the month.
This works because the Database understands how to read the dd-MMM-yyyy
format. this behaviour is not particular to SQL Server, I just tried it in
JDBC/PostgreSQL (don't have access to MSSQL right now) and it works
also... I would be surprised if it didn't worked in JDBC/MSSQL.

CREATE TABLE public.tbl_test
(
datefield date
) ;

********** JAVA *************
Connection c = getConnection();
PreparedStatement statement = c.prepareStatement("INSERT INTO
tbl_test(datefield) VALUES (?)");

statement.setObject(1, "10-Sep-2003");
statement.execute();
statement.clearParameters();
statement.close();
**********************************

SELECT * FROM tbl_test;
datefield
----
2003-09-10
(1 row)

There is a catch though, you have to be carefull with what you write as
"MMM", if the DB server is configured in other language other than English
the month abbreviation must comply to that language.

I hope this helps.

Regards,
Luis Neves|||Simon Brooke (simon@.jasmine.org.uk) writes:
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types.TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
> yyyy-mm-dd hh:mm:ss.fffffffff
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't.

Which smells no bit of luck, given that you post with a UK address.

Try this script:

SET DATEFORMAT dmy
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Fails
go
SET DATEFORMAT mdy
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Passes
go
SET LANGUAGE British
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Fails
go
SET LANGUAGE us_english
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Passes
go

The dateformat setting is a pure run-time setting. However, changing
language also changes the dateformat setting. And the language can
be set by a default on a login with sp_defaultlanguage. Finally, there
is a server configuration option that determines the default language
for new logins.

If your java app logs in with a certain login, you can probably mandate
that the default language of this login should be one that has a dateformat
of ymd or mdy, for instance Swedish.

If you can't mandate the language, it seems that you need to adapt your
app how much you hate it.

I should add that this problem appears because you are sending down
raw SQL statements to SQL Server, rather than parameterized queries
or RPC calls to stored procedures. If you do this, the client library
will handle the date format and pass SQL Server a binary value which
is not subject to settings. Whether this is possible to do in Java, I
have no idea, but client libraries such as ODBC and ADO supports it,
so why not JDBC?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve Kass <skass@.drew.edu> writes:

> [microsoft.public.sqlserver.setup removed - can't send to two mail
> servers at once, unfortunately...]
> Simon,
> Can you see what
> DBCC USEROPTIONS
> returns on the connection that is failing?

I'm sorry, how do I do this? I'm not by any means a SQL Server
expert. I tried it in query analyzer and got:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sbcc'

When I try it over the JDBC connection I get:
DBCC USEROPTIONS
SQL Error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No rows affected.
DBCC USEROPTIONS;

SQL Error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error at token 0, line 0 offset 0.

> And if there are no
> differences from other servers, whether the syslanguages table has not
> been modified?

There does not appear to be syslanguages table in the database. There
are plenty of other 'dbo.sysxxx' tables, but not syslanguages. This is
SQL Server 2000.

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

A message from our sponsor: This site is now in free fall|||"Simon Brooke" <simon@.jasmine.org.uk> wrote in message
news:878yp38qg1.fsf@.gododdin.internal.jasmine.org. uk...
> Steve Kass <skass@.drew.edu> writes:
> > [microsoft.public.sqlserver.setup removed - can't send to two mail
> > servers at once, unfortunately...]
> > Simon,
> > Can you see what
> > DBCC USEROPTIONS
> > returns on the connection that is failing?
> I'm sorry, how do I do this? I'm not by any means a SQL Server
> expert. I tried it in query analyzer and got:
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sbcc'

Umm, here is simply a typo. This will work in Query Analyzter.|||Simon Brooke (simon@.jasmine.org.uk) writes:
> Steve Kass <skass@.drew.edu> writes:
>> [microsoft.public.sqlserver.setup removed - can't send to two mail
>> servers at once, unfortunately...]
>>
>> Simon,
>>
>> Can you see what
>>
>> DBCC USEROPTIONS
>>
>> returns on the connection that is failing?
> I'm sorry, how do I do this? I'm not by any means a SQL Server
> expert. I tried it in query analyzer and got:
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sbcc'

As Greg Strider pointed out, you gave a typo, and I don't want to
be sarcastic or anything, but double-checking what you typed, before
you ask for help, may increase your effectivenesss.

> When I try it over the JDBC connection I get:
> DBCC USEROPTIONS
> SQL Error
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No rows
> affected.
> DBCC USEROPTIONS;
> SQL Error
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax
> error at token 0, line 0 offset 0.

Some DBCC commands produces their output as messages, which could
confuse some drivers. However, USEROPTIONS always produce a result set.
Maybe the JDBC driver is too smart for its own good and performs its
own parsing, and don't recognize the command. Not knowing about
JDBC I cannot really help.

> There does not appear to be syslanguages table in the database. There
> are plenty of other 'dbo.sysxxx' tables, but not syslanguages. This is
> SQL Server 2000.

syslanguages is in master. I would hold it as unlikely that someone
has changed syslanguages.

In any case, I seem to recall that I tried to explained exactly what
was going on a couple of days ago. Did you see that post?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Simon Brooke <simon@.jasmine.org.uk> writes:

> Briefly, I have a piece of cross-platform Java code which is used in
> production environments against at least five different database
> backends. Many installations use SQL Server and have been running
> reliably since 1998, and several installations use SQL Server 2000
> with the com.microsoft.sqlserver.SqlServerDriver satisfactorily.
> Yesterday, one of our customers reported a problem and on
> investigation we found that their (new) installation wasn't accepting
> dates properly. It would not accept the date 28th August 2003 at all,
> and when (at my suggestion) they tried 4th August 2003, they got back
> 8th April 2003, which showed we had a date format problem.
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types.TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
> yyyy-mm-dd hh:mm:ss.fffffffff
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't. We
> haven't been able to reproduce the bug on our test machine. We haven't
> been able to identify any difference in configuration between the
> machine that doesn't work and ones which do.

OK, just for the record here is the resolution of this issue.

What we found was that on the servers which worked, the user logins
used by the application had language set to 'English', and not either
'US English' or 'British English'. We set the language on the server
that didn't work to 'English', and it worked.

Many thanks to everyone who helped!

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
Das Internet is nicht fuer gefingerclicken und giffengrabben... Ist
nicht fuer gewerken bei das dumpkopfen. Das mausklicken sichtseeren
keepen das bandwit-spewin hans in das pockets muss; relaxen und
watchen das cursorblinken. -- quoted from the jargon filesql

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this ?
Thanks for concern.
You can't call the GETDATE() function in the proc execution. Bus you can declare a variable and
construct the subject into that variable (including today's date) and then pass that variable as a
parameter for subject in the xp_sendmail call.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform this
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this ?
> Thanks for concern.
sql

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this '
Thanks for concern.You can't call the GETDATE() function in the proc execution. Bus you can dec
lare a variable and
construct the subject into that variable (including today's date) and then p
XXX that variable as a
parameter for subject in the xp_sendmail call.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform th
is
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this '
> Thanks for concern.

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this '
Thanks for concern.You can't call the GETDATE() function in the proc execution. Bus you can declare a variable and
construct the subject into that variable (including today's date) and then pass that variable as a
parameter for subject in the xp_sendmail call.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform this
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this '
> Thanks for concern.

Month-to-month function

Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed

September:

@.CurrentDate = 9/10/2007

@.EndDate = last working day 9/30/2007

Total# of working days = 15

October:

@.CurrentDate = 10/1/2007

@.EndDate = last working day 10/31/2007

Total# of working days = 23

November:

@.CurrentDate = 11/1/2007

@.EndDate = last working day 11/30/2007

Total# of working days = 22

etc.

Any ideas of how i can approch this?

Thanks in advance.

If we just count out weekends, you can use this script. This will not take into account holidays.

Code Snippet

DECLARE @.startDate DATETIME,

@.dateTest DATETIME,

@.workDays INT

SET @.startDate = getDate()

SET @.dateTest = @.startDate

SET @.workDays = 0

WHILE( MONTH(@.startDate) = MONTH(@.dateTest) )

BEGIN

IF( DATENAME(dw, @.dateTest) != 'SATURDAY' AND DATENAME(dw, @.dateTest) != 'Sunday')

SET @.workDays = @.workDays + 1

SET @.dateTest = @.dateTest + 1

END

SELECT @.workDays

|||

You might try to search this forum on "Working Days". Also, give a look to this article about using a "calendar table:"

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

Code Snippet

create function udf_WeekdayCounter

(

@.dtFrom datetime

, @.dtThrough Datetime

)

returns smallint

begin

if @.dtThrough <= @.dtFrom

return 0

declare @.iCounter int

set @.iCounter = 0

declare @.dtFrom datetime, @.dtThrough datetime

set @.dtFrom = '11/1/2007'

set @.dtThrough = '11/30/2007'

while @.dtFrom < = @.dtThrough

begin

if datepart(weekday,@.dtFrom) not in (datepart(weekday,'December 30, 2006'), datepart(weekday,'December 31, 2006'))

set @.iCounter = @.iCounter + 1

set @.dtFrom = dateadd(d, 1, @.dtFrom)

end

return @.iCounter

end

select dbo.udf_WeekdayCounter( '11/1/2007', '11/30/2007')

sql

Months string

can sum one help with this:
I want 3 strings what represent the last three months from today's date inclusive.
for eg: if today is november 19th, 2003.
How would i get 3 strings as '2003_11', '2003_10', '2003_09'
Thanks a million.declare @.DateValue datetime
set @.DateValue = getdate()

select replace(convert(varchar(7), @.DateValue, 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -1, @.DateValue), 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -2, @.DateValue), 120), '-', '_')

You can ignore the REPLACE functions if you are picky about the format returned, but SQL Server does not have a datetime format using underscore characters.

blindman|||Thanks for the reply appreciate it.

Well this is what i'm doing with these strings:

I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables

Thanks a lot.|||9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman|||Originally posted by blindman
9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman

Well the original table was getting unmanagable, ie, it had about 160 million records and was growing fast. That why archiving the table into monthly tables was adopted. Now the original table will be split into monthyl tables and views are created for say each quarter, annual etc.|||Had you fully exhausted all the other possibilities for performance improvement?

Drives.
Processors.
Memory.
Indexing.
Normalization.
Pre-aggregation.
Query optimization.

Magic 8 Ball says outlook not good.

blindman|||Yes, indexing would take alot of space itself (almost the same as the table). The table is being used for reporting purposes so most of the time previous years data is not even touched, but still it's there in one big table. The whole process became slow due to the size of the table. Memory is not an issue. The current year's data is being used most often so monthly tables would give us more flexibility in viewing data.|||Did you try a 12 month partitioned view before this dynamic creation thing?

Or how about 13...make luck 13 an archive of infrequently used data, and set up an archive method...|||yes the 12 month archiving was thought of too.
for reporting etc. last 6 months data could be required. that that would make the 12 or 13 month archival method bogus. the monthly tables would be most flexible.

Another question: Is there a way to get the records counts for all the tables on (server a) and save those counts on a table in server B. And we r running this script or proc from server b.

server a and server b or not on a trusted connection but a password and userid are given.|||The 13th "month" would all previous yeard data...then you create a partitioned view to see all of the data

As for counts

Did you set up a linked server?|||Well the original table has data from jan 2001 till present.

for counts

i'm using openrowset(...) to get all the table names on server a. Would linked server be better? if so how would u setup linked servers? ne sample code would be helpful

thx|||I have setup linked servers, but how would you get the tables counts of all the tables on the linked server?|||Do you mean like SELECT COUNT(*) FROM myTable99?

Theres alos sp_spaceused, and a system table contains the info...

All of which (except SELECT COUNT(*)) need to statistics run to make sure the current..|||I cant do the select count(*) becuase in the sysservers tabe the linked i added has a null value in the srvnetname column. But all the sp_columns_ex and sp_tables_ex command work.

Friday, March 23, 2012

Months and Years between Dates

Hi All,
I am stuck with a Date problem which I am trying to execute in a Stored Proc.

I basically get a Start Month and a Start Year And an End month & an End Year from a screen that I build.
Now what I want to do is to traverse all the Months_Year for this period.

ie For example if my start year is Feb 2001 and end Year is July 2004, then I need
Feb 2001
March 2001
April 2001
....
Jan 2004
..
July 2004 in a cursor.

Thanks in anticipation.
Raman.Hmmm what database are you using Oracle or Sybase ?
if its Sybase you can use the datediff to get your dates
if in Oracle you can use the to_date, to_char to manipulate the given dates and return what you want.

Originally posted by ramanjaiya
Hi All,
I am stuck with a Date problem which I am trying to execute in a Stored Proc.

I basically get a Start Month and a Start Year And an End month & an End Year from a screen that I build.
Now what I want to do is to traverse all the Months_Year for this period.

ie For example if my start year is Feb 2001 and end Year is July 2004, then I need
Feb 2001
March 2001
April 2001
....
Jan 2004
..
July 2004 in a cursor.

Thanks in anticipation.
Raman.|||Originally posted by llccoo
Hmmm what database are you using Oracle or Sybase ?
if its Sybase you can use the datediff to get your dates
if in Oracle you can use the to_date, to_char to manipulate the given dates and return what you want.

I am using SQL Server.
Basically What I amtrying now is to create 2 Temp tables. One with all the months, and one with all the years & then looping twice to create my combinations.
I feel it could be done in a better way although!|||please see the articles The integers table (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid569539_tax285649,00.html) and Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) (registration may be required, but it's free)

the examples show how to generate a series of dates using an integers table

applied to your example, you would use the integers within a DATEADD() function using the integer as the number of months to add from a starting date

Monthname in SQL server

Being new to SQL server T-SQL (working with DB2 / ORACLE) is there a function like monthname() in SQL server. I.e. a function that takes date as input and returns the Monthname (like AUG or AUGUST)??This example extracts the month name from the date returned by GETDATE.

SELECT DATENAME(month, getdate()) AS 'Month Name'

Here is the result set:

Month Name
----------
February|||Sorry, where can I put the date where I can extract the monthname from?
I want to add an object that displays 'JAN' or Januari if I have a date like:
01/01/2003|||SELECT DATENAME(month, '01/01/2003') AS 'Month Name'|||Thank you very much..............

Monthly Reports - Date Format

I have a report that charts our weekly sales and monthly sales. I use the
following SQL statement to reflect 7 days or 30 days, which works but not
properly. I'd like for my report to run from Sunday to Sunday for the 7-day
report, and from the 1st of the month to the end of the month, regardless of
the number of days in the month. How do I modfify my statement to do this?
Here's my SQL statement for the 30-day or monthly report:
SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
(*) AS TOT
FROM Sysadm.receipt
WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
-30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
101), 101))
GROUP BY receipt_date, receipt_no, stamp
ORDER BY receipt_date
--
EnchantnetI used the following statement to run a report for the previous week, from
Saturday to Friday:
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
101), 101) AS StartDt,
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
101), 101) AS EndDt
You could probably use a variation of this for your monthly report. I hope
this helps!
"Enchantnet" wrote:
> I have a report that charts our weekly sales and monthly sales. I use the
> following SQL statement to reflect 7 days or 30 days, which works but not
> properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> report, and from the 1st of the month to the end of the month, regardless of
> the number of days in the month. How do I modfify my statement to do this?
> Here's my SQL statement for the 30-day or monthly report:
> SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> (*) AS TOT
> FROM Sysadm.receipt
> WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> 101), 101))
> GROUP BY receipt_date, receipt_no, stamp
> ORDER BY receipt_date
> --
> Enchantnet|||Hey DAW,
Thanks for the code. However, I'm a newbie at this sort of stuff and I get
the following error when I entered your code: "ADO error: Incorrect syntax
near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
could not be completed." Any idea? Do I need to include or define the
StartDt and EndDt elsewhere?
--
Enchantnet
"daw" wrote:
> I used the following statement to run a report for the previous week, from
> Saturday to Friday:
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> 101), 101) AS StartDt,
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> 101), 101) AS EndDt
> You could probably use a variation of this for your monthly report. I hope
> this helps!
> "Enchantnet" wrote:
> > I have a report that charts our weekly sales and monthly sales. I use the
> > following SQL statement to reflect 7 days or 30 days, which works but not
> > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > report, and from the 1st of the month to the end of the month, regardless of
> > the number of days in the month. How do I modfify my statement to do this?
> > Here's my SQL statement for the 30-day or monthly report:
> >
> > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > (*) AS TOT
> > FROM Sysadm.receipt
> > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > 101), 101))
> > GROUP BY receipt_date, receipt_no, stamp
> > ORDER BY receipt_date
> > --
> > Enchantnet|||I'm not sure what that particular error means, but your statement should look
something like:
select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
from ....
"Enchantnet" wrote:
> Hey DAW,
> Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> the following error when I entered your code: "ADO error: Incorrect syntax
> near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> could not be completed." Any idea? Do I need to include or define the
> StartDt and EndDt elsewhere?
> --
> Enchantnet
>
> "daw" wrote:
> > I used the following statement to run a report for the previous week, from
> > Saturday to Friday:
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > 101), 101) AS StartDt,
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > 101), 101) AS EndDt
> >
> > You could probably use a variation of this for your monthly report. I hope
> > this helps!
> >
> > "Enchantnet" wrote:
> >
> > > I have a report that charts our weekly sales and monthly sales. I use the
> > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > report, and from the 1st of the month to the end of the month, regardless of
> > > the number of days in the month. How do I modfify my statement to do this?
> > > Here's my SQL statement for the 30-day or monthly report:
> > >
> > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > (*) AS TOT
> > > FROM Sysadm.receipt
> > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > 101), 101))
> > > GROUP BY receipt_date, receipt_no, stamp
> > > ORDER BY receipt_date
> > > --
> > > Enchantnet|||Sorry, I had that wrong as far as what you need. Try this:
select ....
from...
where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
"daw" wrote:
> I'm not sure what that particular error means, but your statement should look
> something like:
> select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> from ....
> "Enchantnet" wrote:
> > Hey DAW,
> >
> > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > the following error when I entered your code: "ADO error: Incorrect syntax
> > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > could not be completed." Any idea? Do I need to include or define the
> > StartDt and EndDt elsewhere?
> > --
> > Enchantnet
> >
> >
> > "daw" wrote:
> >
> > > I used the following statement to run a report for the previous week, from
> > > Saturday to Friday:
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS StartDt,
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS EndDt
> > >
> > > You could probably use a variation of this for your monthly report. I hope
> > > this helps!
> > >
> > > "Enchantnet" wrote:
> > >
> > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > the number of days in the month. How do I modfify my statement to do this?
> > > > Here's my SQL statement for the 30-day or monthly report:
> > > >
> > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > (*) AS TOT
> > > > FROM Sysadm.receipt
> > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > 101), 101))
> > > > GROUP BY receipt_date, receipt_no, stamp
> > > > ORDER BY receipt_date
> > > > --
> > > > Enchantnet|||Perfect! Thank u, Thank u, Thank u!!!!!
--
Enchantnet
"daw" wrote:
> Sorry, I had that wrong as far as what you need. Try this:
> select ....
> from...
> where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
> DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
> "daw" wrote:
> > I'm not sure what that particular error means, but your statement should look
> > something like:
> >
> > select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> > GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> > GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> > from ....
> >
> > "Enchantnet" wrote:
> >
> > > Hey DAW,
> > >
> > > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > > the following error when I entered your code: "ADO error: Incorrect syntax
> > > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > > could not be completed." Any idea? Do I need to include or define the
> > > StartDt and EndDt elsewhere?
> > > --
> > > Enchantnet
> > >
> > >
> > > "daw" wrote:
> > >
> > > > I used the following statement to run a report for the previous week, from
> > > > Saturday to Friday:
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS StartDt,
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS EndDt
> > > >
> > > > You could probably use a variation of this for your monthly report. I hope
> > > > this helps!
> > > >
> > > > "Enchantnet" wrote:
> > > >
> > > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > > the number of days in the month. How do I modfify my statement to do this?
> > > > > Here's my SQL statement for the 30-day or monthly report:
> > > > >
> > > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > > (*) AS TOT
> > > > > FROM Sysadm.receipt
> > > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > > 101), 101))
> > > > > GROUP BY receipt_date, receipt_no, stamp
> > > > > ORDER BY receipt_date
> > > > > --
> > > > > Enchantnet

Monthly Reports

Hi,
Actually I am generating report by stored procedure by passing two parameters like START date and END date.
Now my problem is I need to generate report for every month seperately that fall in range of START date and END date.
thanks
IndiraIndira,
Group by Date, and when creating the group, if you use a date, Crystal will, by default, give the grouping options:
the date, in a dropdown
the order to sort by, in a dropdown,
the grouping frequency, in a dropdown (defaults to "for each day.")
change the last one to "for each month."
Crystal will then produce your summaries for each calendar month within the range.
You can then add a page break after this date group, to start anew page with a new month.
That's how you'd produce one report covering many months, aggregated by each month.
YOur question says you want to produce separate monthly reports.
If they are paper copies, you just restart your page numbering for each new group, print the report out, and then distribute the separate months pages as separate reports.
If you want to produce separate files, you will need to outline which method you want to use to distribute the separate reports, because thismight require code via VB or C etc...

Dave

Monthly parameter expressions [Formerly:Queried parameters]

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

sql

Monthly date range substitution

I would like to run a report for each month over two years. I am currently
using a date range like this. Then manually substitute the error_time
bounds for each month and rerun the query. How can I script this so I can
programmatically perform the substitution in a loop. Thanx in advance.

select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Apr2004' and error_time < '1May2004'Robert (robert.j.sipe@.boeing.com) writes:
> Maybe this is a lot easier to do than I first thought:
> select count(*) from application_errors
> where error_message like 'Time%'
> and error_time >= '1Jan2004' and error_time < '1Jan2005'
> group by month (error_time)
> This saves me a lot of work. Now, if I could figure out how to span years
> and still group by months...

select convert(char(6), error_time, 112), count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004'
group by convert(char(6), error_time, 112)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx Erland, I am not worthy!!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns975E38A7771EYazorman@.127.0.0.1...
> Robert (robert.j.sipe@.boeing.com) writes:
>> Maybe this is a lot easier to do than I first thought:
>>
>> select count(*) from application_errors
>> where error_message like 'Time%'
>> and error_time >= '1Jan2004' and error_time < '1Jan2005'
>> group by month (error_time)
>>
>> This saves me a lot of work. Now, if I could figure out how to span
>> years
>> and still group by months...
>
> select convert(char(6), error_time, 112), count(*) from
> application_errors
> where error_message like 'Time%'
> and error_time >= '1Jan2004'
> group by convert(char(6), error_time, 112)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Robert wrote:
> Thanx Erland, I am not worthy!!

You can as well use DATEPART to extract year and month from the timestamp
column.

robert

> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns975E38A7771EYazorman@.127.0.0.1...
>> Robert (robert.j.sipe@.boeing.com) writes:
>>> Maybe this is a lot easier to do than I first thought:
>>>
>>> select count(*) from application_errors
>>> where error_message like 'Time%'
>>> and error_time >= '1Jan2004' and error_time < '1Jan2005'
>>> group by month (error_time)
>>>
>>> This saves me a lot of work. Now, if I could figure out how to span
>>> years
>>> and still group by months...
>>
>>
>> select convert(char(6), error_time, 112), count(*) from
>> application_errors
>> where error_message like 'Time%'
>> and error_time >= '1Jan2004'
>> group by convert(char(6), error_time, 112)
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>>
http://www.microsoft.com/technet/pr...oads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Create a report range table:

CREATE TABLE ReportRanges
(range_name CHAR(15)
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
..);

INSERT INTO ReportRanges
VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');

INSERT INTO ReportRanges
VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');

etc.

INSERT INTO ReportRanges
VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');

Now use it to drive all of your reports, so they will be consistent.

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A, ReportRanges AS R
WHERE A.error_time BETWEEN R.start_date AND R.end_date
GROUP BY R.range_name;

You are making a classic newbie design flaw. You still think of
programming with procedural code and functions, but not with relational
operators.|||> CREATE TABLE ReportRanges
> (range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);

This non-table is unusable. It has no key and cannot have a key because
range_name is NULLable.

> INSERT INTO ReportRanges
> VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');

Do you really think it a good idea to use the month name in the data like
this? What about other languages - French, Italian etc...

> SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
> WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;

You are still using the 89 syntax and should be using the more recent 92
syntax.

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A
CROSS JOIN ReportRanges AS R
WHERE A.error_time BETWEEN R.start_date AND R.end_date
GROUP BY R.range_name;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138908826.954654.5580@.g47g2000cwa.googlegrou ps.com...
> Create a report range table:
> CREATE TABLE ReportRanges
> (range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);
> INSERT INTO ReportRanges
> VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');
> INSERT INTO ReportRanges
> VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');
> etc.
> INSERT INTO ReportRanges
> VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');
> Now use it to drive all of your reports, so they will be consistent.
> SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
> WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;
> You are making a classic newbie design flaw. You still think of
> programming with procedural code and functions, but not with relational
> operators.|||On 2 Feb 2006 11:33:47 -0800, --CELKO-- wrote:

>Create a report range table:
>CREATE TABLE ReportRanges
>(range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);
>INSERT INTO ReportRanges
>VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');
>INSERT INTO ReportRanges
>VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');
>etc.
>INSERT INTO ReportRanges
>VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');

Hi Joe,

1. Never omit the column list of an INSERT. THis, like SELECT *, is
extremely bad practice.

2. Please use unambiguous date formats:

* yyyymmdd for date only
* yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.ttt for date plus time
(with or without milliseconds).

3. Because SQL Server has datetime precision of 1/300 seecond, the
values for end_date will be rounded UP to 2005-02-01T00:00:00.000,
2005-03-01T00:00:00.000, and 2006-01-01T00:00:00.000. Not the values you
want with the query you propose...

>Now use it to drive all of your reports, so they will be consistent.
>SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
>WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;

.... however, this query is no good either. Never use BETWEEN for date
comparisons.

You should populate the Reportanges table as follows:

INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Jan', '20050101', '20050201');
INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Feb', '20050201', '20050301');
(...)
INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Total', '20050101', '20060101');

And change the query to

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A
INNER JOIN ReportRanges AS R
ON A.error_time >= R.start_date
AND A.error_time < R.end_date
GROUP BY R.range_name;

(Note the use of greater _OR EQUAL_ for start_date, but lesser (and not
equal) for end_date).
This will always work - both for datetime and smalldatetime, and it will
continue to work if Microsoft ever decides to change the precision of
their datetime datatypes.

--
Hugo Kornelis, SQL Server MVP|||>> Note the use of greater _OR EQUAL_ for start_date, but lesser (and not equal) for end_date). This will always work - both for datetime and smalldatetime, and it will
continue to work if Microsoft ever decides to change the precision of
their datetime datatypes. <<

Good point. I keep forgetting that SQL Server does not follow the
FIPS-127 rules about keeping at least five decimal places of seconds
like other products. Generally goiing to 1/100 of a second has worked
for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
DATETIME).

If we had the OVERLAPS predicate, we could use that, but I prefer the
BETWEEN with adjusted times in the non-conformng SQLs I use. I can
move the code with a text change.|||On 3 Feb 2006 17:38:40 -0800, --CELKO-- wrote:

>>> Note the use of greater _OR EQUAL_ for start_date, but lesser (and not equal) for end_date). This will always work - both for datetime and smalldatetime, and it will
>continue to work if Microsoft ever decides to change the precision of
>their datetime datatypes. <<
>Good point. I keep forgetting that SQL Server does not follow the
>FIPS-127 rules about keeping at least five decimal places of seconds
>like other products. Generally goiing to 1/100 of a second has worked
>for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
>DATETIME).

Hi Joe,

This will still bite you if smalldatetime is used. Or if ever an entry
makes it into the datebase with a 23:59:99.993 timestamp.

What is your objection to
SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval
(with EndOfInterval actually being equal to the first fraction of a
second after the end of the interval, or the start of the next interval
if there are consecutive intervals)

AFAICT, this will work on ALL products, regardless of the precision of
the date and time datatypes used in the product. Am I wrong?

--
Hugo Kornelis, SQL Server MVP|||>> his will still bite you if smalldatetime is used. Or if ever an entrymakes it into the datebase with a 23:59:99.993 timestamp. <<

I never use SMALLDATETIME because it is soooo proprietary and does not
match the FIPS-127 requirements.

>> What is your objection to
SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval <<

Mostly style and portable code. The BETWEEN predicate reads so much
better to a human. I would prefer OVERLAPS and some of Rick
Snodgrass's operators if i coudl get them.

>> FAICT, this will work on ALL products, regardless of the precision of the date and time datatypes used in the product. Am I wrong? <<

Yeah, yeah!! But I hate 5to split a single concept (between-ness) into
muliple predicates. I also hate a change of ORs when I can use IN(),
etc.

--|||On 4 Feb 2006 14:22:07 -0800, --CELKO-- wrote:

>>> his will still bite you if smalldatetime is used. Or if ever an entrymakes it into the datebase with a 23:59:99.993 timestamp. <<
>I never use SMALLDATETIME because it is soooo proprietary and does not
>match the FIPS-127 requirements.

Hi Joe,

So instead, you use DATETIME, which also is proprieatary, which also
doesn't match FIPS-127, and which takes twice the space. Good job. For a
table with mostly date columns, your performance will now be about twice
as slow.

>>> What is your objection to
> SomeDate >= StartOfInterval
> AND SomeDate < EndOfInterval <<
>Mostly style and portable code.

Style, like beauty, is in the eye of the beholder. So I won't comment on
that.

But "portable code"? <Cough!> Please tell me: what part of the code
above is not portable, and why?

> The BETWEEN predicate reads so much
>better to a human.

Maybe. But does '2006-02-28T23:59:59.997' also read better to a human
than '2006-03-01'?

SomeDate >= '2006-02-01'
AND SomeDate < '2006-03-01'

SomeDate BETWEEN '2006-02-01' AND '2006-02-28T23:59:59.997'

Are you really going to tell me that the latter reads better to a human?

--
Hugo Kornelis, SQL Server MVP

Month/Year comparison against a date

Hi,

Is there a way to get the last day of a month, given a date in a datetime variable?

I have a stored procedure that accepts a datetime parameter. I need to find the last day of the month for that parameter value. For example, if the stored procedure is passed the datetime value, '6/13/2007', I need to be able to get from that '6/30/2007.'

Here's the bigger picture: The sp actually takes two datetime parameters (unfortunately, I don't have access to change the user interface). The sp needs to select records between the month/years of those dates, not including the first but including the second. For example, if the user specifies the following dates:

6/7/2006
7/15/2007

the sp needs to select all records that come after 6/30/2006 and on or before 7/31/2007.

I've tried this: ("ReportDate" is the name of the datetime field in a table in the sp and "@.StartDate" and "@.EndDate" are the datetime parameters in the sp)

Month(ReportDate) > Month(@.StartDate) And Year(ReportDate) >= Year(@.StartDate) And
Month(ReportDate) <= Month(@.EndDate) And Year(ReportDate) <= Year(@.EndDate)

But this returns fewer records than when I enter 6/30/2006 and 7/31/2007 as the parameters and just compare dates like this:

ReportDate > @.StartDate And ReportDate <= @.EndDate

Thank you.

Something like this:

Code Snippet

WHERE ( ReportDate >= dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )
)

You do NOT want to enclose ReportDate in a function since that will most likely result in not using indexing.

|||

WHERE ( ReportDate > dateadd( month, datediff( month, 0, @.StartDate ) , -1 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )
)

I just edited the previous reply to make it more close to your requirement..

|||

I don't think that is exactly 'right'. (I did make the assumption that the OP wanted ONLY JULY 2007 dates.)

Follow this code:

Code Snippet

DECLARE
@.StartDate datetime,
@.EndDate datetime

SELECT
@.StartDate = '06/7/2007',
@.EndDate = '7/15/2007'

-- Arnie's Variation
-- Starting at midnight, 7/1/2007, Ending at midnight, 8/1/2007
SELECT
'Arnie',
dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )

-- Mandip's Variation
-- Starting at midnight, 5/31/2007, ending at midnight, 8/1/2007
SELECT
'Mandip',
dateadd( month, datediff( month, 0, @.StartDate ) , -1 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )

-- OP Requested
-- the sp needs to select all records that come
-- after 6/30/2006 and on or before 7/31/2007

-- -
Arnie 2007-07-01 00:00:00.000 2007-08-01 00:00:00.000

Mandip 2007-05-31 00:00:00.000 2007-08-01 00:00:00.000

Month value in SQL

Hi all

Does anyone know how to get the month field from a date in a SQL statement? I am using Oracle 8.0

There is a field called "birth_date" in my table and I have to print the number of records group by month as follows:

Month Count
--- ---
1 45
2 187
3 18
. ..
. ..
. ..
etc

I need the month value in 1-12 format. Please help..

-Chinnaselect month(birth_date),count(*)
from yourtable
group by month(birth_date)

rudy|||There is not "month" function in Oracle!
that shoulde be:
select to_char(brith_day,'mm'),count(*) from yourtable group by to_char(brith_day,'mm')|||select to_char(sysdate,'MON') from dual;

will return today's month|||MONTH() is ANSI/ISO standard sql

my apologies, Chinna, i overlooked the fact that you said oracle 8

oracle corp, in its infinite wisdom, apparently did not begin supporting standard sql until release 9

:(

Month Name

I am trying to get the month name out of the date but need only the short form, like Aug and not August, Sep for September etc..

I am using the following to achieve this:

DateName(month, GetDate( )) = June

LEFT(DateName(month, GetDate( )),3) = Jun

Which does the trick. But is there an elegant way of returning short month name?

Thx.

Nope.. We have onlye Left & datename.. You are on the rite track...

Following query is another trick

Select Cast(Datename(Month,getdate()) as Char(3))

|||

Hmmm. I guess there is only one more way...its ugly too

SubString(DateName(month, GetDate( )), 1, 3)

|||

Code Snippet

selectconvert(char(3),getdate(), 0)

|||

And my personal favorite:

SELECT left( datename( month, getdate()), 3 )

sql

month end date...

Hello:
If I have middle month date ,
for example '2005-04-22',
how can I get end month date, in this case
'2005-04-30'?
I need a solution for any date.
Thanks,
GBTry:
dateadd(d, -(day(@.dt)),@.dt)
"GB" <v7v1k3@.hotmail.com> wrote in message
news:ro2Pf.22370$Ui.5143@.edtnps84...
> Hello:
> If I have middle month date ,
> for example '2005-04-22',
> how can I get end month date, in this case
> '2005-04-30'?
> I need a solution for any date.
> Thanks,
> GB|||DECLARE @.date SMALLDATETIME;
SET @.date = '20050422';
DECLARE @.start SMALLDATETIME, @.endDay SMALLDATETIME;
SET @.start = DATEDIFF(DAY,0,@.date);
SET @.endDay = DATEADD(MONTH,1,@.date)-DAY(@.start);
SELECT @.endDay;
"GB" <v7v1k3@.hotmail.com> wrote in message
news:ro2Pf.22370$Ui.5143@.edtnps84...
> Hello:
> If I have middle month date ,
> for example '2005-04-22',
> how can I get end month date, in this case
> '2005-04-30'?
> I need a solution for any date.
> Thanks,
> GB
>|||mason, that gives the last day of the *previous* month...
"mason" <masonliu@.msn.com> wrote in message
news:%23EIDutWQGHA.1676@.TK2MSFTNGP14.phx.gbl...
> Try:
> dateadd(d, -(day(@.dt)),@.dt)
>
> "GB" <v7v1k3@.hotmail.com> wrote in message
> news:ro2Pf.22370$Ui.5143@.edtnps84...
>|||This solution gives PREVIOUS month date,
for '2005-04-22' it gives '2005-03-31'
I need current month end date, in this case '2005-04-30'.
Thanks,
GB
"mason" <masonliu@.msn.com> wrote in message
news:%23EIDutWQGHA.1676@.TK2MSFTNGP14.phx.gbl...
> Try:
> dateadd(d, -(day(@.dt)),@.dt)
>
> "GB" <v7v1k3@.hotmail.com> wrote in message
> news:ro2Pf.22370$Ui.5143@.edtnps84...
>|||Misfire. :p
dateadd(d, - (day(dateadd(m,1,@.dt))),dateadd(m,1,@.dt)
)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eEPYvyWQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> mason, that gives the last day of the *previous* month...
>
> "mason" <masonliu@.msn.com> wrote in message
> news:%23EIDutWQGHA.1676@.TK2MSFTNGP14.phx.gbl...

Month and Day Date Selection

Hi Folks:

I'm running a query whereby my users will select between "FromCloseDate" and "ToCloseDate". The easy part is when they're searching for month, day and year between the close dates however, they have a boolean report parameter that allow them to select month and day between the close dates. Has anyone done a between date selection for month and day?

Thanks in advance

Couldn't you just use the MONTH() and DAY() functions to extract the month and day for use in your query? Or you could use SUBSTRING().

Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

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

|||

Hi, Confused

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

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

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql

Monday, March 26, 2012

more date problems...

More date format grief

More About SQL Mail

More About SQL Mail

More About SQL Mail

Month-to-month function

Months string

Friday, March 23, 2012

Months and Years between Dates

Monthname in SQL server

Monthly Reports - Date Format

Monthly Reports

Monthly parameter expressions [Formerly:Queried parameters]

Monthly date range substitution

Month/Year comparison against a date

Month value in SQL

Month Name

month end date...

Month and Day Date Selection

Month and date wrong way around

Money Data Type

Blog Archive

About Me