INSERT/UPDATE Date Format problem...

Hi,
This is a problem that everybody knows I guess: When you INSERT our UPDATE a
date in an Sql Server half of the time your date changes. For example: you
want to input two dates: 13th of May (13/05/2003) and 12th of May
(12/05/2003). The first one will always be in the database as "13/05/2003"
because the database knows 13 can't hbe a month. But for the second one you
need to get lucky: there's always a big chance (depending on the regional
settings?) that he will put it in the database as "05/12/2003" and thinks it
is 5th of December instead of 12th of May.
I used to have this problem in VB6, and now again I have it in VB.NET. In
VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
dd/MM/yyyy.
But still I think this isn't a 'nice' way. There should be a way which is
independed of regional settigns etc, and doens't force you to use 'trics'.
Does anybody here know how to do this?
Thanks a lot in advance!
PieterAny of the following formats are "safe" - they work independently of the
server's regional settings
'20031231'
'2003-12-31T17:59:00'
'2003-12-31T17:59:00.000'
Example:
UPDATE Sometable
SET datecol = '20031231'
WHERE ...
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks! I will try this!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Ws-dnW0JbYJ2fUiiRVn-jg@.giganews.com...
> Any of the following formats are "safe" - they work independently of the
> server's regional settings
> '20031231'
> '2003-12-31T17:59:00'
> '2003-12-31T17:59:00.000'
> Example:
> UPDATE Sometable
> SET datecol = '20031231'
> WHERE ...
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||<%
'This function recieves a date from text string in format dd/mm/yy or
dd/mm/ccyy
'And creates a string that is compatible with inserting into sql as a
datetime field
'If an empty string is passed it just passes back trimmed original
'Write Value Test value to sql database 'datetime' field
'Added 16/04/2003
'If a 2 digit year is passed then 20 is prepended onto year to build a
CCYY year
'--
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'--
'pass a date as dd/mm/yy
Function convDate(theDate)
Dim Itemp
If TRIM(theDate) <> "" Then
sTemp = cdate(theDate)
dteArray = Split(sTemp,"/",-1,1)
If LenB(dteArray(2)) = 2 Then
dteArray(2) = "20" & dteArray(2)
End If
convDate =dteArray(2) & "/" & dteArray(1) & "/" & dteArray(0)
Else
convDate = Trim(theDate)
End If
End Function
%>
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:3fd5df92$0$289$ba620e4c@.reader5.news.skynet.be...
> Hi,
> This is a problem that everybody knows I guess: When you INSERT our UPDATE
a
> date in an Sql Server half of the time your date changes. For example: you
> want to input two dates: 13th of May (13/05/2003) and 12th of May
> (12/05/2003). The first one will always be in the database as "13/05/2003"
> because the database knows 13 can't hbe a month. But for the second one
you
> need to get lucky: there's always a big chance (depending on the regional
> settings?) that he will put it in the database as "05/12/2003" and thinks
it
> is 5th of December instead of 12th of May.
> I used to have this problem in VB6, and now again I have it in VB.NET. In
> VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
> dd/MM/yyyy.
> But still I think this isn't a 'nice' way. There should be a way which is
> independed of regional settigns etc, and doens't force you to use 'trics'.
> Does anybody here know how to do this?
> Thanks a lot in advance!
> Pieter
>

Insert Trigger

I guess the first question is "Can you have two columns in two seperate
table share an Identity Column?" If there is a way, then I'll do that.
The other solution that I have came up with is to have another table
that generates the IDs and then insert it into the record on Insert. I
need to know how to update a record contained in the Inserted table. I
tried doing it directly but I keep getting the error that states you
cannot alter the Inserted or Deleted tables. Any help would be
appreciated.Hi Toppar,
I guess the first question is "Can you have two columns in two seperate
table share an Identity Column?"
-No you can=B4t. There is no sequence in SQL Server like in Oracle.
YOu have to reference the table in your update statement using the
primary keys to join the original on the inserted one:
UPDATE SomeTable
SET SomeColumn =3D SomeValue
FROM SomeTable S
Inner Join INSERTED I
On S.JoinedColumns =3D s.JoinedColumns
--AND other joined columns
HTH, jens Suessmeyer.|||create table #t1(id int identity(1,2), j int)
insert into #t1(j)
select 1
union all
select 2
union all
select 3
select * from #t1
go
-- the idenitites wont collide
create table #t2(id int identity(0,2), j int)
insert into #t2(j)
select 1
union all
select 2
union all
select 3
-- the idenitites wont collide
select #t1.*, '#t1' from #t1
union all
select #t2.*, '#t2' from #t2
id j
-- -- --
1 1 #t1
3 2 #t1
5 3 #t1
0 1 #t2
2 2 #t2
4 3 #t2
(6 row(s) affected)|||Hi Jens,
I did finally get my plan to finally work after a lot of pain. I
pretty much had to do it your way but I had to add a default value so
that the unique constraint of the primary key was satisfied. I'm still
kind of new to row level locking in SQL Server, but if it works similar
to that in Oracle, I think that what I did should work. If not, I have
written code in the form to handle it and then retry if two users try
to insert at the same time. Once again, thank you for the help.
Jon...

insert row question

Hello all, my main question is i guess, is this the easiest way?
INSERT INTO table1 (c1, c2, c3...)
SELECT number1 AS c1, c2, c3...
FROM table1
WHERE c1 = number2
Just so there is no confusion: I am only working with one table here. I
basically want to copy a row where c1 = some_number, and paste on a new row
where c1 = different_number: Here is a crude example:
some_number, c2, c3, c4...
different_number, c2, c3, c4...
The reason I ask is beacuse the columns can sometimes extend way out there,
it is not very convenient to get the names and list them all out. I have
saved code to run a lot of the queries I need just as anyone would do, but
they are not always in hand.
I was looking for something real simple, but that may be asking to much.
Would a cursor, or using a temp table be better? Or just stick to way above?
I really hope this is all not to confusing, and thank you all very much.
Josh.Just so I understand, you have a table with some number of rows in it,
like so:
5, 10, 15
6, 11, 26
And you want to add an additional row for every row in the table where
the first column = some value (say 5 in this case), but you want to
change that value to be something else (7), eg.
7, 10, 15
So your results would be
5,10,15
6, 11, 26
7, 10, 15
Correct?
If so, your method is fine.
Stu|||Stu, that is close to what is happening. The colums are a variety of data
types, not all numbers. Also, I do not always want to add them in sequential
order. The first colum is an ID number that associates all rows with that
number together, I will use all numbers as an example since it is easiest:
7, 4, 2, 6, 5
7, 9, 6, 2, 6
3, 2, 5, 8, 9
6, 8, 3, 1, 7
4, 0, 5, 7, 3
4, 0, 5, 8, 2
4, 9, 4, 2, 3
Now lets say I want to take that very first row of data and make a copy to
add to the group starting with 4.
So after running the query end up with something like this:
7, 4, 2, 6, 5
7, 9, 6, 2, 6
3, 2, 5, 8, 9
6, 8, 3, 1, 7
4, 0, 5, 7, 3
4, 0, 5, 8, 2
4, 9, 4, 2, 3
4, 4, 2, 6, 5
I know that is not really far from what you had wrote but I just thought I
would clarify. Do you still think the best way is with what I am doing now?
Thank you, Josh
Stu wrote:
>Just so I understand, you have a table with some number of rows in it,
>like so:
>5, 10, 15
>6, 11, 26
>And you want to add an additional row for every row in the table where
>the first column = some value (say 5 in this case), but you want to
>change that value to be something else (7), eg.
>7, 10, 15
>So your results would be
>5,10,15
>6, 11, 26
>7, 10, 15
>Correct?
>If so, your method is fine.
>Stu|||Sorry, my example was a poor fit; yes, you should be OK.
Stu