db error: instead

Showing posts with label instead. Show all posts

Monday, March 19, 2012

DateTime parameter format

Hi everybody

A quick question - is it possible to present DateTime report parameters with specified format. Let's say instead of '9/3/2005 12:00 AM' i'd like to show '9/3/2005' only. Plus maybe it's possible to plug in some sort of a popup calendar.

Thanks for response,
Konstantin

At least in RS 2000 if you define the parameter as string (instead of datetime) then it'll be shown in the format that you want.
And I don't know if the datepicker can be used in RS (maybe someone else could help us)|||One way of pluging in a popup calendar would be to create a front-end application to the report. You would use either the URL Builder functionality (fast) or the Web Service (slow) to run the report. The parameters could be created from any control (calendar, datagrid, etc...) There are some sample apps out there that do this (and some that run as Sharepoint web parts.)
An option to format dates is to use a Date Key (integer) and Date Name (formatted date) drop down list of dates. The report would have the date key passed in - the stored procedure would translate to the date selected.
Hope this helps,
Andrew.|||

I see
i guess second option would solve formatting issue.

Thanks,
Konstantin

Sunday, March 11, 2012

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:mm Tongue Tied s.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

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)

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

- 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…

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

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:mm Tongue Tied s.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……………….

|||

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]

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

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…

|||

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

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

THE SOLUTION

MS - this should be in your court

RESULT

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…

Wednesday, March 7, 2012

DateTime Bugs?

Hi,
Below shown simple script to get the w

day. Any idea why the w

day
for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
2006 2:00PM') '
TEST
--
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
OUTPUT
--
2
Changed language setting to Espaol.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.
Thanks,
KennyI believe it is something to do with which day of the w

to be considered
as first day. As default (English) it is Sunday.
If you issue SET DATEFIRST 7 (7 represents Sunday) just before DATAEPART
function, it should solve your "bug".
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
SET DATEFIRST 7
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the w

day. Any idea why the w

day
> for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
> 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>|||Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of w

s-within-year numbers.|||What are you talking about? 8601 was not even out until 1988 and was not
popular until second version in 2000. Sybase was created before that.
Also, check the calendar on your desk. It starts with Sunday. People were
using start of w

on Sunday long before ISO. Besides, you can change the
start day anyway.
William Stacey [MVP]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137739565.063520.234520@.g14g2000cwa.googlegroups.com...
| Microsoft failed to follow ISO standards about day of the wek numbers.
| They also wrote their own version of w

s-within-year numbers.
||||Hello, Joe
Indeed, the w

numbers returned by the DATEPART are not the ISO w

numbers. There is an example in Books Online on how to create a UDF to
return the ISO w

number. However, the original poster was talking
about w

days, not w

numbers (which is a completely different
thing).
Razvan|||As indicated in other posts, day of w

is dependent on which country you l
ive in. In the US,
Sunday is the first day of the w

. In Sweden (and majority of Europe, prob
ably all), first day of
w

is Monday. DATEPART to calculate day of w

is dependent on SET LANGUA
GE and can be overridden
with SET DATEFIRST.
set language us_english
print DATEPART(dw,getdate())
set language british
print DATEPART(dw,getdate())
set language spanish
print DATEPART(dw,getdate())
set language polish
print DATEPART(dw,getdate())
set language german
print DATEPART(dw,getdate())
set language swedish
print DATEPART(dw,getdate())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kenny" <keejh@.hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl..
.
> Hi,
> Below shown simple script to get the w

day. Any idea why the w

day
for spanish datetime is
> 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>

Friday, February 17, 2012

DateDiff Function February question

I'm executing the following datediff function and getting
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
VicDATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
>> I'm executing the following datediff function and
getting
>> a value of 4 instead of 5. I don't understand why.
>> Please help.
>> select DateDiff(mm,'10/01/2004','02/28/2005')
>> TIA,
>> Vic
>
>.
>|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
> >--Original Message--
> >DATEDIFF(mm) liteterally counts the nubmer of months,
> regardless of when in
> >the month the queries are made. For instance:
> >
> >select DateDiff(mm,'20041031','20050101')
> >
> >This returns 3 -- November, December, January
> >
> >In your case, it looks like you want to "round" based on
> day of the month.
> >You could try something like:
> >
> >DECLARE @.startdate smalldatetime
> >DECLARE @.enddate smalldatetime
> >
> >SET @.startdate = '20041001'
> >SET @.enddate = '20050228'
> >
> >SELECT DateDiff(mm,@.startdate,@.enddate)
> > + CASE
> > WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> > WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> > ELSE 0
> > END
> >
> >This is probably flawed in one or more ways, so you
> should tweak it to get
> >whatever logic you actually need.
> >
> >Also, please notice that I used the date format YYYYMMDD
> instead of
> >MM/DD/YYYY -- The latter format is ambiguous, as it can
> change based on
> >locale. Please try to stick with the ISO standard
> format instead.
> >
> >
> >--
> >Adam Machanic
> >SQL Server MVP
> >http://www.sqljunkies.com/weblog/amachanic
> >--
> >
> >
> >"Vic" <vduran@.specpro-inc.com> wrote in message
> >news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> >> I'm executing the following datediff function and
> getting
> >> a value of 4 instead of 5. I don't understand why.
> >> Please help.
> >>
> >> select DateDiff(mm,'10/01/2004','02/28/2005')
> >>
> >> TIA,
> >> Vic
> >
> >
> >.
> >|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>>--Original Message--
>>DATEDIFF(mm) liteterally counts the nubmer of months,
>>
>regardless of when in
>
>>the month the queries are made. For instance:
>>select DateDiff(mm,'20041031','20050101')
>>This returns 3 -- November, December, January
>>In your case, it looks like you want to "round" based on
>>
>day of the month.
>
>>You could try something like:
>>DECLARE @.startdate smalldatetime
>>DECLARE @.enddate smalldatetime
>>SET @.startdate = '20041001'
>>SET @.enddate = '20050228'
>>SELECT DateDiff(mm,@.startdate,@.enddate)
>>+ CASE
>> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
>> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
>> ELSE 0
>>END
>>This is probably flawed in one or more ways, so you
>>
>should tweak it to get
>
>>whatever logic you actually need.
>>Also, please notice that I used the date format YYYYMMDD
>>
>instead of
>
>>MM/DD/YYYY -- The latter format is ambiguous, as it can
>>
>change based on
>
>>locale. Please try to stick with the ISO standard
>>
>format instead.
>
>>--
>>Adam Machanic
>>SQL Server MVP
>>http://www.sqljunkies.com/weblog/amachanic
>>--
>>
>>"Vic" <vduran@.specpro-inc.com> wrote in message
>>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
>>
>>I'm executing the following datediff function and
>>
>getting
>
>>a value of 4 instead of 5. I don't understand why.
>>Please help.
>>select DateDiff(mm,'10/01/2004','02/28/2005')
>>TIA,
>>Vic
>>
>>.
>>

DateDiff Function February question

Tuesday, February 14, 2012

dateadd update works but adds 2 or 3 yrs-not 1

I have a page that is supposed to add a year to a record when it loads. The problem is that it adds 2 or three years instead.
Here is the page_load event:

Sub page_load(sender as object, e as eventargs)
 Try
 Dim connection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim command As SqlCommand = new SqlCommand("Updateexpiredate", connection)
 command.CommandType = CommandType.StoredProcedure Dim param0 As SqlParameter = new SqlParameter("@.memberid",SqlDbType.Int)
 param0.Direction = ParameterDirection.Input
 param0.Value = memberid
 command.Parameters.Add(param0)
 connection.Open()
command.ExecuteNonQuery()
 connection.Close()
 myerror.Text = "Thank You! Your account was updated"
 Catch ex As Exception
 myerror.Text = ex.Message
 End Try
End Sub

And here is the SPROC:

CREATE PROCEDURE Updateexpiredate
 (
 @.memberid int
 )
 AS
 UPDATE
 members
 SET
 expiredate=(dateadd(year,1,expiredate))<--I also tried expiredate=(dateadd(month,12,expiredate)) with the same results
 WHERE
 memberID = @.memberID
GO

... I'm assuming you're calling that method or procedure 2 or three time. :)

edited: Step through your app. It could be that it's called multiple times.|||How do I do that?
I don't even know where to begin with that.
Thanks|||The first thing I would do is turn tracing on for the page, and do a trace.write as the command right before the ExecuteNonQuery. This should give you more information on how many times that part of your code is being executed.

If you are not familiar with trace.write, look here:Trace Logging to Page Output. That is a good debugging tool to have in your pocket.

Terri|||What do you use to develop in? Is it Visual Studio, DreamWeaver, WebMatrix, notepad?

Monday, March 19, 2012

DateTime parameter format

Sunday, March 11, 2012

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

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

Wednesday, March 7, 2012

DateTime Bugs?

Friday, February 17, 2012

DateDiff Function February question

DateDiff Function February question

DateDiff Function February question

Tuesday, February 14, 2012

dateadd update works but adds 2 or 3 yrs-not 1

db error

Blog Archive

About Me