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
>