DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

DB Access to more than one DB on the Same Server

I am trying to grant access to more than one database on the same SQL serve
r.
I have created two databases (Helpdesk, Helpdesk_2) on SQL Server Server1.
I have given User1 acces to both databases. I have made the DB "HELPDESK"
User1s default DB. When User1 tries to create an ODBC connection to DB "HELP
DESK_2"
he is not able to se the database(HELPDESK_2) in the list.
In Sybase when this happens, I just change the default database to "MASTER"
and this
clears up the problem. I have tried this in SQL Server and the problem still
persists.
What is it that I am overlooking.
Configuration: SQL Server Enterprise SP3 Processor License.
MS Windows 2K SP3If he run the statement "select * from master..sysdatabases" does it return
the full list?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Yes, all the databases appear when they run a select on sysdatabases.
-- VFLORES wrote: --
I am trying to grant access to more than one database on the same SQL serve
r.
I have created two databases (Helpdesk, Helpdesk_2) on SQL Server Server1.
I have given User1 acces to both databases. I have made the DB "HELPDESK"
User1s default DB. When User1 tries to create an ODBC connection to DB "HELP
DESK_2"
he is not able to se the database(HELPDESK_2) in the list.
In Sybase when this happens, I just change the default database to "MASTER"
and this
clears up the problem. I have tried this in SQL Server and the problem still
persists.
What is it that I am overlooking.
Configuration: SQL Server Enterprise SP3 Processor License.
MS Windows 2K SP3|||I would try running SQL profiler to trace the problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

DB access revoked...

I have two servers (A & B) with 7 DBs and have created a
user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the
user on server B, loses all its access to these 7 DBs from
a server level, though I can see that this user exists
from a DB perspective.
Any ideas why and how this can be corrected? Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_change_users_login in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rob" <rhchin@.hotmail.com> wrote in message =news:0dfc01c3812e$29ce71e0$a001280a@.phx.gbl...
I have two servers (A & B) with 7 DBs and have created a user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the user on server B, loses all its access to these 7 DBs from a server level, though I can see that this user exists from a DB perspective.
Any ideas why and how this can be corrected? Thanks.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_change_users_login in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rob" wrote in message news:0dfc01c3812e$29=ce71e0$a001280a@.phx.gbl...I have two servers (A & B) with 7 DBs and have created a user on =each server with DB_OWNER role.When I restore a DB backup from one =server A to B, the user on server B, loses all its access to these 7 DBs from =a server level, though I can see that this user exists from a DB perspective.Any ideas why and how this can be corrected? Thanks.

---

--=_NextPart_000_0094_01C3810D.6E860260--

Day Of The Week

There is probably a better way to do this...but...
I am wanting to run a procedure which will backup a database. I created 7
devices, one for each day of the week.
When I run my procedure, I was wanting it to figure out the day of the week
so it would know which "device" to use for a backup.
Is there a SQL command which will allow me to determine the day of the week
rather then having to pass a parm which provides the day of the week?
Thanks in advance for your assistance...Select datename(dw,getdate())
--
HTH
Satish Balusa
Corillian Corp.
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns94A07D007A203JHeaveyBDUP@.207.46.248.16...
> There is probably a better way to do this...but...
> I am wanting to run a procedure which will backup a database. I created 7
> devices, one for each day of the week.
> When I run my procedure, I was wanting it to figure out the day of the
week
> so it would know which "device" to use for a backup.
> Is there a SQL command which will allow me to determine the day of the
week
> rather then having to pass a parm which provides the day of the week?
> Thanks in advance for your assistance...|||select datepart(weekday,getdate())
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns94A07D007A203JHeaveyBDUP@.207.46.248.16...
> There is probably a better way to do this...but...
> I am wanting to run a procedure which will backup a database. I created 7
> devices, one for each day of the week.
> When I run my procedure, I was wanting it to figure out the day of the
week
> so it would know which "device" to use for a backup.
> Is there a SQL command which will allow me to determine the day of the
week
> rather then having to pass a parm which provides the day of the week?
> Thanks in advance for your assistance...

Day Of The Week

There is probably a better way to do this...but...
I am wanting to run a procedure which will backup a database. I created 7
devices, one for each day of the week.
When I run my procedure, I was wanting it to figure out the day of the week
so it would know which "device" to use for a backup.
Is there a SQL command which will allow me to determine the day of the week
rather then having to pass a parm which provides the day of the week?
Thanks in advance for your assistance...Select datename(dw,getdate())
HTH
Satish Balusa
Corillian Corp.
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns94A07D007A203JHeaveyBDUP@.207.46.248.16...
> There is probably a better way to do this...but...
> I am wanting to run a procedure which will backup a database. I created 7
> devices, one for each day of the week.
> When I run my procedure, I was wanting it to figure out the day of the
week
> so it would know which "device" to use for a backup.
> Is there a SQL command which will allow me to determine the day of the
week
> rather then having to pass a parm which provides the day of the week?
> Thanks in advance for your assistance...|||select datepart(weekday,getdate())
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns94A07D007A203JHeaveyBDUP@.207.46.248.16...
> There is probably a better way to do this...but...
> I am wanting to run a procedure which will backup a database. I created 7
> devices, one for each day of the week.
> When I run my procedure, I was wanting it to figure out the day of the
week
> so it would know which "device" to use for a backup.
> Is there a SQL command which will allow me to determine the day of the
week
> rather then having to pass a parm which provides the day of the week?
> Thanks in advance for your assistance...

DateTime.Now expression expected problem

Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column.

Using the default insert created, I have the following code:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Profile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now)

The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Dim pd as partyDetails.partyDetailsDataTable
pd = da.GetPartyDetailsByID(Profile.partyid)
da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid)

Have I an error in my Insert section?

Thanks for any help,

Mark

Look at what the functions da.Insert and ds.Update are expecting as their arguments.

Maybe daInsert is expecting the date as a string instead of a DateTime object

DateTime Now Problem

Hey, basically i have a database of users and on a certain page load i want to register when they logged onto that page, so i created this snippet of code :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

but that give me the error :

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.

So I changed it to :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

as when I looked at previous times in my database there format was "27/08/2007 15:25:45" for example. but that gives me the exact same error can anyone tell me why this doesnt work? / how to get it to work? My datatype for this feild is "datetime". Thanks John

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated= '" + DateTime.Now.ToString() + "' WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

OR , best way is

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

|||

Awesome this :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

way did work the best, for anyone else who is having this problem, thanks loads! John

DateTime Format problem

In SQL query I have to find records which occour between two dates. I created Select query with two parameters @.date1 and @.date2 in clasue WHERE. But problem is with date format of my parameters. This format is to long. I dont wont to use time part of these parameters only date part is needed. When I put two identical dates my query doesn't find any data because both dates are eg. 2007-05-22 00:00:00. But I need data for all this day. How to correct this problem? Regards Pawel.

Use the Convert Function to convert it to a small date it will trim the time part

Where Convert(Varchar(10),@.Date1) = Convert(Varchar(10),@.Date2) ... Also you can use the third parameter in the Convert Function to get a specific format of dates i.e dd/mm/yyyy or yyyy/mm/dd etc. For a complete list

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Check the link

|||

If the goal is to retrieve data for a single day, the method I prefer is lower inclusion, upper exclusion. Let me explain:

declare @.dtdatetime, @.startDatedatetime, @.endDatedatetime-- assume this is the dateset @.dt ='2007-01-02 12:34:56'select-- if only a date portion is passed into the sproc -- you won't need to remove the time portion @.startDate =convert(char(10), @.dt, 120) , @.endDate =dateadd(day, 1, @.startDate)select a.*-- use column list here!from tbl awhere-- inclusive of the lower limit a.DateColumn >= @.startDate-- exclusive of the upper limitand a.DateColumn < @.endDate

datetime fields and constarints

I've created a user defined datatype called t_date. It's a datetime datatype. I would like to bind a rule to the udt that constrains the time to 12:00am. Can anyone recommend the best way to do this.What about this?

create rule [myrule] as @.date=convert(varchar(10),@.date,101)+' 12:00'
GO|||Yep that works, in fact that's what I've been doing. I was hoping there was a more efficient way to check instead of having to cast to a string and back to a date again. It's not in your example but I cast back to a datetime to do the compare. Not sure if I have to or not.

Do you know how costly casting data in SQL server is?

Thanks for the help :-)|||It doesn't matter how costly it is, 'cause its the only way! You wants it, you pays for it!

blindman|||Thanx blindman, damn good thing my company has deep pockets because weez gonna have to pay|||Pay for processors...pay for memory...make sure they pay you good too!

Seriously, casting/converting datetime and string values is probably not a fast process, but for the string lengths you are dealing with it should not have a big impact on performance. And there is no way around it. MS Access, MS Excel, and other Microsquash products allow you to take the integer portion of the datetime value (a vast operation), but SQL Server uses a different logic for storing datetime values and this method won't work.

blindman

DATETIME Default getDate() query

Hi,
I have created a datagrid within an ASP.NET page that links to an SQL
table - this works just fine. One of the columns retrieves the date which a
particular row was populated. However, the format
which the date is returned is:
19.12.2004 13:03:25
I only want to display the date that the row was populated, not the exact
time as well. When I created the table I used the following code (I've cut
out the rest of the fields):
CREATE TABLE Users
(
Paul,
You can use this as your default:
Default dateadd(d,0,datediff(d,0,getdate()))
It works by finding the number of day boundaries between day 0 (January
1, 1900) and now, and adds that many days back to day 0.
Steve Kass
Drew University
Paul Evans wrote:

>Hi,
>I have created a datagrid within an ASP.NET page that links to an SQL
>table - this works just fine. One of the columns retrieves the date which a
>particular row was populated. However, the format
>which the date is returned is:
>19.12.2004 13:03:25
>I only want to display the date that the row was populated, not the exact
>time as well. When I created the table I used the following code (I've cut
>out the rest of the fields):
>CREATE TABLE Users
>(
> .
> .
> .
> .
> u_entrydate DATETIME Default getDate()
>)
>
>Is this to do with the DATETIME Default getDate() code for creating the
>column? Is there something else I could use to get just the date only, with
>out the time of day the row was populated?
>I'm using a microsoft SQL server.
>Thanks for your time
>Paul Evans
>P.S. or is it a problem with my ASP.NET code?
>
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>> Using SQL2005 DTS - I am trying to import data from a CSV file into a
>> table created with the following
>> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
>> [Date-Time] datetime,
>> [Server] text,
>> [Drive] text,
>> [Drive Size] numeric(29,0),
>> [Space Free] numeric(29,0)
>> )
>> the first field is date and time and looks like this >>
>> 09/20/2006 06:30:03 PM
>> But no matter what I try to use for a final field format the result of
>> that data after it's imported displays the same time for every record >>
>> 12:00:00 AM <<. The date comes through fine, but it just does not seem
>> to recognize the time. What do I need to do to get the time to be
>> imported correctly ?
>

DateTime and Date problem

I have created a cube with two dimensions in as2005. A time dimension and a "data" dimension. The "data" dimension is a table from a db that has a field called "ItemDate" that is of type DateTime. In the Dimension usage panel of the cube designer I create a reference in the time row to the "ItemDate" in the data dimension. The problem arises when I want to count how many "data" rows have a date of "March 27 2002". It will only count the rows with a datetime that is "3/27/2002 12:00:00 AM". All the rows that are "3/27/2002 1:45:01 PM" or anything else get ignored. Is there a way to solve this problem in analysis server 2005. Can I convert the datetime to a date or change the reference in some way.
note: I have simplified the problem and striped away names and dimensions to make it less confusing.

Assuming that the "data" dimension source is SQL Server, you can add a Named Calculation to the Data Source View, like "ItemDateOnly", defined as:

convert(datetime, convert(varchar, ItemDate, 101))

This new column could be used to join to a normal date dimension.|||Thanks, I was trying to create a calculated field in the cube with MDX. The calculated field in the data source view opens up many solutions.

DateTime

Hello All

Here is the Question

I have created a table in order to time my query in parts:

For example

/*********************************************************************************/

USE [PUBS]

GO

CREATE TABLE [dbo].[IntegrationTestTime]

(

[Process] [varchar] (30),

[StartTime] datetime,

[EndTime] datetime,

[RunTime] datetime,

[DataRetrieveTime] datetime,

) ON [PRIMARY]

GO

INSERT INTO IntegrationTestTime (ProcessName) VALUES ('Change1');

INSERT INTO IntegrationTestTime (ProcessName) VALUES ('Change2')

/*********************************************************************************/

During the start of the query I am updating the StartTime Col for Change1 with Getdate()

UPDATE IntegrationTestTime SET StartTime = GETDATE() where Process = 'Change1'

At the End of the query i am updating EndTime Col for Change1 with Getdate()

UPDATE IntegrationTestTime SET EndTime = GETDATE() where Process = 'Change1'

Q1) I want to update RunTime Col with the time difference upto milli second from StartTime to EndTime col.

i.e, Runtime Col must have the exact time it took to execute the whole query

UPDATE IntegrationTestTime SET RunTime = (SELECT datediff(MS, StartTime, EndTime)

where Process = 'Change1')

But this gives me an error, i mean wrong value is being updated

Q2) In middle of the query, after executing certain SQL commands, I want to time it again and set [DataRetrieveTime] Colunm with the difference of StartTime and Getdate time at that instance.

Any suggestion please?

I would use a computed field, try this DDL for your table.

Code Snippet

CREATE TABLE [dbo].[IntegrationTestTime](

[Process] [nvarchar](50) NULL,

[StartTime] [datetime] NULL,

[EndTime] [datetime] NULL,

[RunTime] AS (datediff(millisecond,[StartTime],[EndTime])) PERSISTED,

[DataRetrieveTime] datetime

) ON [PRIMARY]

GO

|||

Well lets see.

Check the data types of the columns and the values you are trying to store in them. You have defined RunTime as datetime and you are trying to store the milli seconds in them. To store the milli seconds you don't need a datetime column.

|||

I am want to store the time difference between the two time (Start and End Time). Its hould be upto an accuracy of milli second

Suppose Start tiem is 4:55:56.981

and ENd time is 5: 01:57.991

so the RunTime = 00: 06 : 01: 010

This is what i need

|||You need to understand how datetime works. Datetime uses 8 bytes to store the datetime and it uses first 4 bytes to store the date and next four to store the time. You can't separate the values. Your code is trying to store the milli seconds in a datetime field which tries to convert implicitly to a date and time value. If you want to store only the difference of time between columns, you need a int data type (only 4 bytes) not a datetime datatype. Also don't try to store the formats of data in the database. let app layer handle the formatting of data.

|||

A could of things. 1. SQL Server will only give you .003 second accuracy in GetDate(). 2. Shawn's answer DOES store the difference, with no trigger or follow on code to muck with:

CREATE TABLE [dbo].[IntegrationTestTime](
[Process] [nvarchar](50) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[RunTimeMs] AS (datediff(millisecond,[StartTime],[EndTime])) PERSISTED,
) ON [PRIMARY]

GO

INSERT INTO IntegrationTestTime (Process) VALUES ('Change1');
INSERT INTO IntegrationTestTime (Process) VALUES ('Change2')

GO
UPDATE IntegrationTestTime SET StartTime = GETDATE() where Process = 'Change1'

UPDATE IntegrationTestTime SET EndTime = GETDATE() where Process = 'Change1'

select *
from IntegrationTestTime

Process StartTime EndTime RunTimeMs

-- -- -- --
Change1 2007-09-06 14:03:37.777 2007-09-06 14:04:06.157 28380
Change2 NULL NULL NULL

Your update statement:

UPDATE IntegrationTestTime SET RunTime = (SELECT datediff(MS, StartTime, EndTime)

WHERE Process = 'Change1')

Won't work because you are trying to store it to a datetime value. In my example, (after changing RunTime to be an integer) the value returns: 1925-08-21 00:00:00.000. So the problem is likely that the date being returned is out of the range for your tool?

|||

I think i have a wrong code

Can any body suggest me whats the right code / procedure to get the difference between the Start and end.

I have started at so and so time

and ended at so and so time

whats the time taken to do taht job?

|||

This is the right code: datediff(MS, StartTime, EndTime). It is just that it is not a date value. It is a number of milliseconds difference. You can turn this into 00:00:00.000 easily enough, just by doing the math to peel off minutes, seconds, etc. Here is a blog I wrote on that subject: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1238.entry

You could store that formatted output into your table, likely as a varchar value.

Dates when inserting a record

Is it possible to have sql server automatically record date and time (in a designated field)when a record is created in the db? This may seam basic but it has caused me a lot of grief.You can use database trigger for automatic firing.|||yes it can
just use when creating the table for that field a default

something like this:

create table xxx (
id int not null,
dateadded datetime not null default(getdate())
)|||so if I used get date () in the field and queried the table. Will it show me the date and time as of that momnet or will it have embedded the date and time the record was added...|||getdate() is a function that returns the current date when the record is actually added. it's not a column name. so if u want to query the date when the record was added, u need to query that column.|||getdate() is not working. It works in the sense that it displays system date and time. The problem is that it updates every column whenever I view the table or query it...

I need an option which will ensure that each record added inserts into the designated Table/Field the actual system time at which the record was inserted.|||Foefie's solution should work for you. Specifying the Default value of a column to be GETDATE() will insert the SQL Server's current system date/time into the column when the record is inserted into the database. It willl not fluctuate or vary with queries -- it is concretely written into the record. We use this approach all of the time without incident.

Terri|||If you use GETDATE() as default value in the column, you can control what happens.

What I mean by that is, if you want the date to reflect the datetime of last change, then you can include the field in any update, but specify DEFAULT as the value to be inserted. This will then cause the date to be update.

Alternatively (if you don't want it to change on every update, and always reflect the original datetime when the record was created), you should omit the field from any update statements and it will remain as originally inserted (you don't need to specify the field on any insert statements either, since on the first insert any fields for which you don't explicitly specify a value should get the default)

HTH

Anton|||Thank you. This explains it much better. The confusion has been due to the fact that while using Enterprise Manager, rightclicking on the table in question and selecting return all rows, I have noticed that it always updates the date and time. I just assumed that the same would apply anytime I queried the data in the table.

Thanks!

Dates problem in SQL Server Evrywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Replied in another thread (same question)|||Replied in another thread. (for same question)

Dates problem in SQL Server Everywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

Please use quote for inserting datetime value, it will solve the problem.

use like

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,'4/7/2006',DEFAULT)

Thanks

Sachin

|||

Thank you,

The information was very good,

the problem with the dates is now solved.

Toni.

Dates problem in SQL Server Everywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Repliedin another thread (same question)

Dates incorrectly being saved incorrectly. DD and MM swapped round ?

For some reason a stored procedure which I have created is incorrectly
saving the date to the table. It seems the day and month are being
swapped around e.g. a date which should be the 12th April (12/04/2005)
is saving as the 4th December (04/12/2005).

The parameter used in the stored procedure comes from a VB6 app, I
amended this so the format was "yyyymmdd hh:mm:ss". The full line in VB
being,

Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))

When I run my VB app it works fine, the syntax in the stored procedure
is,

CREATE PROCEDURE dbo.spUpdValues

@.data_id int,
@.date_of_call datetime

as

update data
SET date_of_call = CONVERT(char, @.date_of_call, 101)
where data_id=@.data_id

Is it because the convert format is using an american date format ? I
can't see why as I can't reproduce this error using my own PC as the
date saves correctly, I can also confirm it's not happening to everybody
who uses the app. If it is happening for specifc users then what could
be the cause. I've checked Regional Settings and all seems fine there.

Any ideas on what could be doing this as I'm struggling to investigate
any further.

To debug I ran the stored procedure direct, manually inputting the
variable - again no problem. Also, the following SQL statment shows no
problem...

declare @.date_of_call datetime
set @.date_of_call = '20041101 08:30:00'

select CONVERT(char, @.date_of_call, 101)
select CONVERT(char, @.date_of_call, 106)

----------
11/01/2004

(1 row(s) affected)

----------
01 Nov 2004

(1 row(s) affected)

Any help would be much appreciated.

*** Sent via Developersdex http://www.developersdex.com ***MSSQL stores dates internally in a binary format - the display format
in any client (including Query Analyzer etc.) is defined by the client,
not the server. See this article:

http://www.karaszi.com/sqlserver/info_datetime.asp

The best way to format dates is usually to do it in the client
application, because it has access to the client's regional settings.

Simon|||On Tue, 26 Apr 2005 10:36:00 GMT, Robert Zirpolo wrote:

(snip)
>The parameter used in the stored procedure comes from a VB6 app, I
>amended this so the format was "yyyymmdd hh:mm:ss".

Hi Robert,

This format is not one of the guaranteed "safe" formats. I must admit that
I have not yet found any positive evidence that this format IS interpreted
wrong, but since it's not guaranteed, it MIGHT be interpreted wrong.

These formats are safe:

* yyyymmdd - for date only (note: no dashes, slashes, dots, or other
interpuction)

* yyyy-mm-ddThh:mm:ss - for data and time (note: dashes are required
between the parts of the date; colons between the parts of the time and an
uppercase T seperates the date from the time part)

* yyyy-mm-ddThh:mm:ss.ttt - same as above, but including milliseconds

Try using one of these formats and see if that solves your problem.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Robert Zirpolo (robert.zirpolo@.moorestephens.com) writes:
> For some reason a stored procedure which I have created is incorrectly
> saving the date to the table. It seems the day and month are being
> swapped around e.g. a date which should be the 12th April (12/04/2005)
> is saving as the 4th December (04/12/2005).
> The parameter used in the stored procedure comes from a VB6 app, I
> amended this so the format was "yyyymmdd hh:mm:ss". The full line in VB
> being,
> Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
> Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))

That's indeed a safe format for datetime values. Nevertheless, you
should use adDBTimeStamp instead, so that binary values are passed
over the wire.

> CREATE PROCEDURE dbo.spUpdValues
> @.data_id int,
> @.date_of_call datetime
> as
> update data
> SET date_of_call = CONVERT(char, @.date_of_call, 101)
> where data_id=@.data_id

If data.date_of_call is datetime, there is no need to use convert at
all. Just take it away.

> Is it because the convert format is using an american date format ? I
> can't see why as I can't reproduce this error using my own PC as the
> date saves correctly, I can also confirm it's not happening to everybody
> who uses the app. If it is happening for specifc users then what could
> be the cause. I've checked Regional Settings and all seems fine there.

SQL Server does not go by regional settings, nor on the server, and
nor of the client. Instead SQL Server goes by dateformat and language
settings. Different users can have different default languages.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 26 Apr 2005 21:50:00 +0000 (UTC), Erland Sommarskog wrote:

(snip)
>> update data
>> SET date_of_call = CONVERT(char, @.date_of_call, 101)
>> where data_id=@.data_id
>If data.date_of_call is datetime, there is no need to use convert at
>all. Just take it away.

Hi Erland,

Ah, I missed that part (I guess I shouldn't stop reading when I think I
see the problem, eh?)

Good catch!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Apr 26 2005, 05:50 pm, Erland Sommarskog <esquel@.sommarskog.se> wrote in
news:Xns9644F22BB824Yazorman@.127.0.0.1:

>> Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
>> Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))
> That's indeed a safe format for datetime values. Nevertheless, you
> should use adDBTimeStamp instead, so that binary values are passed
> over the wire.

Just curious, why would you use adDBTimeStamp and not adDate for this?

--
remove a 9 to reply by email|||I have just re-visited this and am going to go with your suggestion of

"You should use adDBTimeStamp instead, so that binary values are passed
over the wire."

I think this could be the cause, it's still bl**dy strange why it is
happening so infrequently.

Hopefully this will eradicate the problem. Thanks to everybody in
regards to your posts.

*** Sent via Developersdex http://www.developersdex.com ***|||Robert Zirpolo (robert.zirpolo@.moorestephens.com) writes:
> I have just re-visited this and am going to go with your suggestion of
> "You should use adDBTimeStamp instead, so that binary values are passed
> over the wire."
> I think this could be the cause, it's still bl**dy strange why it is
> happening so infrequently.

First of all, you should take that convert thing out. That's your main
problem.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

There is only one format allowed for dates in Standard SQL,
"yyyy-mm-dd" and it is based on the ISO-8601 Standard. You should be
using only this and not any local dialect formats. Let the front end
worry about the display.|||Dimitri Furman (dfurman@.cloud99.net) writes:
> Just curious, why would you use adDBTimeStamp and not adDate for this?

Because adDBTimeStamp is the same binary representation as in SQL Server.
(Well, not really since adDBTimeStamp permits for nine-digit fractions
and SQL Server only three.)

adDate on the other hand is a floating point number, with 0 meaning
1899-12-30. Since the base date in SQL Server is 1900-01-01, this can
cause some confusion. This may be covered up behind the scenes, but
in any case that would only be extra conversions. Furthermore, I don't
thiak adDate is able to handle milliseconds.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Joe Celko (jcelko212@.earthlink.net) writes:
> There is only one format allowed for dates in Standard SQL,
> "yyyy-mm-dd" and it is based on the ISO-8601 Standard. You should be
> using only this and not any local dialect formats. Let the front end
> worry about the display.

Please Joe, you are in an SQL Server newsgroup now. Do not give outright
incorrect recommendations. Yes, YYYY-MM-DD may be standard SQL, but
this format may be misinterpreted:

set language us_english
select convert(datetime, '2005-04-12') -- Prints 2005-04-12
go
set language German
select convert(datetime, '2005-04-12') -- Prints 2005-12-04

You can use ISO 8601 safely in SQL Server, but then you need to go
all the way (save for the timezone): 2005-04-12T19:12:12. Without the
T, you can get into misery.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Dates and Parameters

I have created a report with parameters, I select the dates (01/05/2007) to
(01/07/07) using the calendar and then click view report the dates then reset
to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
are displayed in the report are returning the correct values. When you click
view report again without amending the dates the data is returned and the
parameters values as displayed in the report are incorrect. Please help.On Jul 6, 6:28 am, Changing Dates in reports & parameters <Changing
Dates in reports & paramet...@.discussions.microsoft.com> wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.
You most likely want to check/change your computer's/server's regional
settings. This can be done through the control panel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Checked regional settings on both the Server and my local pc and both are
British English.
thanks
lisa
"Changing Dates in reports & parameters" wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.