Re: Converting and formatting DATE field

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 19:42:48 GMT

Hi

SQLServer does not have a date only datatype, therefore it will store a time
portion. If you don't specify the time portion when the datetime is inserted
it will be defaulted to 00:00:00.000. Without formatting the output you will
get a date and time. Try using:

SELECT CONVERT(char(10),col1,101) FROM MyTable

when you retrieve the data.

John

"Olivia" <anonymous@discussions.microsoft.com> wrote in message
news:336901c3fd66$8cba0a80$a501280a@phx.gbl...
> I need to load a string date data (from a text file) into
> a datetime type field.
>
> CONVERT(VARCHAR, COL1, 101) -- These one doesn't work
> because my destination field is a datatype and the load
> fails. No matter what style I use (100, 101, 102, etc)
>
> CONVERT(DATETIME, COL1, 101) -- This one, the load
> succeedes but no matter what style I use (100, 101, 102,
> etc) it always return the same date format:
> 2002-11-19 00:00:00.000
> I would like to be able to format this date and show only
> the date without time or uses mm/dd/yyyy or mm-dd-yyyy,
> etc.
>
> Thanks in advance for your help. Olivia
>

Relevant Pages

  • Re: Input Mask on Date for Year or Month Only
    ... If you wish to use the DateTime functions, then the field's datatype ... format, the stored value must contain the day as well. ... mm-yyyy format, i.e. 08/2008 or 08-2008. ...
    (microsoft.public.access.gettingstarted)
  • Re: Input Mask on Date for Year or Month Only
    ... Can I use an input mask to caputre only the year or only the month? ... If you wish to use the DateTime functions, then the field's datatype ... format, the stored value must contain the day as well. ...
    (microsoft.public.access.gettingstarted)
  • Re: simple way to convert 1900-01-01 17:12:00.000 to 5:12pm?
    ... datatype is datetime. ... i want to the convert the start_time column into a format that only ... has the hour and minutes plus AM/PM ...
    (comp.databases.ms-sqlserver)
  • Re: convert / group by date
    ... I have a datetime column named dtDateTime. ... FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId ... since the conversion doesn't chop off the time portion. ...
    (comp.databases.ms-sqlserver)
  • Bulk Insert - How to represent nulls in .txt input file?
    ... I have a table that I'm loading via .txt file input. ... One of the columns has a datatype of datetime. ... way to pass a null to a datetime field that allows nulls. ...
    (microsoft.public.sqlserver.dts)