Re: Getting the correct date format from SQL-Server 2000

Tech-Archive recommends: Fix windows errors by optimizing your registry




"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


.



Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • RE: exec sp_help_job user account rights
    ... wrapper in which u can call original stored procedure sp_help_job using “WITH ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: SSIS - OLE DB Command - how to retrieve query results ???
    ... my side according to your process, and I managed to execute the task. ... Data Destination: <My SQL Server 2005 Instance>.TestDB ... OLE DB Command: ...
    (microsoft.public.sqlserver.dts)
  • RE: xp_cmdshell, ownership chaining, sql 2000
    ... Cross database ownership chaining enabled ... The procedure is called by a crystal report. ... If I log in to SQL Server through SSMS 2005 using the same user as the ... I get the following error when attempting to execute ...
    (microsoft.public.sqlserver.programming)
  • Re: 3 Simple Security SQL Statements
    ... Kalen Delaney, SQL Server MVP ... window and execute. ... Builds a list of text commands. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)