Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Post by Tom Furnes » Sat, 12 May 2001 02:03:36



Just store the date with time in the table and create the date w/o time when
you need it.


I would like to convert mm/dd/yyyy hh:mm:ss AM/PM format into mm/dd/yyyy
format
within the same table.  So I can have 'date w/ time' field and 'date w/o
time'
field in the same table.  Please help.  Thanks.

 
 
 

Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Post by Sara » Sat, 12 May 2001 02:09:44


Tae,

select CONVERT(char(12), getdate(), 112)

or

select CONVERT(char(12), getdate(), 101)

I'd recommend code 112 instead of 101, as 101 fails if you, for instance
have set the
language to deutsch of french first

Below fails:
SET LANGUAGE deutsch
SELECT CAST(CONVERT(char(10), getdate(), 101) AS datetime)

hth
Sara.

 
 
 

Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Post by Umachandar Jayachandra » Sat, 12 May 2001 02:10:57


    Datetime/Smalldatetime data types in SQL Server will always store both
the components. You have to store them separately in character or int format
depending on your requirements. If you are on SQL70/2000, then define
computed columns for each part like:

CREATE TABLE tst (
    DateTimeCol datetime,
    DateCol AS CONVERT( varchar , DateTimeCol , 112 ),
    TimeCol AS CONVERT( varchar , DateTimeCol , 108 )
)

    If you are on SQL6x, define a view on the table that computes these
values.
    Again if you need to search on these columns on a regular basis, then
persist them directly in SQL6x/70 or create index on the computed column in
SQL2000.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Post by Tae Le » Sat, 12 May 2001 03:22:50


What if I have existing data that I want to convert.  I am sure your code will
work on new data.  If you have more suggestions, let me know.

-Tae Lee

Quote:-----Original Message-----
Tae,

select CONVERT(char(12), getdate(), 112)

or

select CONVERT(char(12), getdate(), 101)

I'd recommend code 112 instead of 101, as 101 fails if you, for instance
have set the
language to deutsch of french first

Below fails:
SET LANGUAGE deutsch
SELECT CAST(CONVERT(char(10), getdate(), 101) AS datetime)

hth
Sara.

.

 
 
 

Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Post by Aaron Bertrand [MVP » Sat, 12 May 2001 04:08:46


Do the convert when you're retrieving the data.  Don't try and store the
data that way (it would require char datatypes, which aren't going to order
or index the way you want).  %datetime datatypes store both the date and
time components.  Using the code that Sara provided, it is trivial to select
the data as you want it for the presentation layer.

www.aspfaq.com


I would like to convert mm/dd/yyyy hh:mm:ss AM/PM format into mm/dd/yyyy
format
within the same table.  So I can have 'date w/ time' field and 'date w/o
time'
field in the same table.  Please help.  Thanks.