Re: Getting the correct date format from SQL-Server 2000
- From: "Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx>
- Date: Sun, 25 Sep 2005 01:38:48 -0700
"PB" <PB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:594B7957-7EF8-45B6-97F2-1E2B56262357@xxxxxxxxxxxxxxxx
>I have an ASP web page that causes an out of process dll to execute and
> connect to an SQL server database to retrieve records. No matter what I
> try,
> the date fields are always returned as dd/mm/yyyy.
>
> How do I get the vbasic dll program to return yyyy/mm/dd format dates from
> the SQL Server?
>
> I've tried
> 1) Executing the SET DATEFORMAT ymd SQL command after openning the
> connection
> 2) Setting the regional Date format on the userid the out of process dll
> executes under.
>
> What else is there. It works properly on one server but not another.
SET DATEFORMAT only affects interpretation of input. I was thinking
collation might affect it, but from a glance at the BOL it doesn't look like
it. You might try setting the regional format for the user context under
which SQL Server runs, if that's practical.
The most certain way to make sure dates are rendered as expected every time
is to derive a column using the CONVERT() built-in function:
SELECT CONVERT(varchar(10), [mydate], 120) As [mydate] FROM [mytable]
The magic number 120, and friends can be found in the BOL under the topic
"Cast and Convert". Limiting the width to 10 truncates the time component.
HtH,
Mark
> Running
> W2000 Server
> SQL Server 2000
> VB 6.0
.
- Prev by Date: Re: Permissions???
- Next by Date: Re: ActualSize returns -1 for Text fields
- Previous by thread: ActualSize returns -1 for Text fields
- Next by thread: Re: Retrieving CLOB field???
- Index(es):
Relevant Pages
|
|
