DB architecture

Hi there.
We are building a new mission critical application in our company, using SQL
server 2000 as the RDBMS. The new database is replacing a legacy system that
used to run in two platforms: the day to day operations (OLTP) was
maintained in a small DB2 database running on a OS/2 PC (only 1 week of
data) and the rest was moved periodically to an iseries IBM server (DB2).
all the OLTP was done on the PC, and most of the reporting was done against
the iseries DB2 database.
So far we only have one database to replace the two systems mentioned. We
are planning to either separate the data and have the reporting done in
another SQL server machine (with the same schema,and using log shipping) or
create a set of tables that would pre-process the information and would be
used by the reporting tools. When planning for theses, we created a set of
views tha are being use by our current reports (this layering protects us if
we need to change the underlying schema). The current DB is highly
normalized and I don't think would operate well for OLAP. Is the log
shipping approach recommended? Is there a better alternative? I wouldn'
like to have to maintain two different schemas.
Thank you,
Pedro."PeyoQuintero" <pedroquintero@.earthlink.net> wrote in message
news:u73cc.10784$NL4.2990@.newsread3.news.atl.earthlink.net...
> Hi there.
> We are building a new mission critical application in our company, using
SQL
> server 2000 as the RDBMS. The new database is replacing a legacy system
that
> used to run in two platforms: the day to day operations (OLTP) was
> maintained in a small DB2 database running on a OS/2 PC (only 1 week of
> data) and the rest was moved periodically to an iseries IBM server (DB2).
> all the OLTP was done on the PC, and most of the reporting was done
against
> the iseries DB2 database.
> So far we only have one database to replace the two systems mentioned. We
> are planning to either separate the data and have the reporting done in
> another SQL server machine (with the same schema,and using log shipping)
or
> create a set of tables that would pre-process the information and would be
> used by the reporting tools. When planning for theses, we created a set of
> views tha are being use by our current reports (this layering protects us
if
> we need to change the underlying schema). The current DB is highly
> normalized and I don't think would operate well for OLAP. Is the log
> shipping approach recommended? Is there a better alternative? I wouldn'
> like to have to maintain two different schemas.
> Thank you,
>
The answer, as so often with these sorts of questions, is "It depends". If
you want the maximum performance then on the OLTP database have a highly
normalized schema and few indexes. Normalized data means the integrity of
your data is easily maintained.
On the OLAP database, if no-one is updating the data directly, then you
already know the data is correct. Normalization is no longer required, and
the speed of your queries can be improved by creating tables that reflect
your views. No messy or slow joins for SQL to deal with. Bung in your
indexes to speed the filtering and grouping of your data. Include
calculated and aggragated data directly in your tables.
To maintain these different schemas create DTS jobs to transform and move
your data from one database to the other.
Of course, the downside of this approach, as you've identified, is
maintaining two schemas, but the OLAP database (apart from the automated DTS
jobs) is a read-only database, and therefore should not need that much
maintaining once up and running. Also, log shipping will typically have
less latency, but in your old model you imply that there was a weekly
upload, so that would not be an issue.
Log Shipping works only if both databases have the same schema. It is much
simpler than DTS, but less flexible as well.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004

DB access for web apps

I have custom .net web based application running on IIS 6 which has SQL2000
running in background, access to this application is based on UIDs and
password from "users" table in SQL, my question is regarding web.config
file... this file has user name and password that allow web application talk
to SQL db, what sql role should this account have in order to .net
application work corectly? db owner will do but I'm wondering this is too
much...
TIAFor a qick improvement, membership in the
db_datareader (can select all data from any user table in the database) and
db_datawriter (can modify any data in any user table in the database)
roles should be enough. Then you can study grainer permissions needed.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> I have custom .net web based application running on IIS 6 which has
SQL2000
> running in background, access to this application is based on UIDs and
> password from "users" table in SQL, my question is regarding web.config
> file... this file has user name and password that allow web application
talk
> to SQL db, what sql role should this account have in order to .net
> application work corectly? db owner will do but I'm wondering this is too
> much...
> TIA|||thanks for response, will this allow execute sp_ ?
"Dejan Sarka" wrote:

> For a qick improvement, membership in the
> db_datareader (can select all data from any user table in the database) an
d
> db_datawriter (can modify any data in any user table in the database)
> roles should be enough. Then you can study grainer permissions needed.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
> "Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
> news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> SQL2000
> talk
>
>|||For stored procedures in your database, you will have to give an explicit
EXECUTE permission to this user. I you are talking about system procedures
to get some info, like sp_help, then the user will be able to execute them
without an explicit permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:F1EDD860-343A-421A-A303-2FBB25687147@.microsoft.com...[vbcol=seagreen]
> thanks for response, will this allow execute sp_ ?
> "Dejan Sarka" wrote:
>
and[vbcol=seagreen]
web.config[vbcol=seagreen]
application[vbcol=seagreen]
too[vbcol=seagreen]sql

DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

Datetime problem

an existing application sends server an sql string like
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
where c1 is an int, and c2 is a datetime field. This command returns an erro
r.
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
when I change that command like following
SET DATEFORMAT dmy
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
it works fine.
I want to set server always accepts dates im dmy format.
What can I do for this.
Thanks in advanceCould you instead pass dates in the following format? It always works:
YYYYMMDD HH:MM:SS
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance|||that way requires application change. Actually there is lots of data in dmy
format. When server changed to SQL 2000, application get following error
message from server. It was work fine with previous version SQL server, but
not SQL 2000
"Narayana Vyas Kondreddi" wrote:

> Could you instead pass dates in the following format? It always works:
> YYYYMMDD HH:MM:SS
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote
in
> message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> error.
>
>|||This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...
datetime.asp, more
specifically rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote in
message
news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
> that way requires application change. Actually there is lots of data in dm
y
> format. When server changed to SQL 2000, application get following error
> message from server. It was work fine with previous version SQL server, bu
t
> not SQL 2000
> "Narayana Vyas Kondreddi" wrote:
>|||When I set logins default language by enterpirse manager, it runs normal.
Thanks for help :)
"Tibor Karaszi" wrote:

> This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...o_datetime.asp, more
> specifically /url]
> --
> Tibor Karaszi, SQL Server MVP
> [url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote i
n message
> news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
>|||Create INSTEAD OF trigger on your table and reformat an input in it.
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
> error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance

datetime problem

my asp .net application is coming along nicely. however, i would like to record the users last login time. i have a datetime field, and when i insert or update using Now() as the data for the field, i simply get 1/1/1900 12:00:00 in the last_login field. Same thing using Today(). any suggestions? am i using the wrong data type? i need to be able to do date comparisons as i plan on connecting my site with a forum, and this would be the easiest way to determine if there were new posts from the user's perspective.

TIA

Use GetDate() in your SQL to input the current time.|||

Unless your business object has a say in what the date/time stamp value is, there is little reason to send it over the network both ways. Might as well have the database provide the value and hand it back to you.

|||

thanks mikesdotnetting! worked like a charm!

|||

Don't forget that getdate(0 and Now() work on different servers which can be in different time zones :)

DateTime Issue

I don't like store time in smalldatetime field owing to application limitation, Is there any solution, let me help matter is most urgent and my application is too big.
Thanking You
R.MallWhat is the application limitation?? do u need to run queries aganist this column??|||What is the application limitation?? do u need to run queries aganist this column??

In totality it is big application already developped in PowerBuilder 7.3 and fullfill the need of business. That was with sybase, rightnow I am looking to port this data into MSSQL Server and application also tested on the MSSQL Server 2000, It is working as per need apart from date issue. Sybase doesn't need to store time with date, So I am looking similar datatype which doesn't need to store timestamp in date columns.

Is the any way to resolve this issue at database level.

Thanks

R.Mall|||Hi,

SQL Server do not support Time data type as such...So to store a time only data u will have to store the integer portion of datetime datatype as 0 ...(i.e January 1, 1900)...

But then if ur application does not allow u to use datetime datatype...may be try with CHAR......|||How I can store only integer part in datetime colmns.

Thanks|||How I can store only integer part in datetime colmns.make sure when you save a datetime value that you do not provide a time value as well

so INSERT ... VALUES ... ( '2004-10-27' ... ) is okay, and the time portion of the value is set to 00:00:00

however, INSERT ... VALUES ... ( getdate() ... ) includes a time

if you want to strip the time, use

... cast(convert(char(10),getdate(),120) as datetime)

DateTime formats in sql server 2005 and 2000

Hi there

I have an application running in two development environments, one using a sql server 2005 database and the other using a 2000 database. The application works on the 2000 database but when i try to insert values into the 2005 database the date format is incorrect (mm/dd/yyyy). I've checked the regional data settings on both machines and they are identical. The application (which i inherited) uses inline sql and when i dump the values before the sql command is run i get dd/mm/yyyy for the app running 2005 and mm/dd/yyyy for the app on 2000. I'm trying to determine if this is an issue with the machine itself and the .net framework installed or infact the two different versions on sql server.

thanks

hi jrogoz,

try inserting in the following format: yyyymmdd, it works no matter the local settings.

another option: try using DateTime object in the .net code and DATETIME datatype in SQLServer

hope this helps

|||

thanks for the reply

Could this be a machine setting somewhere? There are several instances where the inline sql contains this issue and i'd prefer to not have to change the code to get it to work just on this one machine...the way they have their production box setup it works fine as well.

thanks

|||

the format is language dependent

Try these with the user id you used to login to 2000 and 2005

select @.@.langid, @.@.language

select dateformat from master..syslanguages where langid = @.@.langid

Best option is to use the datatime object or specify the date in universal format YYYYMMDD

|||i did some more testing yesterday on the laptop in question and it seems that the issue is with the data and time setting of the laptop itself and not sql server. I simply wrote DateTime.now into a label and it displayed the date in the format dd/mm/yyyy where my machine displayed mm/dd/yyyy. I have matched up the regional settings as well and changed some registry settings to match what is on my computer and still no change. any ideas?|||

Hi,

I assume that you're concatenating strings to form a SQL command. So, the date/time info will be appended as the regional setting of the machine.

In this case, you can try to use parameters instead. Just assign date/time value as a DateTime object, and it is locale independent.

DateTime

I have an application that is sending the date with the following format dd/mm/yyyy the field in the table in sql is a datetime and the record is not being inserted. If I manually change the value in the query analyzer to mm/dd/yyyy the record is inserte
d successfully. How can I change the data type to accept the values as is
Thanks
Have you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks
|||does the set language command do that at the server level or database level or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:

> Have you check what the dateformat for the language you are using. You can
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> dd/mm/yyyy the field in the table in sql is a datetime and the record is not
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
>
>
|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?[vbcol=seagreen]
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
can[vbcol=seagreen]
it
> ----
--
> ----
--[vbcol=seagreen]
format[vbcol=seagreen]
not[vbcol=seagreen]
data[vbcol=seagreen]
|||Well, this is a vendor product. you're right, I don't want to change the language The tables are already created and i can't make any changes to the front end. It is a european vendor. The system came with an MSDE database. I am trying to switch it to
an enterprise version of sql 2000. When I made the switch, data wasn't being written to the main table. After running a trace, I noticed that the insert to that table was failing because the datetime that was being inserted was in European format and t
he datetime field was rejecting that. Changing the data type to varchar allowed the insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change to the Datetime field (like some formula or something), any other sugge
stions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:

> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry for
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> level or table level or field level?
> can
> it
> --
> --
> format
> not
> data
>
>
|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language The tables are
already created and i can't make any changes to the front end. It is a european vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise version of sql 2000. When I
made the switch, data wasn't being written to the main table. After running a trace, I noticed that
the insert to that table was failing because the datetime that was being inserted was in European
format and the datetime field was rejecting that. Changing the data type to varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change
to the Datetime field (like some formula or something), any other suggestions? I am not sure where
to use set dateformat[vbcol=seagreen]
> thanks
>
> "Gregory A. Larsen" wrote:
|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
http://www.aspfaq.com/
(Reverse address to reply.)

DateTime

I have an application that is sending the date with the following format dd/mm/yyyy the field in the table in sql is a datetime and the record is not being inserted. If I manually change the value in the query analyzer to mm/dd/yyyy the record is inserted successfully. How can I change the data type to accept the values as is
ThanksHave you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks|||does the set language command do that at the server level or database level or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:
> Have you check what the dateformat for the language you are using. You can
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > I have an application that is sending the date with the following format
> dd/mm/yyyy the field in the table in sql is a datetime and the record is not
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
> >
> > Thanks
>
>|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
> > Have you check what the dateformat for the language you are using. You
can
> > do this by issuing the following command:
> >
> > sp_helplanguage @.@.language
> >
> > If you want a different format then your langauge format you can change
it
> > by using the SET DATEFORMAT.
> >
> > Here is an example of where I used two different language. In these
> > examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> > Hopefully this will give you some ideas on how to fix you problem.
> >
> > set language us_english
> > exec sp_helplanguage @.@.language
> > create table x(d datetime)
> > insert into x values ('30/06/2002')
> > insert into x values ('09/30/2002')
> > select * from x
> > drop table x
> >
> > set language British
> > exec sp_helplanguage @.@.language
> > create table x(d datetime)
> > insert into x values ('30/06/2002')
> > insert into x values ('09/30/2002')
> > select * from x
> > drop table x
> > --
> >
> ----
--
> ----
--
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > > I have an application that is sending the date with the following
format
> > dd/mm/yyyy the field in the table in sql is a datetime and the record is
not
> > being inserted. If I manually change the value in the query analyzer to
> > mm/dd/yyyy the record is inserted successfully. How can I change the
data
> > type to accept the values as is
> > >
> > > Thanks
> >
> >
> >|||Well, this is a vendor product. you're right, I don't want to change the language The tables are already created and i can't make any changes to the front end. It is a european vendor. The system came with an MSDE database. I am trying to switch it to an enterprise version of sql 2000. When I made the switch, data wasn't being written to the main table. After running a trace, I noticed that the insert to that table was failing because the datetime that was being inserted was in European format and the datetime field was rejecting that. Changing the data type to varchar allowed the insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change to the Datetime field (like some formula or something), any other suggestions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:
> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry for
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> > does the set language command do that at the server level or database
> level or table level or field level?
> > can I change the datetime field in my db table to accept european time?
> >
> > "Gregory A. Larsen" wrote:
> >
> > > Have you check what the dateformat for the language you are using. You
> can
> > > do this by issuing the following command:
> > >
> > > sp_helplanguage @.@.language
> > >
> > > If you want a different format then your langauge format you can change
> it
> > > by using the SET DATEFORMAT.
> > >
> > > Here is an example of where I used two different language. In these
> > > examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> > > Hopefully this will give you some ideas on how to fix you problem.
> > >
> > > set language us_english
> > > exec sp_helplanguage @.@.language
> > > create table x(d datetime)
> > > insert into x values ('30/06/2002')
> > > insert into x values ('09/30/2002')
> > > select * from x
> > > drop table x
> > >
> > > set language British
> > > exec sp_helplanguage @.@.language
> > > create table x(d datetime)
> > > insert into x values ('30/06/2002')
> > > insert into x values ('09/30/2002')
> > > select * from x
> > > drop table x
> > > --
> > >
> >
> > ----
> --
> >
> > ----
> --
> > > --
> > >
> > > Need SQL Server Examples check out my website at
> > > http://www.geocities.com/sqlserverexamples
> > > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > > news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > > > I have an application that is sending the date with the following
> format
> > > dd/mm/yyyy the field in the table in sql is a datetime and the record is
> not
> > > being inserted. If I manually change the value in the query analyzer to
> > > mm/dd/yyyy the record is inserted successfully. How can I change the
> data
> > > type to accept the values as is
> > > >
> > > > Thanks
> > >
> > >
> > >
>
>|||http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language The tables are
already created and i can't make any changes to the front end. It is a european vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise version of sql 2000. When I
made the switch, data wasn't being written to the main table. After running a trace, I noticed that
the insert to that table was failing because the datetime that was being inserted was in European
format and the datetime field was rejecting that. Changing the data type to varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change
to the Datetime field (like some formula or something), any other suggestions? I am not sure where
to use set dateformat
> thanks
>
> "Gregory A. Larsen" wrote:
> > I'm not sure what you are trying to do, but I don't think you want to mess
> > with your language setting. The "set language" command is only in affect
> > for the session. What I really think you need is to use the "set
> > dateformat" statement to control the format of your input data. Sorry for
> > the confusion. Something like this:
> >
> >
> > create table x(d datetime)
> > set dateformat dmy
> > insert into x values ('30/06/2002')
> > set dateformat mdy
> > insert into x values ('09/30/2002')
> > select * from x
> > drop table x
> >
> >
> > --
> >
> > ----
> > ----
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> > > does the set language command do that at the server level or database
> > level or table level or field level?
> > > can I change the datetime field in my db table to accept european time?
> > >
> > > "Gregory A. Larsen" wrote:
> > >
> > > > Have you check what the dateformat for the language you are using. You
> > can
> > > > do this by issuing the following command:
> > > >
> > > > sp_helplanguage @.@.language
> > > >
> > > > If you want a different format then your langauge format you can change
> > it
> > > > by using the SET DATEFORMAT.
> > > >
> > > > Here is an example of where I used two different language. In these
> > > > examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> > > > Hopefully this will give you some ideas on how to fix you problem.
> > > >
> > > > set language us_english
> > > > exec sp_helplanguage @.@.language
> > > > create table x(d datetime)
> > > > insert into x values ('30/06/2002')
> > > > insert into x values ('09/30/2002')
> > > > select * from x
> > > > drop table x
> > > >
> > > > set language British
> > > > exec sp_helplanguage @.@.language
> > > > create table x(d datetime)
> > > > insert into x values ('30/06/2002')
> > > > insert into x values ('09/30/2002')
> > > > select * from x
> > > > drop table x
> > > > --
> > > >
> > >
> > > ----
> > --
> > >
> > > ----
> > --
> > > > --
> > > >
> > > > Need SQL Server Examples check out my website at
> > > > http://www.geocities.com/sqlserverexamples
> > > > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > > > news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > > > > I have an application that is sending the date with the following
> > format
> > > > dd/mm/yyyy the field in the table in sql is a datetime and the record is
> > not
> > > > being inserted. If I manually change the value in the query analyzer to
> > > > mm/dd/yyyy the record is inserted successfully. How can I change the
> > data
> > > > type to accept the values as is
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
--
http://www.aspfaq.com/
(Reverse address to reply.)

DateTime

I have an application that is sending the date with the following format dd/
mm/yyyy the field in the table in sql is a datetime and the record is not be
ing inserted. If I manually change the value in the query analyzer to mm/dd
/yyyy the record is inserte
d successfully. How can I change the data type to accept the values as is
ThanksHave you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks|||does the set language command do that at the server level or database level
or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:

> Have you check what the dateformat for the language you are using. You ca
n
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> dd/mm/yyyy the field in the table in sql is a datetime and the record is n
ot
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
>
>|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
>
can[vbcol=seagreen]
it[vbcol=seagreen]
> ----
--
> ----
--[vbcol=seagreen]
format[vbcol=seagreen]
not[vbcol=seagreen]
data[vbcol=seagreen]|||Well, this is a vendor product. you're right, I don't want to change the la
nguage The tables are already created and i can't make any changes to the fr
ont end. It is a european vendor. The system came with an MSDE database.
I am trying to switch it to
an enterprise version of sql 2000. When I made the switch, data wasn't bein
g written to the main table. After running a trace, I noticed that the inse
rt to that table was failing because the datetime that was being inserted wa
s in European format and t
he datetime field was rejecting that. Changing the data type to varchar all
owed the insert but I don't want to keep it as varchar obviously. Can this
issue be fixed with some change to the Datetime field (like some formula or
something), any other sugge
stions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:

> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry fo
r
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> level or table level or field level?
> can
> it
> --
> --
> format
> not
> data
>
>|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language
The tables are
already created and i can't make any changes to the front end. It is a euro
pean vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise versi
on of sql 2000. When I
made the switch, data wasn't being written to the main table. After running
a trace, I noticed that
the insert to that table was failing because the datetime that was being ins
erted was in European
format and the datetime field was rejecting that. Changing the data type to
varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be
fixed with some change
to the Datetime field (like some formula or something), any other suggestion
s? I am not sure where
to use set dateformat[vbcol=seagreen]
> thanks
>
> "Gregory A. Larsen" wrote:
>|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
http://www.aspfaq.com/
(Reverse address to reply.)

Dates overlow

Dear All,
When I enter a date with the year before 1950, it is rejected with an
"overflow" message.
I'm building an application that uses dates that ranges from 1200 till 2004.
So, does any body know how to solve this problem?
Regards,
Mohamed El Wakil
Teaching Assistant
Information Systems Department,
Faculty of Computers and Information,
Cairo University - Cairo
http://mohamedelwakil.tripod.com
Please, reply to mohamed.elwakil@.omeldonia.com
SQL Server accepts date from 1753 to 9999 for the datetime datatype. If you need to go out of this
span, you need to use some other representation. A string representation is one option. A number of
integer columns (one for each element) is another. One integer column counting seconds from a
certain reference date is yet another option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till 2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||The exact "overflow" error would be helpful. Does it come from SQL Server
or from whatever application you are using to enter the date?
Are you using two digits for the year? I recommend that you use more...
There are easier ways to enter dates than typing them all in...a WHILE loop
might be easier!
You may have some problems entering dates earlier than 1753.
From Books Online:
datetime and smalldatetime
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.
Keith
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||Date outside the range 1753-9999 is not accepted at datetime type. Look for
datetime and smalldatetime in BOL for more detail.
You can come up with your own definition, not as datetime, but such as a
char column, to accommodate your needs.
Quentin
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>

dates on Page headers

Dates in database are

Ist july 2006
2 nd july 2006
1 st sep 2006

Group by state1

I need to get count of Application nos for Group by state1 and date should be in header for a week. we have to pass date range values from date_range parameter

How do get it. The format is given below:

Sat Sunday Monday .. Friday
Statename 1/7/2006 2/7/2006 3/7/2006 .. 7/7/2006
State1 2 5 7 6



If I do group by date, then dates will not appear in a horizontal manner, Please help me how to proceed furtherTry the cross-tab format for the report.

Rashmi|||cross tab shoul not be used as per the clients requirement

Dates Error

Hi:

I got the next problem, when I try to modify a record of my SQL Server Database from my Delphi application the next message error appears

"Date is less than 01/12/2003"

The record that I'm trying to modify was inserted from the same applicaition.

I'm not so sure if it's a database problem, but I don't know why it is passing. What can I do?

Thaks for your help!!

Cristopher SerratoNope,

Probably someone wrote a trigger to check to rows modified date. Someone probably updated the row since you got it last..

Your update has to supply a date if I'm not mistaken...

They basically want you to requery the data so you can work with th most current version of data...

Just a guess...