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 stored procedure

i want to insert values into a table select one field value from other table
(want to write select statement into insert statement but not knows how to
do it)
how can i implement it
the stored procedure is as given below
ALTER PROCEDURE dbo.usp_Insert
(
@.AccountName [NVARCHAR] (50)
, @.AccountSite [NVARCHAR] (50)
, @.LeadName [NVARCHAR] (50)
)
AS
BEGIN
SELECT Lead_ID into @.Lead_ID from Lead where Leadname=@.Leadname
INSERT INTO [Account]
(
[AccountName]
, [AccountSite]
, [Lead_ID]
)
VALUES
(
@.AccountName
, @.AccountSite
, @.Lead_ID
)
RETURN (@.@.IDENTITY)
END
ALTER PROCEDURE dbo.usp_Insert
(
@.AccountName [NVARCHAR] (50)
, @.AccountSite [NVARCHAR] (50)
, @.LeadName [NVARCHAR] (50)
)
AS
BEGIN
INSERT INTO [Account] ([AccountName], [AccountSite], [Lead_ID])
SELECT @.AccountName, @.AccountSite, b.Lead_ID
FROM [Lead]
WHERE [LeadNane] = @.Leadname
RETURN SCOPE_IDENTITY()
END
Andrew J. Kelly SQL MVP
"news.microsoftnews" <sapk81@.yahoo.com> wrote in message
news:erADAHSrEHA.332@.TK2MSFTNGP14.phx.gbl...
> i want to insert values into a table select one field value from other
table
> (want to write select statement into insert statement but not knows how to
> do it)
> how can i implement it
> the stored procedure is as given below
> ALTER PROCEDURE dbo.usp_Insert
> (
> @.AccountName [NVARCHAR] (50)
> , @.AccountSite [NVARCHAR] (50)
> , @.LeadName [NVARCHAR] (50)
>
> )
>
> AS
> BEGIN
> SELECT Lead_ID into @.Lead_ID from Lead where Leadname=@.Leadname
> INSERT INTO [Account]
> (
> [AccountName]
> , [AccountSite]
> , [Lead_ID]
> )
> VALUES
> (
> @.AccountName
> , @.AccountSite
> , @.Lead_ID
> )
> RETURN (@.@.IDENTITY)
> END
>