dump the time

Results 1 to 3 of 3

Thread: dump the time

  1. #1
    Join Date
    Dec 1969

    Default dump the time

    Hi, I am trying to change the format of the datetime to just date, that is...from 14/11/2001 17:09:57 to just 14/11/2001. Is it possible to write a statement in SQL 7 database to perform such action so I don&#039;t have to go and change all the records one bt one?<BR><BR>I Have tried something like :<BR> <BR>UPDATE tb_user<BR>SET DTECRTD = CONVERT(varchar(12), DTECRTD, 103)<BR>WHERE USERAGE &#062; 0<BR><BR>it didn&#039;t work. when i change the statement to alter individual entry, sometime it work sometime it doesn&#039;t. I have check all the enties, there&#039;s no &#039;null&#039; and all are in the same format..very confused.<BR><BR>Can anyone help?<BR><BR>thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: dump the time

    You should convert the date whenever you view the data. What you are trying to do is to insert varchar(12) datatype in a datetime column in your table which shouldn&#039;t work

  3. #3
    Join Date
    Dec 1969

    Default Fadi is right! And...

    Just dump the time when you *use* the field. <BR><BR>However...<BR><BR>If you are 100% certain that you&#039;ll never have a use for the time, then do:<BR><BR>UPDATE tb_user<BR>SET DTECRTD = CONVERT(DateTime, CONVERT(varchar, DTECRTD, 112)<BR>WHERE USERAGE &#062; 0<BR><BR>And then fix your data entry so it stops shoving date and time into that field. If it&#039;s a default value, then follow that same pattern for the default:<BR><BR>CONVERT(DateTime, CONVERT(varchar, GetDate(), 112)<BR><BR>I use 112 instead of 103 because it is ISO format (YYYYMMDD) and works in any Locale, etc. More reliable than using 103 (DD/MM/YYYY) and then finding that the conversion *back* to DateTime used 101 (MM/DD/YYYY) and all of a sudden 10 July 2002 became 7 October 2002.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts