Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert a SQL Server datetime to a shorter date format

I have a datetime column in SQL Server that gives me data like this 10/27/2010 12:57:49 pm and I want to query this column but just have SQL Server return the day month and year - eg. 2010 10 27 or something like that.

What are the functions I should be researching?

Should I be trying to convert to another date data type? Or simply convert it to a string?

like image 1000
kaes Avatar asked Jan 17 '12 20:01

kaes


People also ask

How do you make a date smaller in SQL?

The SMALLDATETIME data type specifies a date and time of day in SQL Server. SMALLDATETIME supports dates from 1900-01-01 through 2079-06-06. The default value is 1900-01-01 00:00:00.

How do you convert a date to a short date?

Click Home tab, then click the drop-down menu in Number Format Tools. Step 2. Select Short Date from the drop-down list. The date is instantly displayed in short date format m/d/yyyy.

How do I convert DateTime to date format?

The following formula will help you converting date/time format cell to date only in Excel. 1. Select a blank cell you will place the date value, then enter formula =MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2) into the formula bar and press the Enter key.


2 Answers

Have a look at CONVERT. The 3rd parameter is the date time style you want to convert to.

e.g.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format 
like image 71
AdaTheDev Avatar answered Oct 05 '22 07:10

AdaTheDev


Try this:

print cast(getdate() as date )

like image 41
jabu.hlong Avatar answered Oct 05 '22 07:10

jabu.hlong