Hi all

I'm trying to get 3 single integer values into a datetime field on my sql
server. I'm using DTS ActivX Script Transformation and Visual Basic.

When I test the Script i get the correct datetime format. But when i then
go to the table on the sql server my date is sometimes in the wrong format...

I use the following code:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
Dim tt
Dim mm
Dim jjjj
Dim date

if DTSSource("PENDD") = "0" OR DTSSource("PENMM") = "0" OR
DTSSource("PENYYYY") = "0" then
DTSDestination("PENDAT") = null
else
dd = CStr(DTSSource("PENDD"))
mm = CStr(DTSSource("PENMM"))
yyyy = CStr(DTSSource("PENYYYY"))
date = mm + "/" + dd + "/" + yyyy
DTSDestination("PENDAT") = date
end if
Main = DTSTransformStat_OK
End Function

When i Test my Code with the following values:

DD =4
MM =5
YYYY = 2000

Format: mm/dd/yyyy
Returns : 5/4/2000

But in the sql Server it is shown as 5.4.2000 . Cause I'm in a german
language environment the Format should be dd.mm.yyyy ...but its like
mm.dd.yyyy in this case.

If I have values like:

DD = 20
MM = 5
YYYY = 2000

Returns 5/20/2000

And also in My SQL Server the Values are correct with 20.5.2000 ...
what am I doing wrong?? Is this an error in sql server? how can i do it right?

thanks for your help
Michael

Dates with separators are tricky because they are interpreted differently
depending on the settings of your SQL Server. Instead of creating a string
from the dateparts, and then saving that in a datetime column, you can
create a date in one go from the dateparts with the VBScript function
DateSerial. You only need to change one line in your code:

date = DateSerial( yyyy , mm , dd )

--
Jacco Schalkwijk
SQL Server MVP


"m.ahrens" <mahrens (AT) discussions (DOT) microsoft.com> wrote

A couple of points when handling dates. The format you see is governed by
the user settings, so make sure you know what these are and that they match
what you are expecting. A date in SQL is not stored in any format, but as an
internal number. When you ask the client to display the date it will format
it as per the client or user settings.

When you do the code "date = mm + "/" + dd + "/" + yyyy", you are building a
string, which will get converted to a date when it is inserted to SQL, based
on the user settings (assuming PENDAT is a datetime or smalldatetime
column). If you then ask SQL for it back to check your value, you are still
at the mercy of the user settings for the display format.

When returning a date from SQL try using a convert to ensure you get a known
format, e.g. SELECT CONVERT(char(8), PANDAT, 112) FROM table
which will always return yyyymmdd.

When inserting a date through ActiveX Script you are at the mercy of user
settings, and also the rather lax VBScript date handling. For example the
following will works-

Dim dDate
dDate = CDate("30/01/2004")
MsgBox Month(dDate)
dDate = CDate("01/30/2004")
MsgBox Month(dDate)

VBScript will force the best fit of a date, with the user settings
determining the default. This is dangerous as you really don't know what
will happen, and no error is raised if you try using the wrong format.
Personally if using dates as strings in VBScript I always use a unambiguous
format, and they only one I have found so far is one that has textual month
e.g. dd mmm yyyy, 30 Jan 2004.


--
Darren Green
http://www.sqldts.com

"m.ahrens" <mahrens (AT) discussions (DOT) microsoft.com> wrote

thanks! it works perfect!!


"Jacco Schalkwijk" wrote: