Re: SQL query to format datetime

From: Steve Kass (skass_at_drew.edu)
Date: 01/27/05 

Date: Wed, 26 Jan 2005 23:22:01 -0500

Onnuri,

  The data is not stored with any particular format - the format
displayed when you retrieve the data may depend on the tool
you're using to query the database, on database settings, and
on regional settings of the OS.

  Depending on what you want, there are two answers.

If you simply want to display 1/25/2005 when retrieving the datetime
value of January 25, 2005, 11:37:25 PM, you can select
select convert(varchar(10),getdate(),101). This will put leading
zeroes in front of date parts less than 10, which hopefully is ok.

If you want to change the actual value, and store just the date,
but not the time, you will have to change the value in your
table. You can't really store just a date, but you can store
a datetime with a time part of midnight, and it will behave like
a date-only.

update dateColumn set
  dateColumn = dateadd(day,datediff(day,0,dateColumn),0)

You may also want to put a constraint on the date column
to prevent values with times other than midnight from being
entered, if this is really what you want, as opposed to just
a change in display format.

Steve Kass
Drew University

Onnuri wrote:

>Hi,
>Can I ask you a simple SQL query question? This datetime data type stores
>records like "1/25/2005 11:37:25 PM". Is there a SQL query to format it
>like "1/25/2005"? Please let me know.
>
>Onnuri
>
>
>
>

Relevant Pages

  • Re: Populating fields from a separate table
    ... You don't need store the existing data in the second table, ... To display the data create a query with Table two and table one as the ... set the join type to a left join on the 3 digit column. ...
    (comp.databases.ms-access)
  • Re: table formatting not carrying to calculated field in query
    ... Format in table design only controls display. ... But why does the query see the formatting in the field "State" ... And because the concantated field ...
    (microsoft.public.access.queries)
  • Re: Very Weird Date Format Problem
    ... Many, many, many thanks John. ... the date fields set format to d All but L1 display the day. ... The query uses a stored field which is the first of the month. ...
    (microsoft.public.access.reports)
  • Re: How to convert text to number in a query?
    ... CCurconverts the value into Currency type data. ... Queries are reallly just the engine, and not really a display interface. ... on your form/report has a Format property. ... The fields in a query do have a Format property too. ...
    (microsoft.public.access.queries)
  • Re: table formatting not carrying to calculated field in query
    ... Format in table design only controls display. ... But why does the query see the formatting in the field "State" ... And because the concantated field ...
    (microsoft.public.access.queries)
Quantcast