Convert a SQL Server datetime to a shorter date format

Question

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

User · Answer

With SQL Server 2005  I would use this   select replace convert char 10  getdate   102                Results  2015 03 05

User · Answer

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

User · Answer

If you need the result in a date format you can use   Select Convert DateTime  Convert VarChar  GetDate    101

User · Answer

Just add date keyword  E g  select date orderdate  count 1  from orders where orderdate    2014-10-01  group by date orderdate    orderdate is in date time   This query will show the orders for that date rather than datetime    Date keyword applied on a datetime column will change it to short date

User · Answer

In addition to CAST and CONVERT  if you are using Sql Server 2008  you can convert to a date type  or use that type to start with   and then optionally convert again to a varchar   declare  myDate date set  myDate   getdate   print cast  myDate as varchar 10     output   2012-01-17

User · Answer

The shortest date format of mm dd yy can be obtained with   Select Convert varchar 8  getdate   1

User · Answer

Try this   print cast getdate   as date

User · Answer

For any versions of SQL Server  dateadd dd  datediff dd  0  getdate     0

User · Answer

If you have a datetime field that gives the results like this 2018-03-30 08 43 28 177  Proposed    and you want to change the datetime to date to appear like 2018-03-30    cast YourDateField as Date

User · Answer

The original DateTime field     Date Time   The converted to Shortdate     Short Date   CONVERT date    Date Time   AS  Short Date

[sql-server] Convert a SQL Server datetime to a shorter date format

Examples related to sql-server

Examples related to tsql

Examples related to datetime