DB access works in debug but fails when hosted

I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.

When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.

But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails.

What could I doing wrong could I be missing a setting in IIS? Any ideas??

Here's my webconfig if that helps at all:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="studentprofilesConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<roleManager defaultProvider="AspNetWindowsTokenRoleProvider" />
<compilation debug="true" defaultLanguage="c#" /></system.web></configuration

There is a posibility that if SQL server is not configured to accept remote connection it may fail. To enable remote connection follow this steps

Under SQL2005 program menu follow configuration Tools =>SQL Server Surface Area Configuration => Select Server Surface Area Configuration for Services and Connections.

It will lead to a window with a treeview select Remote connections.Then Select Local and Remote Connections.

Now if the error is due to Remote configuration issue it will be resolved

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 Values in SQL Express ASPNETDB.MDF

greets again folks,

The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.

The date is usually correct but the time is always hours off.

Is there some way to get the time part of the DateTime to be correct?

Do I have to write code to set the time when the user logs in?

Thanks a mil!

It sounds as if GetUtcDate() is being called instead of GetDate. GetUTCDate records the UTC or GMT date time whereas GetDate() get the local date time.

|||

hypercode:

greets again folks,

The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.

The date is usually correct but the time is always hours off.

Is there some way to get the time part of the DateTime to be correct?

Do I have to write code to set the time when the user logs in?

Thanks a mil!

check database coumn type ... is it set to DateTime ....

|||

Kamrul,

Although unlikely, the column does not have to be a DateTime to have GetDate assigned to it. E.g.SELECTCONVERT(VARCHAR(20),GetDate(), 113) returned "19 Apr 2007 17:07:06". but could have inserted the valud into a CHAR(20) column.

|||

The date is OFF in the ASPNETDB itself.

Beoroe I write any code to retireve the values, they are already in the dBase off to begin with.

Is there some way to tell the dBase to record the correct times?

|||

Look in the table definition, do the collumns have the default property change the GetUtcDate() to GetDate(). Do the same in the stored procedures and all date/time from then will be in local rather than universal time.

Incidentally was the time an exact number of hours off from the server time?

|||

" Look in the table definition, do the collumns have the default property change the GetUtcDate() to GetDate(). Do the same in the stored procedures and all date/time from then will be in local rather than universal time. "

I just looked in the table definition for all of the columns which contain datetime date types. I don't see GetUtcDate or GetDate() anywhere in the table definition. Where should these values be displayed?

|||If the data is not being set by a default property, look in the stored procedures for them.|||

Hi Hypercode,

Actually,the datetime value is saved as UTC format in system or database. When there's a request from a user, the server will translate the time into local time which depends on the server's location and response the user's request. So pls be sure that the settings of the timezone on your server is correct ( or just as you want).

If the problem still exists, you have to translate the time manually.Here's the UDF that you might be interested in looking into

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712

Hope it helps.

Thanks

|||

Thanks to you guys for pitchin in!

I still didn't get her straightened out yet. Been busy with other stuff (on the same project). I'll be getiing this straightened out though when I get a chance.

Datetime Parameter Format

Hi,

I'm trying to test a stored procedure in VB and SQL Express 2005 - it has a smalldatetime field.

It works fine from the VB side using the data source Preview Data facility but when I try it in SQL Management Studio I get errors no matter what format I try!

I'm sure I'm missing something very simple - thanks in advance.

USE [Bookings]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[spAddReservation]
@.RES_TBL_ID = 1,
@.RES_TTL_ID = 1,
@.RES_Diner_Surname = N'Bloggs',
@.RES_Date = 28/05/2007 18:15:00,
@.RES_Meal_ID = 1,
@.RES_STA_ID = 1,
@.RES_OCC_ID = 1,
@.RES_STF_ID = 1

SELECT 'Return Value' = @.return_value

GO

The error this generates is
Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '/'.

I've tried with quotes (single or double) but then I get a convert error!
You need to include your date info quotes
You could set current datetime format by SET DATEFORMAT statement.
Also you could use CONVERT function:
@.Res_date = convert(datetime, '28/05/2007 18:15:00', 131)

|||

You won't get datetime conversion errors if you were to use the ISO standard date format = year/month/day,

e.g., '2007/05/28 18:15:00'

|||

See function CONVERT in BOL. If you use styles 112 (ISO) or 126 (ISO8601), then SQL Server will interprete correctly datetime constants no matter the settings for LANGUAGE and DATEFORMAT.

> @.RES_Date = 28/05/2007 18:15:00

@.RES_Date = '2007-05-28 18:15:00'

AMB

|||

I hope the error you got because of the missing quote..

When I check the dateformat it is DMY. (it may cause /throw another error if the system dateformat is different).

It is not bad idea to use the DATEFORMAT if the entier db uses the single format.

Try the following code..

Code Snippet

SET DATEFORMAT DMY;

USE [Bookings]

GO

DECLARE@.return_value int

EXEC@.return_value = [dbo].[spAddReservation]

@.RES_TBL_ID = 1,

@.RES_TTL_ID = 1,

@.RES_Diner_Surname = N'Bloggs',

@.RES_Date = '28/05/2007 18:15:00',

@.RES_Meal_ID = 1,

@.RES_STA_ID = 1,

@.RES_OCC_ID = 1,

@.RES_STF_ID = 1

SELECT'Return Value' = @.return_value

|||Thanks for this - worked a treat!

I had assumed the date format would be as per culture setting and was misled by the fact that that worked OK inside VB Express.

datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

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

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…

datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

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

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…

DateTime format

Hi,
I'm new to SQL Server (Express) and I wonder if there is a way that I can format a date's appearance in the database, that is, the format of the datetime column.

When I view a date in VWD Express, it's in my country's format (2006-11-24 for example) but when I try to insert a date using the same format using a web form, the inserted date in the database becomes 1905-06-something. This happens regardless of whether I'm inserting a string or if the string has been converted to a date via CDate.

So, is there a way I can set the database's date format? And why is it wrong anyway? It's bugging me as the original (Swedish) date is already in the ISO format that SQL Server seems to use (such as yyyy-mm-dd), and I'm using localhost with Windows set to Swedish, IE 6 set to Swedish, and even web.config's UICulture and Culture to Swedish as well.

Of course, I can rearrange the order of the date's numbers to get proper values in the db, but it seems as an unnecessary step and I can't figure out what format to use anyway.

All help is very welcome.

Pettrer

You can use a SQL session option 'DATEFORMAT' to set the date format for your connection to SQL, instead of rearranging the order of the date's numbers. Please check this post:

http://forums.asp.net/thread/1262753.aspx

|||

When inserting to a table with a datetime column the best way is always to use a parameterized query/sp and feed it a datetime value. Never do any string formatting in your code before insert.

Although it "should work" :) so therefore I ask you - remembered quotes around date? i.e

insert into blabla select 1, 2, '2006-06-12'

|||

Iori_Jay:

You can use a SQL session option 'DATEFORMAT' to set the date format for your connection to SQL, instead of rearranging the order of the date's numbers. Please check this post:

http://forums.asp.net/thread/1262753.aspx

Iori_Jay,

Thanks for the tip! It's really cool.

Pettrer

|||

aspcode.net:

When inserting to a table with a datetime column the best way is always to use a parameterized query/sp and feed it a datetime value. Never do any string formatting in your code before insert.

Although it "should work" :) so therefore I ask you - remembered quotes around date? i.e

insert into blabla select 1, 2, '2006-06-12'

Ehrm... I must admit I didn't know they were needed... (I've only used MS Access before.)

That did it! Thanks a bunch!

Pettrer

|||

Great it worked out. Done the same mistake myself :) Without quotes SQL Server first calculates the value, meaning 2006-06-12 becomes 1988 and that is interpreted as days added to sql server mindate or something.

Ex

select convert(datetime, 2006-06-12) --> 1905-06-12 same as
select convert(datetime, 1988) --> 1905-06-12

select convert(datetime, '2006-06-12') --> 2006-06-12

|||

Well, yeah, that nailed it! ;-)

Thanks

P

Dates in Reporting Services

hey all

set up

Visual Studio 2005

SQL Server Express / Reporting Services

four fields

State date Start time Finish Date Finish Time

I need to take one away from the other - can someone please help me?

Is it better to keep these in separate fields or to combine and subtract?

Is there anything special I need to know with subtracting time?

I am reasonably newbie still so would appreciate any help thanks

I am using the visual side in Reporting services - Data - Layout - Preview.

thanks

Jewel

Jewel,

What you are describing could be done a couple different ways, but the best method would likely depend on your situation. Can you provide a bit more detail or maybe an example of what you would expect to happen?

|||

thanks heaps for replying

so I would have a line like this - other info eg Tracker / Description etc would be on the 2nd line

and Criteria would be = Closed

Call ID Received Date Received Time Closed Date Closed Time TIME TAKEN

so Time Taken would be the result from Received to Closed.

thanks

Jewel

|||

To come up with Time Taken in this case, I would probably combine your start date and time into one variable, then combine your end date and time into another variable and perform your subtraction from there. So if you had the following date initially:

Received Date: 01/12/2006
Received Time: 18:23:47
Closed Date: 01/13/2006
Closed Time: 06:35:27

Then you would have two new fields as follows:

Received DateTime: 01/12/2006 18:23:47
Closed DateTime: 01/13/2006 06:35:27

You would then subtract one from the other to get the time difference.

|||

thanks

so I have combined my fields as you said.

When I do the subtraction -

New fields - Text=ClosedDate Text=RecvdDate

=(ClosedDate) - (RecvdDate)

I get error ClosedDate not declared

or should I be using?

thanks

|||

it depends where you combine the fields. You can either do this in you source query or using the .NET object model in an expression. Either way you need to make sure the field has the correct datatype

SQL
===

SELECT ReceivedDateTime = CAST(ReceivedDate + ' ' + ReceivedTime AS DATETIME)
, ClosedDateTime = CAST(ClosedDate + ' ' + ClosedTime AS DATETIME)
FROM your_table

Expression (assuming your fields are string data type)

=CDate(Fields!ClosedDate.Value + " " + Fields!ClosedTime.Value) - CDate(Fields!ReceivedDate.Value + " " + Fields!ReceivedTime.Value)

|||

thanks Adam

that works a treat - appreciate it

DatePart Function

Hi,

I'm curren't writing a stored procedure for my sql server express database and need to display the year part of a date field as '04' but using the DatePart function it will only display as '2004'? Is it possible to get the Year part of the date to display showing the last 2 digits of the Year only? I don't require another part of the date field just the Year part. All help is well come.

Code been used is shown below:

DATEPART(yy,[Date])

Hi,

The datepart function doesn't have an argument to do this. You can do the following to get the desired result:

SELECT RIGHT(DATEPART(yy, [Date]), 2)

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks Geert it worked a treat!!

DateDiff years as a float

Hello,
I would like to calculate the diferrence between two dates and express the
result as a float of the number of years - such as 3.75 or 5.33333. I am
trying...
CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld
(I realize that dividing by 365 is inaccurate, but it is close enough for my
purposes here)
This line truncates the result to 3.0 or 5.0. What do I need to change in
the syntax?
Thanks in advanceHi Mark,
I get the corretn result when I do this
select CONVERT(float, DATEDIFF(d, convert(datetime,'1 jan 2000'),
getdate() ) ) /365 as YearsOld
but to make sure what you can do is this
select CONVERT(float, DATEDIFF(d, convert(datetime,'1 jan 2000'),
getdate() ) ) / CONVERT(float,365) as YearsOld
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Mark Hoffy" <mark@.here.com> wrote in message
news:0nuIe.290$Zo3.52@.fe03.lga...
> Hello,
> I would like to calculate the diferrence between two dates and express the
> result as a float of the number of years - such as 3.75 or 5.33333. I am
> trying...
> CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld
> (I realize that dividing by 365 is inaccurate, but it is close enough for
> my
> purposes here)
> This line truncates the result to 3.0 or 5.0. What do I need to change in
> the syntax?
> Thanks in advance
>
>

date/time fields

Hallo,
I know a lot has already been told about date/time fields in a database but
still confuses me, specif when dealing with SQLserver(Express).
It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
(difference between Express and MSDE2000A ?)
What is the one and only true way to deal with this problem in VB2005:
Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps
dd/MM/yyyy) and time in "hh:mm:ss"
dim MyDateVar as string, MyIdVar as Integer
dim MyCommand = New Sqlcommand("",Connection)
MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar &
"' Where MyIdField = " & MyIdVar = SomeIntegerValue
MyCommand.executenonquery
How to deal with the MyDateVar when:
1.
The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
In this case there is no need to have the time with it.
2.
The date comes from a datetimepicker control
(MyDateVar = DtPicker.Value)?
3.
The date and time comes from the system
MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
So, any help and/or suggestion on this will be greatly appreciated.
Thanks and greetings to all
JeromeJerome,
It is very simple you should never use a date/time as a string, however
always as a DateTime field.
When you present that to a textbox, than you can use the overloaded toString
with the Iformatprovider
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.asp
If you get it back you can use the Cdate
mydateField = Cdate(mytextbox.text)
And if you want to supply it to a database you use the parameters.
http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071
Maybe even better to show with this more extended but with a Dutch datetime
in it and for Access (OleDb)
http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137
In fact is that all.
(The datetimepicker.value returns a datetime field).
Cor|||Hi
This is my little guide :
- For dates, use date variables, not into strings. That way you can
do arithmetics, format properly , passs parameters without problems etc
- Use Cdate when picking up dates/times from text fields
- When calling SQLprocedures, use parameters.
- When building an SQL string in a (VB) program, use date format
yyyy-mm-dd , ie. today is 2006-01-25 , and format explicitly ,
do not rely on implicit (locale dependent) formatting.
ie. SQLtext = ... & format (date_var,"yyyy-mm-dd hh:MM:ss") & ...
The somewhat exotic format does not matter inside a program, the important
thing
is that SQLserver never fails to understand you correctly.
No more lottery if 01/04/06 is April 1st or January 4th or ...
Matti
"Jerome" <Jommeke@.fake.com> wrote in message
news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
> Hallo,
> I know a lot has already been told about date/time fields in a database
> but still confuses me, specif when dealing with SQLserver(Express).
> It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
> (difference between Express and MSDE2000A ?)
> What is the one and only true way to deal with this problem in VB2005:
> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or
> perhaps dd/MM/yyyy) and time in "hh:mm:ss"
> dim MyDateVar as string, MyIdVar as Integer
> dim MyCommand = New Sqlcommand("",Connection)
> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar &
> "' Where MyIdField = " & MyIdVar = SomeIntegerValue
> MyCommand.executenonquery
> How to deal with the MyDateVar when:
> 1.
> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
> In this case there is no need to have the time with it.
> 2.
> The date comes from a datetimepicker control
> (MyDateVar = DtPicker.Value)?
> 3.
> The date and time comes from the system
> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
> So, any help and/or suggestion on this will be greatly appreciated.
> Thanks and greetings to all
> Jerome
>|||I'm a database person, so from a database perspective:
Make sure the variable in the client is date datatype, not string.
Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do the
string conversion for you.
If you absolutely want to pass it as a string to SQL Server, read
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerome" <Jommeke@.fake.com> wrote in message news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
> Hallo,
> I know a lot has already been told about date/time fields in a database but still confuses me,
> specif when dealing with SQLserver(Express).
> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between Express
> and MSDE2000A ?)
> What is the one and only true way to deal with this problem in VB2005:
> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time
> in "hh:mm:ss"
> dim MyDateVar as string, MyIdVar as Integer
> dim MyCommand = New Sqlcommand("",Connection)
> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField = "
> & MyIdVar = SomeIntegerValue
> MyCommand.executenonquery
> How to deal with the MyDateVar when:
> 1.
> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
> In this case there is no need to have the time with it.
> 2.
> The date comes from a datetimepicker control
> (MyDateVar = DtPicker.Value)?
> 3.
> The date and time comes from the system
> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now,
> "yyyyMMdd.hhmmss") gives a runtime error
> So, any help and/or suggestion on this will be greatly appreciated.
> Thanks and greetings to all
> Jerome
>|||Hoi Friends,
Thanks very much the answers. At least these are short, understandable and
valuable answers! Far more better than all the microsoft stuff readings.
I will try the suggestions right away when my sqlserverExpress is working
again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and
Sqlexpress. Nice!
Anyway, the answers leaves my with one more question:
What are the benifits of using Parameters instead plain variables (for
numeric or charachter fields at least)?
As i can see at a first glance there is a lot more wrtiting to do for the
Parameters. (adding them to a command before they are usable, defining the
number of chars for a string param, etc,etc)?
For instance: If the client decides that a stringfield should have more
characters capacity, one should go trough the whole project and adjust the
number of chars for the Params that points to that specific field? Or can
one program a param with, let's say 100 chars, where the field is only 50
chars ? The max charachters is limited by the maxlength property of the
textbox anyway.
Thanks once again for the answers and suggestions
Jerome
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> I'm a database person, so from a database perspective:
> Make sure the variable in the client is date datatype, not string.
> Pass it though a command object and a parameter object to SQL Server = you
> are safe. ADO will do the string conversion for you.
> If you absolutely want to pass it as a string to SQL Server, read
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerome" <Jommeke@.fake.com> wrote in message
> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> Hallo,
>> I know a lot has already been told about date/time fields in a database
>> but still confuses me, specif when dealing with SQLserver(Express).
>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
>> (difference between Express and MSDE2000A ?)
>> What is the one and only true way to deal with this problem in VB2005:
>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or
>> perhaps dd/MM/yyyy) and time in "hh:mm:ss"
>> dim MyDateVar as string, MyIdVar as Integer
>> dim MyCommand = New Sqlcommand("",Connection)
>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar
>> & "' Where MyIdField = " & MyIdVar = SomeIntegerValue
>> MyCommand.executenonquery
>> How to deal with the MyDateVar when:
>> 1.
>> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
>> In this case there is no need to have the time with it.
>> 2.
>> The date comes from a datetimepicker control
>> (MyDateVar = DtPicker.Value)?
>> 3.
>> The date and time comes from the system
>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
>> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
>> So, any help and/or suggestion on this will be greatly appreciated.
>> Thanks and greetings to all
>> Jerome
>>
>|||> What are the benifits of using Parameters instead plain variables (for numeric or charachter
> fields at least)?
* Avoid "SQL Injection" (Google and you will find.)
* Assuming that ADO.NET is smart enough to execute your code using sp_executesql and make parameters
for that out of your ADO.NET parameters: You will have a lot greater chance for your query plan to
be re-used.
If you just build a string and first search for "johnson", then SQL Server can cache that plan. But
that cached plan is identified (basically) based on all the text in the query. "johnson" is a part
of that text. Next time, you search for "smith", and SQL Server first searches for a plan match.
Such doesn't exists (you searched for "johnson" last time). So a new plan will be added to plan
cache for this query with "smith" embedded. I've seen installations with 10,000 instances of plans
in cache for the same query! And how much memory is now available for caching data? Not to speak
about the overhead of searching through many many thousands of plans in cache in order to find a
match - every time you execute a query - in vain. If they were parametized, then you'd have only one
plan for the query in cache, and SQL Server would substitute the parameters.
* Better yet, use stored procedures. This way you also have control over if this plan should be
cached in the first place and also plan recompiles. Along with bunch of other advantages of using
stored procedures.
*"Feels better"
I bet others can jump in with other advantages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerome" <Jommeke@.fake.com> wrote in message news:oK2Cf.211227$Xy5.6942778@.phobos.telenet-ops.be...
> Hoi Friends,
> Thanks very much the answers. At least these are short, understandable and valuable answers! Far
> more better than all the microsoft stuff readings.
> I will try the suggestions right away when my sqlserverExpress is working again. Yesterday Mr.
> Murphy came to visit and ruined my VS2005 and Sqlexpress. Nice!
> Anyway, the answers leaves my with one more question:
> What are the benifits of using Parameters instead plain variables (for numeric or charachter
> fields at least)?
> As i can see at a first glance there is a lot more wrtiting to do for the Parameters. (adding them
> to a command before they are usable, defining the number of chars for a string param, etc,etc)?
> For instance: If the client decides that a stringfield should have more characters capacity, one
> should go trough the whole project and adjust the number of chars for the Params that points to
> that specific field? Or can one program a param with, let's say 100 chars, where the field is only
> 50 chars ? The max charachters is limited by the maxlength property of the textbox anyway.
> Thanks once again for the answers and suggestions
> Jerome
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef in bericht
> news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
>> I'm a database person, so from a database perspective:
>> Make sure the variable in the client is date datatype, not string.
>> Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do
>> the string conversion for you.
>> If you absolutely want to pass it as a string to SQL Server, read
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerome" <Jommeke@.fake.com> wrote in message
>> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> Hallo,
>> I know a lot has already been told about date/time fields in a database but still confuses me,
>> specif when dealing with SQLserver(Express).
>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between
>> Express and MSDE2000A ?)
>> What is the one and only true way to deal with this problem in VB2005:
>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time
>> in "hh:mm:ss"
>> dim MyDateVar as string, MyIdVar as Integer
>> dim MyCommand = New Sqlcommand("",Connection)
>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField =>> " & MyIdVar = SomeIntegerValue
>> MyCommand.executenonquery
>> How to deal with the MyDateVar when:
>> 1.
>> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
>> In this case there is no need to have the time with it.
>> 2.
>> The date comes from a datetimepicker control
>> (MyDateVar = DtPicker.Value)?
>> 3.
>> The date and time comes from the system
>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now,
>> "yyyyMMdd.hhmmss") gives a runtime error
>> So, any help and/or suggestion on this will be greatly appreciated.
>> Thanks and greetings to all
>> Jerome
>>
>|||Hoi Tibor,
That explains a lot.
SQL Injection, in my case, is unlikely to occur.They are not going to tamper
with the application. There are a maximu of 4 persons working with the
application and the whole bunch is not even connected to the internet.
Nobody at the site in question ever heard about Sql not to speak about
running a query! I was obliged to use an existing MsAccess Db as backend
(they already are working for years with Access) ;-) and i had to enhance
and expanding the application. So, rewriting 200+ functions!?
Now i'm trying for myself and for learning purposes to rebuild parts of the
applic in VB2005 and with a sqlexpress as backend and that's when i ran into
those date problems. Perhaps that explains a bit more my questions and i am
happy that people like you and others are willing to give advice. If you
have to learn it from the books of Microsoft....pfff. Even for a simple
readonly lookup table and a combobox they lead trough a complete
strongly-typed dataset! Ridicolous
Anyway, thanks a lot for the feedback
Jerome
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eFrJeZnIGHA.1728@.TK2MSFTNGP09.phx.gbl...
>> What are the benifits of using Parameters instead plain variables (for
>> numeric or charachter fields at least)?
> * Avoid "SQL Injection" (Google and you will find.)
> * Assuming that ADO.NET is smart enough to execute your code using
> sp_executesql and make parameters for that out of your ADO.NET parameters:
> You will have a lot greater chance for your query plan to be re-used.
> If you just build a string and first search for "johnson", then SQL Server
> can cache that plan. But that cached plan is identified (basically) based
> on all the text in the query. "johnson" is a part of that text. Next time,
> you search for "smith", and SQL Server first searches for a plan match.
> Such doesn't exists (you searched for "johnson" last time). So a new plan
> will be added to plan cache for this query with "smith" embedded. I've
> seen installations with 10,000 instances of plans in cache for the same
> query! And how much memory is now available for caching data? Not to speak
> about the overhead of searching through many many thousands of plans in
> cache in order to find a match - every time you execute a query - in vain.
> If they were parametized, then you'd have only one plan for the query in
> cache, and SQL Server would substitute the parameters.
> * Better yet, use stored procedures. This way you also have control over
> if this plan should be cached in the first place and also plan recompiles.
> Along with bunch of other advantages of using stored procedures.
> *"Feels better"
> I bet others can jump in with other advantages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerome" <Jommeke@.fake.com> wrote in message
> news:oK2Cf.211227$Xy5.6942778@.phobos.telenet-ops.be...
>> Hoi Friends,
>> Thanks very much the answers. At least these are short, understandable
>> and valuable answers! Far more better than all the microsoft stuff
>> readings.
>> I will try the suggestions right away when my sqlserverExpress is working
>> again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and
>> Sqlexpress. Nice!
>> Anyway, the answers leaves my with one more question:
>> What are the benifits of using Parameters instead plain variables (for
>> numeric or charachter fields at least)?
>> As i can see at a first glance there is a lot more wrtiting to do for the
>> Parameters. (adding them to a command before they are usable, defining
>> the number of chars for a string param, etc,etc)?
>> For instance: If the client decides that a stringfield should have more
>> characters capacity, one should go trough the whole project and adjust
>> the number of chars for the Params that points to that specific field? Or
>> can one program a param with, let's say 100 chars, where the field is
>> only 50 chars ? The max charachters is limited by the maxlength property
>> of the textbox anyway.
>> Thanks once again for the answers and suggestions
>> Jerome
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schreef in bericht news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
>> I'm a database person, so from a database perspective:
>> Make sure the variable in the client is date datatype, not string.
>> Pass it though a command object and a parameter object to SQL Server =>> you are safe. ADO will do the string conversion for you.
>> If you absolutely want to pass it as a string to SQL Server, read
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerome" <Jommeke@.fake.com> wrote in message
>> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> Hallo,
>> I know a lot has already been told about date/time fields in a database
>> but still confuses me, specif when dealing with SQLserver(Express).
>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
>> (difference between Express and MSDE2000A ?)
>> What is the one and only true way to deal with this problem in VB2005:
>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or
>> perhaps dd/MM/yyyy) and time in "hh:mm:ss"
>> dim MyDateVar as string, MyIdVar as Integer
>> dim MyCommand = New Sqlcommand("",Connection)
>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" &
>> MyDateVar & "' Where MyIdField = " & MyIdVar = SomeIntegerValue
>> MyCommand.executenonquery
>> How to deal with the MyDateVar when:
>> 1.
>> The Variable comes from a textbox knowing that the user puts in
>> dd/MMyyyy
>> In this case there is no need to have the time with it.
>> 2.
>> The date comes from a datetimepicker control
>> (MyDateVar = DtPicker.Value)?
>> 3.
>> The date and time comes from the system
>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
>> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
>> So, any help and/or suggestion on this will be greatly appreciated.
>> Thanks and greetings to all
>> Jerome
>>
>>
>