db error: insert

Showing posts with label insert. Show all posts

Thursday, March 22, 2012

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.Min won't insert into SQL Server Mobile 3.0

Hi all,

In my C# code I have a Class property that takes the value DateTime.Min upon initialisation, but when I try to insert this into the database column (yes, it is DateTime data type :)) I get an 'Unexpected Error' from SQL Server Mobile.

Is this a known?

Tryst

It is due to the diffrence between the Min date of C# and the Min Date of SQL Server. The Min Date in C# is 01/01/01 while in SQL Server it is 01/01/1753 ... so create your own Min Date equlient to SQL Server Min Date and the Problem will be resolved.

|||ok, thanks, Akbar Khan.sql

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime,
>> what am I losing out on ?
>> Would I be able to perform the same functions against a varchar datatype
>> as opposed to datetime such as datepart, datediff,etc. ?
>>
>|||Downsides of using varchar include:
There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
Performing various datetime calculation might mean bad performance, since you might end up with a
convert on the column side in your predicate.
You need to decide on a format. If you want to retrieve the value and present it in a different
format from what it is stored with you have more work to do.
...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Kalen,
> Does not seem to be any difference with that example.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to datetime
>> such as datepart, datediff,etc. ?
>>
>>
>|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a
>> start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to
>> datetime, what am I losing out on ?
>> Would I be able to perform the same functions against a varchar
>> datatype as opposed to datetime such as datepart, datediff,etc. ?
>>
>>
>>
>|||On Feb 11, 6:46=A0am, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype a=s
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation|||> Hassan would have found all that out if he read the article I pointed him to.
Ah, that ol' article ;-)
Thanks Kalen :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkINH5MbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Hassan would have found all that out if he read the article I pointed him to.
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
>> Downsides of using varchar include:
>> There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
>> Performing various datetime calculation might mean bad performance, since you might end up with a
>> convert on the column side in your predicate.
>> You need to decide on a format. If you want to retrieve the value and present it in a different
>> format from what it is stored with you have more work to do.
>> ...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to
>> datetime such as datepart, datediff,etc. ?
>>
>>
>>
>>
>

Wednesday, March 21, 2012

DateTime string insert into sql datetime column fails

Iam trying to write to a DateTime field in MSSQL from wonderware
intouch. The problem is that I keep getting the error that the string
I'm using is not a valid datetime string....has anybody experienced
this and what was the workaround?
Thanks
GaryThis should arm you with enough information to understand why the operation
fails:
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/
<GaryCharlotte@.Charter.net> wrote in message
news:1145071019.588279.87720@.i39g2000cwa.googlegroups.com...
> Iam trying to write to a DateTime field in MSSQL from wonderware
> intouch. The problem is that I keep getting the error that the string
> I'm using is not a valid datetime string....has anybody experienced
> this and what was the workaround?
> Thanks
> Gary
>|||Thank you Tibor.
I have tried various combinations including the recommended on that
site ie '02/23/1998 14:23:05'
Still no joy. I wonder if this is a wonderware sqlinsert problem...|||> I have tried various combinations including the recommended on that
> site ie '02/23/1998 14:23:05'
That's not recommended, it will fail if, for example, your dateformat is
dmy.
What does "no joy" mean? Does it fail? With what error? Did you try a
safe standard format like
'19980223 14:23:05'
?|||OK I found out what the problem is. If you use a SQLInsertprepare and
SQLInsertexecute it fails no matter what format you use.
Used SQLConnect, SQLInsert and SQLDisconnect and it works great!
Thanks for the help Tiborsql

datetime problem

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

TIA

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

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

|||

thanks mikesdotnetting! worked like a charm!

|||

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

Monday, March 19, 2012

DateTime help

This simply shouldn't take all morning to figure out but for some reason it has. I simply want to insert the current date and time into a datetime field.

No matter what I try I either get errors (Syntax error converting datetime from character string.) or I get the wrong date (4/11/1900, 1/1/1900).

Here's my current SQL which gives the syntax error.


CREATE PROCEDURE [dbo].[QuoteApprovalWeb_Approve]
@.table nvarchar(50),
@.approvedby nvarchar(100),
@.quote nvarchar(50),
@.dt datetime
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'Update [' + @.table + '] set quoteapproval = ' + "'" + @.approvedby + "', "
Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='
Select @.SQL = @.SQL + "'" + @.quote + "'"
exec (@.sql)
GO

I tried replacing @.dt with getdate() but that would always give me errors also.replace the line

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='

with

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + '''' + CAST(@.DT AS VARCHAR(20))+ '''' + ' where quoteno ='

your string would look something like
quoteapprovaldate = 'Apr 5 2004 3:35PM' where quoteno =

Navin|||Sweet - that works great!

Thanks much.

Sunday, March 11, 2012

DateTime format during INSERT using MS JDBC driver

Hi,
Using the latest MS JDBC-driver:
The date format of the SQL Server causes me some trouble at the moment:
My INSERT statement uses DateTime values of the format "yyyy-mm-dd
hh:mm:ss.mmm" but they somehow get interpreted as"yyyy-dd-mm" causing a date
format out of range exception.
How can I "force" SQL Server to use my date format? Calling "SET DATEFORMAT
YMD" on each open connection before the statement is executed does not help
but this may be caused by additional connections being opened "behind the
scenes" (so I've been told). I'm not sure this is so even if my connection
pool keeps connections open.
The db-user opening the connection has the correct language setting and the
collation label of the databases is also correct.
So I'm wondering whether it's the regional setting of the Windows account
that runs the SQLServer-service (or SQLSERVERAGENT-Service?) that causes
this? But then - what region would use "yyyy-dd-mm" as it's date format?
Or is there a setting on the JDBC-driver that can modify this behaviour?
- Tim
Hi Tim,
You can either use the parameterized query and pass in a value of a
Java.Sql.Date type. You can thus convert the "yyyy-mm-dd" string to Date.
Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (?)");
st.setDate(1, Date.valueOf("1999-01-30"));
st.executeUpdate();
Or if you choose to hard code the "yyyy-mm-dd" in the query string, you can
use CONVERT function to convert the string to a datetime sql data type
according to the proper style of date format. 120 is the ODBC canonical
style that converts yyyy-mm-dd hh:mi:ss(24h). Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (CONVERT (datetime, '1999-01-30', 120) )");
st.executeUpdate();
For more info on CONVERT, please refer to the SQL Server Books Online.
Yilei

Thursday, March 8, 2012

Datetime data type resulted in an out-of-range datetime value. Please help

Hi,

I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date

'31/08/2006 23:28:59'

I get the error "...datetime data type resulted in an out-of-range datetime value"

I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?

cheers,

Ernest

I guess itis caused by the date format in SQL Server. Please try following statements:

set DATEFORMAT dmy
declare @.t smalldatetime
set @.t='31/08/2006 23:28:59'
select @.t

|||

Thanks Lori,

It appears that when I use parameters in my SqlCommand object this works like a treat. God bless the parameters!!

Datetime data type ?

I have a table in the database. One of the column is of datetime type.

What is the best way to insert and retrieve value from that column. Here is the scenario

1) I want to insert value '02/03/2006' into the column. How does the sql server know that the month is 02 and not 03. Will it look into the system settings. If so then can I sepcify custom format to distinguish between month,day and year.

2) I want to retrieve value from the datetime column in the format dd/mm/yyyy hh:mm:ss AM/PM . What sql statement I have to use??

ThanksI belive SQL uses the language setting to determine how you are entering the date, so when you are using the English language and Date is setup at 2/3/06 it assume mm/dd/yyyy. To retrun the date time value you want to return try using this:

Select Convert(varchar(20),getdate(),109). To get the AM/PM setup try this website, http://sqladvice.com/blogs/repeatableread/archive/2005/02/22/4261.aspx
They have a function already written to do that.|||1. SET DATEFORMAT (http://msdn2.microsoft.com/en-us/library/ms189491.aspx)

2. The SQL Statement should only retrieve the data, not format it. The workstation client software ought to control the formatting. If you really want to cause yourself frustration, periodically reformat your boot drive... That will waste about as much time as formatting data on the SQL Server, and will be more obvious in how it wastes that time... If you prefer the subtle approach to wasting time, you can use the SQL Convert (http://msdn2.microsoft.com/en-us/library/ms187928.aspx) function to happily waste many hours!

-PatP|||The SQL Statement should only retrieve the data, not format it. The workstation client software ought to control the formatting. If you really want to cause yourself frustration, periodically reformat your boot drive... That will waste about as much time as formatting data on the SQL Server, and will be more obvious in how it wastes that time... If you prefer the subtle approach to wasting time, you can use the SQL Convert (http://msdn2.microsoft.com/en-us/library/ms187928.aspx) function to happily waste many hours!

-PatPHad your coffee yet this morning, Mr. Sunshine?|||Had your coffee yet this morning, Mr. Sunshine?Oh, can't you just see me jazzed on caffine too? Egad!

-PatP|||Web2000:

In case it wasn't obvious from the silly description, I was trying to inject a bit of humor into the description of a very bad idea...

While it is possible to format your data using SQL Server, I've never seen a case where it was a good idea. This actually makes it almost impossible to handle locale dependant issues, which in turn makes your code almost impossible to scale. If you handle the formatting at the client (even if that is a web browser), you can decide on a client-by-client basis how to handle the formatting (which is typically done for you with zero effort on your part), and you distribute the workload involved in that formatting. It is a win/win situation for you as a developer.

-PatP|||One format that never fails is YYYY-MM-DD, no matter what are your regional settings, SQL Server always will correctly parse and uses a date formated in that way.

Datetime data type ?

I have a table in the database. One of the column is of datetime type.

What is the best way to insert and retrieve value from that column. Here is the scenario

1) I want to insert value '02/03/2006' into the column. How does the sql server know that the month is 02 and not 03. Will it look into the system settings. If so then can I sepcify custom format to distinguish between month,day and year.

2) I want to retrieve value from the datetime column in the format dd/mm/yyyy hh:mm:ss AM/PM . What sql statement I have to use?

Thanks

This is a very good question, one that many forgets to ask (until it's too late) =;o)

1) This is pretty easy. The answer is to use another format, one that cannot be misunderstood regardless of language or date settings. The most common is ssyymmdd (eg 20060203 for february third this year)

You can find an excellent article on the subject here.

How do I delimit/format dates for database entry?
http://www.aspfaq.com/show.asp?id=2023

2) The display of dates are controlled by converting the datetime to a string and supplying a style parameter for the output you wish to have. These can be found in BOL under the pargraph that discusses 'CONVERT'. At a quick glance it doesn't look like there's one that exactly matches what you want, but it's possible to use different parts and concatenate them together in order to make a 'custom' format.

select convert(char(10), getdate(), 103) + ' ' + convert(char(8), getdate(), 108)

.. is close, but the time is in 24hr format instead of AM/PM.

If you really must have AM/PM and a 12hr clock, there are formats that one could use, but the code to rip out those pieces would be a bit more complex.

/Kenneth

datetime Data Type

I am trying to insert dates and times into a SQL database using a small ASP
application I have just written to test it.
The dates are being passed in format: dd/mm/yyyy, and the times in format:
hh:mm:ss
However, when I set the fields as datatype datetime, it fails saying:
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
What am I doing wrong? If I change the datatype of the field to char it
works fine, but I wanted them as datetime.
What do I need to change?
ThanksIf you want to that format, you need to have proper SET DATEFORMAT setting.
I suggest you read below article, and use a language neutral format.
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:OFPuQX6CEHA.3280@.TK2MSFTNGP09.phx.gbl...
> I am trying to insert dates and times into a SQL database using a small
ASP
> application I have just written to test it.
> The dates are being passed in format: dd/mm/yyyy, and the times in format:
> hh:mm:ss
> However, when I set the fields as datatype datetime, it fails saying:
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> What am I doing wrong? If I change the datatype of the field to char it
> works fine, but I wanted them as datetime.
> What do I need to change?
> Thanks
>

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and this is okay on one
> server (which may have British language settings) but not on another (which may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
>> Hello!
>> I'm using the same script to insert/update records on diferent versions of SQL but i'm getting
>> this error:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de dados char em um tipo de
>> dados datetime resultou em um valor datetime fora do intervalo.
>> (translation: error converting one string into datetime value out of range)
>> The SQL versions that I am probing is 8.00.194 (RTM) that is installed with Microsoft SQL
>> Personal Engine CD and ther other version is 8.00.2039 (SP4) that i've downloaded and installed.
>> Can anywone help me?
>> Regards,
>> kTodos
>

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um
tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de
um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/in...ime.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) a
nd this is okay on one
> server (which may have British language settings) but not on another (whic
h may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates
as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2
MSFTNGP03.phx.gbl...
>

Wednesday, March 7, 2012

Datetime changes when insert

Hi,

I have one table with data that has two datetime columns. I also have an empty table that is i copy of the first (ie identical design) + one column, that I want to move some rows to.

When I do the insert into the empty table the datetime fileds are converted from '2004-01-01' to 'Jan 1 2004 12:00AM' WHY!!!

I hate this kind of problems... I haven't told it to convert, and besides it adds a time too...

Anyone got a solution or explanation for this, please?I don't think it's been converted. It's just the way you display it. The value stays the same.

Datetime calculation help

Hi Everyone,
I have got a problem with date calculation. I have a procedure that all
me to insert date into a Table based on user input. The input is a Event
Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w

prior to the
event date as the reminder
My question is how do I calculate prior w

of a certain Date.. e.g Event
Date = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Below is my procedure:
CREATE PROCEDURE EventReminder
@.DocketID int,
@.EventName varchar(50),
@.Reminder int,
@.EventNumber int,
@.EventDate varchar(50)
AS
--Declare variables
Declare @.EventStartNum int,
@.EventReminderNum int,
@.EventDate1 datetime,
@.EventNum int
--Initialize the Variables
set @.EventStartNum = 0
set @.EventReminderNum = 0
set @.EventNum = -1
--Delete the Reminder if the DocketID already exist
delete from reminder where DocketID = @.DocketID
--Start the loop
while @.EventStartNum < @.EventNumber
Begin --Start Begin
set @.EventStartNum = @.EventStartNum + 1
--W

ly Reminder
if @.EventNumber = 1
begin
while @.Reminder >
@.EventReminderNum
begin
--Increment of the w

set @.EventReminderNum =
@.EventReminderNum + 1
set @.EventDate1 = DATEADD(w

,
@.EventReminderNum, @.EventDate)
insert into Reminder
(DocketID, EventDate, EventName, Reminder)
Values
(@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
set @.EventNum = @.EventNum - 1
end
end
--print 'The counter is ' +
convert(varchar(50),@.EventDate1,101)
end --End Begin
GOUse function DATEADD.
Example:
select dateadd(ww, -1, '20050114')
go
AMB
"Roplab" wrote:

> Hi Everyone,
> I have got a problem with date calculation. I have a procedure that al
l
> me to insert date into a Table based on user input. The input is a Event
> Date and Reminder
> Example: if the user Enter an Event Date and choose to a reminder for a
> certain event... I need to calculate a date that will be a w

prior to t
he
> event date as the reminder
> My question is how do I calculate prior w

of a certain Date.. e.g Even
t
> Date = 01/14/2005 I want the reminder to be calculate has
> Reminder=01/07/2005
> Below is my procedure:
> CREATE PROCEDURE EventReminder
> @.DocketID int,
> @.EventName varchar(50),
> @.Reminder int,
> @.EventNumber int,
> @.EventDate varchar(50)
> AS
> --Declare variables
> Declare @.EventStartNum int,
> @.EventReminderNum int,
> @.EventDate1 datetime,
> @.EventNum int
> --Initialize the Variables
> set @.EventStartNum = 0
> set @.EventReminderNum = 0
> set @.EventNum = -1
>
> --Delete the Reminder if the DocketID already exist
> delete from reminder where DocketID = @.DocketID
> --Start the loop
> while @.EventStartNum < @.EventNumber
> Begin --Start Begin
> set @.EventStartNum = @.EventStartNum + 1
> --W

ly Reminder
> if @.EventNumber = 1
> begin
> while @.Reminder >
> @.EventReminderNum
> begin
> --Increment of the w

> set @.EventReminderNum =
> @.EventReminderNum + 1
> set @.EventDate1 = DATEADD(wee
k,
> @.EventReminderNum, @.EventDate)
> insert into Reminder
> (DocketID, EventDate, EventName, Reminder)
> Values
> (@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
> set @.EventNum = @.EventNum -
1
> end
> end
> --print 'The counter is ' +
> convert(varchar(50),@.EventDate1,101)
> end --End Begin
> GO
>
>

DateTime as one parameter

I have this question in one gridview:

InsertCommand="INSERT INTO [Member] ([IC], [FirstName], [LastName], [Gender], [Birth], [Telephone], [Mobile], Email , [Address], [Postcode], [TimeOfRegistration]) VALUES (@.IC, @.FirstName, @.LastName, @.Gender, @.Birth, @.Telephone, @.Mobile, @.Email, @.Address, @.Postcode,@.TimeOfRegistration)"

If I want to insert into TimeOfRegistration with "System.DateTime.Today",

can I have some expression to replace the "@.TimeOfRegistration" in the SQL query? (<%# %>?Maybe?)

If cannot, in the parameter control below, can I put it this way to achieve it?

<asp:Parameter Name="TimeOfRegistration" Type=DateTime DefaultValue="<%# %>" />

If so, how to put it?

Thank you very much!

InsertCommand="INSERT INTO [Member] ([IC], [FirstName], [LastName], [Gender], [Birth], [Telephone], [Mobile],

, [Address], [Postcode], [TimeOfRegistration]) VALUES (@.IC, @.FirstName, @.LastName, @.Gender, @.Birth, @.Telephone, @.Mobile, @.Email, @.Address, @.Postcode, getdate())"|||InsertCommand="INSERT INTO [Member] ([IC], [FirstName], [LastName], [Gender], [Birth], [Telephone], [Mobile],

, [Address], [Postcode], [TimeOfRegistration]) VALUES (@.IC, @.FirstName, @.LastName, @.Gender, @.Birth, @.Telephone, @.Mobile, @.Email, @.Address, @.Postcode, getdate())"|||

Thanks Motley!

I reminds me that I should refresh some of the SQL basics which I forgot long ago^^

|||Thanks Motley It helps me a lot mate... Big Smile

Saturday, February 25, 2012

Dates Help Needed

Hey Gurus
Can you give me a clue to how to do the produce the following Output from
below table.
insert into Q2 (Emp_name,Category,StartDate,EndDate)
Select 'John', 'A10', '19961001','20000807'
Union
Select'John', 'G20', '20000803','20000815'
Union
Select 'John', 'A20', '20000807','20000822'
Union
Select'John', 'G30', '20000817','20000825'
Union
Select'John', 'A30', '20000822','99991231'
I want the result to Look like
Emp_Name Category1 Category2 StartDate EndDAte
John A10 20000801 20000803
John A10 G20 20000803 20000807
John A20 G20 20000807 20000815
John A20 20000815 20000817
John A20 G30 20000817 20000822
John A30 G30 20000822 20000825
John A30 20000825 20000831Jason
What is the purpose? Can you explain why would you want this ouptut? Based
on what?
"Jason" <bornscorpio30@.yahoo.com> wrote in message
news:eV341XZSGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Hey Gurus
> Can you give me a clue to how to do the produce the following Output from
> below table.
> insert into Q2 (Emp_name,Category,StartDate,EndDate)
> Select 'John', 'A10', '19961001','20000807'
> Union
> Select'John', 'G20', '20000803','20000815'
> Union
> Select 'John', 'A20', '20000807','20000822'
> Union
> Select'John', 'G30', '20000817','20000825'
> Union
> Select'John', 'A30', '20000822','99991231'
> I want the result to Look like
> Emp_Name Category1 Category2 StartDate EndDAte
> John A10 20000801 20000803
> John A10 G20 20000803 20000807
> John A20 G20 20000807 20000815
> John A20 20000815 20000817
> John A20 G30 20000817 20000822
> John A30 G30 20000822 20000825
> John A30 20000825 20000831
>|||Cause that is the report that i need to produce.
for employees, showing what catefory they fall under dusing different time
span
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234L3keZSGHA.5728@.tk2msftngp13.phx.gbl...
> Jason
> What is the purpose? Can you explain why would you want this ouptut?
> Based on what?
>
> "Jason" <bornscorpio30@.yahoo.com> wrote in message
> news:eV341XZSGHA.3192@.TK2MSFTNGP09.phx.gbl...
>|||I think Uri is suggesting you give both more explanation and 'details' of
your output.Around here the more info you give the better off you are.
Guys like Uri are smart and skilled but the less you make him guess details
the more apt you are for him to figure out a solution.
"Jason" <bornscorpio30@.yahoo.com> wrote in message
news:%23od2tOeSGHA.4168@.tk2msftngp13.phx.gbl...
> Cause that is the report that i need to produce.
> for employees, showing what catefory they fall under dusing different time
> span
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234L3keZSGHA.5728@.tk2msftngp13.phx.gbl...
>|||Please post DDL and better specs. I am assuming that on any given
date, you have 1 or 2 (vague, unnamed) categories and that you know
what a Calendar table is.
CREATE TABLE Foobar
(emp_name CHAR(10) NOT NULL,
foo_cat CHAR(3) NOT NULL
CHECK (SUBSTRING (foo_cat,1,1) IN ('A', 'G')),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date),
PRIMARY KEY (emp_name, start_date));
Pick a date range (@.my_start_date, @.my_end_date) and use this query to
get the status on every date in that range.
SELECT F1.emp_name,
MIN(F.foo_cat) AS cat_1,
MAX(F.foo_cat) AS cat_2,
C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Foobar AS F,
ON C.cal_date BETWEEN F.start_date AND F.end_date
WHERE C,cal_date BETWEEN @.my_start_date AND @.my_end_date;
If you really need to see this in ranges instead of day by day, we can
do that but it is messy and slow.

Sunday, February 19, 2012

dateformat

Hi every body
I am using a stored procedure to insert values into a table which contain a date datatype. I pass the date in MM-DD-YYYY format.
When i execute the querry it shows error invalid month.

create or replace procedure sample_I
(
P_no varchar2,
P_date date
)
as
begin
insert into sample values(P_no ,to_date(P_date,'MM-DD-YYYY' ));
end;Since p_date already is a date, you should not apply the TO_DATE function to it - doing so is the cause of your problem. Code should be:
create or replace procedure sample_I
(
P_no varchar2,
P_date date
)
as
begin
insert into sample values(P_no ,P_date);
end;
BTW, since this question is Oracle-specific, it would have got answered quicker if you had posted it in the Oracle forum!

Friday, February 17, 2012

Datediff formula on insert returning null value

I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration.

Thank you in advnace for any help on this!

INSERTrequest(emp_id,request_submit_date, request_start_date,request_end_date,request_duration,request_notes,time_off_id)Select@.emp_id,GETDATE(),@.request_start_date,@.request_end_date, 1 +DATEDIFF(day, @.request_start_date, @.request_end_date) - (selectcount(*)from WeekEndsAndHolidayswhere DayOfWeekDatebetween @.request_start_dateand @.request_end_date),@.request_notes,@.time_off_id

Either request_start_date or request_end_date is coming across as null. Are you sure that request_duration is the only null field?|||

Motley,

Thanks for the response. It took a while to get posted and I figured it out way before it got posted. I had worked on it for about an hour before I posted this and figure it out 5 minutes after I posted it. I didn't have one of my text boxes bound correctly.

Thursday, March 22, 2012

DateTime.Now expression expected problem

DateTime.Min won't insert into SQL Server Mobile 3.0

datetime vs varchar

datetime vs varchar

Wednesday, March 21, 2012

DateTime string insert into sql datetime column fails

datetime problem

Monday, March 19, 2012

DateTime help

Sunday, March 11, 2012

DateTime format during INSERT using MS JDBC driver

Thursday, March 8, 2012

Datetime data type resulted in an out-of-range datetime value. Please help

Datetime data type ?

Datetime data type ?

datetime Data Type

Datetime conversion under diferent versions of SQL

Datetime conversion under diferent versions of SQL

Wednesday, March 7, 2012

Datetime changes when insert

Datetime calculation help

DateTime as one parameter

Saturday, February 25, 2012

Dates Help Needed

Sunday, February 19, 2012

dateformat

Friday, February 17, 2012

Datediff formula on insert returning null value

db error

Blog Archive

About Me