Convert Datetime field to display date

Question

I have a field that I would like to only display the date as mm/dd/yyyy.  Current field shows mm/dd/yyyy hh:mm:ss AM.

Answer 1

Something like that:

declare @date datetime
set @date=CONVERT(varchar(8), GETDATE(), 112) -- you may need refer to BOL to find proper format for convert function

Answer 2

To 'cut off' the time part from a datetime, you can convert it into a string that is shorter than the total length of display including time, and at the same time you can also define the format you want your date to be displayed.

select convert(char(10), getdate(), 101)

----------
08/09/2006

(1 row(s) affected)

...is probably what you're after. Just replace getdate() with the name of your datetime column.

It may also be worth mentioning that the actual value stored in a datetime column doesn't resemble a date at all. It's not stored as mm/dd/yyyy hh:mm:ss AM or yyyy-mm-dd or anything like that - these are all display formats. That is why, when you want to control how a date is displayed, you use CONVERT along with the proper STYLE (101 in above example) to format dates to your liking. This format however, does not in any way affect how the datevalue is stored in the table.

For a more comprehensive list of the different styles available, look up CONVERT in BOL.

Hope it helps some
=;o)
/Kenneth

Answer 3

It really depends on how you want it to be displayed.  In my case, I usually do a CONVERT to a char format so I can manipulate the string to whatever format I want displayed.

Answer 4

I have a date/time like this: 2011-Feb-10 10:00:00 AM in a table that I want to convert to just this: 2/10/2011 in a query so that I can put in a between criteria for an input of beginning and ending dates. How do I do this with a Function in the Expression Builder? I don't see a "Convert" function and can't figure out if CVar or CVDate work, if this is what I'm supposed to use.

Thanks,

Brett

Answer 5

Not pretty, but works:

 

declare @displaydate varchar(30);
set @displaydate = '2011-Feb-10 10:00:00 AM';

select CONVERT(VARCHAR(20), CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@displaydate, 
		'Jan', '01'),
		'Feb', '02'),
		'Mar', '03'),
		'Apr', '04'),
		'May', '05'),
		'Jun', '06'),
		'Jul', '07'),
		'Aug', '08'),
		'Sep', '09'),
		'Oct', '10'),
		'Nov', '11'),
		'Dec', '12') AS DATETIME), 101)

 If you are looking for an actual datetime type you may need to CAST the expression one more time.

---

MG.-
Mariano Gomez, MIS, MCP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 6

Try

declare @displaydate varchar(30);
set @displaydate = '2011-Feb-10 10:00:00 AM';
select SUBSTRING(convert(varchar(10),dt,101), case when month(dt) < 10 then 2 else 1 end,
10) as [Date]

from (select CONVERT(DATETIME, @DisplayDate) as dt) X

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Although if your date time field is datetime type, then you don't need to do any conversion, you need to use ISO date format for dates to compare it with, e.g. '20110210'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

But of course! Nice catch and nice solution!

---

MG.-
Mariano Gomez, MIS, MCP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 9

The following page also deals with datetime conversions and date & time functions:

http://www.sqlusa.com/bestpractices/datetimeconversion/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012