Re: Date variable - seconds always set to zero

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

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 14:38:17 +0200

Never ever implicitly convert datetime to a string. You are relying on someone (as MS) to decide for
you what format that string would have.

Also, Note that the format you used, '2004-06-17 08:32:18.437', is not safe if you have different
dateformat, login language or language setting. http://www.karaszi.com/SQLServer/info_datetime.asp 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raj Moloye" <rkmoloye@hotmail.com> wrote in message news:uQwpXeSVEHA.3944@tk2msftngp13.phx.gbl...
> Hello Bernie,
> I was perplexed to learn this was possible, so I did some tests of my own.
> See the script sample below:
>
> declare
>  @DateVarchar_Imp varchar(25)
> , @DateVarchar_Exp varchar(25)
> , @DateDatetime datetime
>
> -- Assign a datetime value to a datetime variable
> set @DateDatetime = '2004-06-17 08:32:18.437'
>
> -- Test 1: Implicitly convert to varchar
> set @DateVarchar_Imp = @DateDatetime
> -- Display the values
> select DateDatetime = @DateDatetime
>  , DateVarchar_Imp = @DateVarchar_Imp
>
>
> -- Test 2: Explicitly convert to varchar using the default format
> select DateVarchar_Exp100 = convert(varchar(25), @DateDatetime, 100)
>  , DateVarchar_Exp121 = convert(varchar(25), @DateDatetime, 121)
>
> Results of Test 1:
> DateDatetime                                           DateVarchar_Imp
> ------------------------------------------ ------------------------- 
> 2004-06-17 08:32:18.437                        Jun 17 2004  8:32AM
>
> Results of Test 2:
> DateVarchar_Exp100     DateVarchar_Exp121
> ------------------------- ------------------------- 
> Jun 17 2004  8:32AM     2004-06-17 08:32:18.437
>
> Note that DateVarchar_Imp from Test1 and DateVarchar_Exp100 from Test2 have
> the same value without the seconds and milliseconds.
> Unfortunately I cannot explain it or say if this is a bug in Sql Server or
> is the designed behaviour, but it seems the implicit conversion is using
> some default style(equivalent to style 0 or 100 from CONVERT) for the
> conversion.
> I am hoping someone else can comment on this.
>
> Regards,
> Raj Moloye
>
>

Relevant Pages

  • Re: dateTime Manupulations
    ... Notice the quoted string, a specific format for a date & time. ... will need to convert that string into a DateTime. ... > datetime conversion between strings, however it was a datetime conversion ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Axis <-> .Net xsd:date interoperability problem
    ... .NET can de-serialize from alternative string representations of the date, ... but you need to tell it the format to expect. ... DateTime to be set. ... serialization of all DateTime's. ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: convert a date to a string?
    ... converting it to a CHARACTER string. ... Also, don't convert it back to a datetime, or put it in a datetime variable, ... or you'll lose the format. ... >>> Declare @m int ...
    (microsoft.public.sqlserver.programming)
  • Re: Search an Outlook appointment
    ... Then I can make my search string using the strDate. ... And my problem is to convert datetime to a string: ... How can I get the right datetime format of the user for making the search? ... I need to search for Outlook appointements from date to date. ...
    (microsoft.public.win32.programmer.messaging)
  • RE: SqlCeEngine.CreateDataBase() - LCID - Datepart format problem
    ... Background info on why insert in your format doesn't work: ... datetime as string, SQL CE does an implicit convert from string to ... datetime formats with their style IDs are listed in CONVERT function in SQL ...
    (microsoft.public.sqlserver.ce)