Re: Undocumented CONVERT styles

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 04/29/04 

Date: Wed, 28 Apr 2004 20:10:20 -0400

Vlad,

  The string value {fn current_date()} will not be interpreted
independently of the date format:

declare @d datetime
set language french
set @d = {fn current_date()}
select @d

Steve Kass
Drew University

Vlad Vissoultchev wrote:

>did you try it? it's invariant to DATEFORMAT setting here. also, style 24
>seems to return time portion only -- probably used to implement { fn
>CURRENT_TIME() }
>
>just wondering why didn't they use styles 112 and 114 instead?
>
>cheers,
></wqw>
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
>message news:udyZGNSLEHA.1388@TK2MSFTNGP09.phx.gbl...
>
>
>>Vlad,
>>
>>I can't commend on the undocumented style. The ODBC functions doesn't feel
>>
>>
>"native" to me. They were
>
>
>>originally introduced so that the ODBC driver could trap the text and
>>
>>
>convert it to something that the target
>
>
>>DBMS could understand. With time, SQL Server started to understand some of
>>
>>
>these natively, and a profiler
>
>
>>trace of below indicates that SQL2K understands this natively:
>>SELECT { fn CURRENT_DATE() }
>>(It doesn't get changed to something else.)
>>
>>For some reason, SQL Server changes this to something else when you add it
>>
>>
>in a constraint (like SQL Server
>
>
>>changes IN to a number of OR's, for some strange reason).
>>
>>What I want to comment on is that 23 is not a "safe" format. It produces a
>>
>>
>string in the format of
>
>
>>'1998-03-23', which is dependent on SET DATEFORMAT.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>
>>"Vlad Vissoultchev" <wqweto@nospam.myrealbox.com> wrote in message
>>news:%232cZY6QLEHA.3548@TK2MSFTNGP10.phx.gbl...
>>
>>
>>>can anyone comment style param produced by this:
>>>
>>>IF OBJECT_ID('MyTable') IS NOT NULL DROP TABLE MyTable
>>>GO
>>>
>>>CREATE TABLE MyTable (
>>> CD DATETIME NOT NULL DEFAULT { fn CURRENT_DATE() }
>>> )
>>>GO
>>>
>>>SELECT COLUMN_NAME
>>> , COLUMN_DEFAULT
>>>FROM INFORMATION_SCHEMA.COLUMNS
>>>WHERE TABLE_NAME = 'MyTable'
>>>GO
>>>
>>>i'm just curious if someone bothered to document these
>>>
>>>TIA,
>>></wqw>
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>

Relevant Pages

  • Re: Moving a VBA function to SQL server
    ... "String Functions " in Books Online. ... The SQL server database contains a table named 'WorkOrders'. ... This will produce a job header formatted like this: ... The format he specifies is stored in table 'Settings' - field 'JobHdrMask' ...
    (comp.databases.ms-access)
  • Re: The third time to ask in here ... Mobile Web form
    ... You would be better directing sql server questions to the ... > i can't change its format, ... > format in the DB to string or the string that the user ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Format a datetime columns output?
    ... you could convert the date to a string? ... SQL Server you should use the format YYYYMMDD, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: can anyone tell me how to use DTS to convert a ISO date time to JAVA long type?
    ... > There is a column which type is ISO DATE type in my source DB, ... "format", but take the input and store it in an internal format, such as ... If you are working with dates as strings, or date to string then you can ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: mm/dd/yyyy format question
    ... I was not talking about how .Net or SQL Server stores date/time values ... I get the impression that you are talking about ISO 8601 in terms ... applications use the format for unambiguously representing date/time ...
    (microsoft.public.dotnet.languages.vb)