2

How would you convert a date in the format of:

2012-10-16 07:44:22.000

to this format:

2012-10-16

And no just making the varchar(10) wont work as this question suggests, because I have to compare them and I can't do it with that method. Also nowhere does it give me the value to do that type of conversion.

This is as close as I've come:

declare @DATE DATETIME = '2012-10-16 00:00:000'
SELECT  Convert(varchar(20), @DATE,105)

Result = 16-10-2012

Community
  • 1
  • 1
Ruan
  • 3,969
  • 10
  • 60
  • 87
  • What do you mean which SQL is it? REAALY? -1? REALY? What did i do wrong? – Ruan Nov 28 '12 at 09:49
  • 1
    You spelled "really" wrong. ;) – fancyPants Nov 28 '12 at 09:51
  • 1
    Just FYI: SQL is a *language*. Many products use it. And @wallyk meant to enquire about the specific product you are using, which turns out to be SQL Server. – Andriy M Nov 28 '12 at 10:33
  • Thank you. Didn't know there was that big of a difference between the SQL languages (syntax wise) – Ruan Nov 28 '12 at 10:37
  • Yes, many things are vendor specific. So always including your database type and version in the tags, will yield faster more accurate answers :) – Leigh Nov 29 '12 at 04:01
  • *because i have to compare them.* Compare them how? Date comparisons should be done with date/time objects, not strings. – Leigh Nov 29 '12 at 04:05
  • Thanks, will defiantly keep that in mind for my next post. So not Even when you just want to see the results of a certain day? '2012-11-39' = '2012-11-39' – Ruan Nov 29 '12 at 05:15
  • @Ruan - No. As long as the target column is a date/time you should use a date/time object. You can truncate the time, just cast it back to a date/time object before the comparison. – Leigh Nov 29 '12 at 15:48
  • Thank you, Will remember that – Ruan Nov 30 '12 at 07:12

3 Answers3

5
SELECT CONVERT(VARCHAR(10),@date, 120)
--Gives: yyyy-mm-dd
Kaf
  • 33,101
  • 7
  • 58
  • 78
  • no actually that wont help me.. Varchar(10) wont work for me. (see question) – Ruan Nov 28 '12 at 10:02
  • 2
    Check out this [SQL Fiddle](http://sqlfiddle.com/#!3/d41d8/6767). This works on sql server. Not sure why you say it isn't – Kaf Nov 28 '12 at 10:09
  • Ah yea you are right, it did work, my comparison was just out. Thank you – Ruan Nov 28 '12 at 10:17
3
declare @DATE DATETIME = '2012-10-16 00:00:000'
SELECT  REPLACE(Convert(varchar(20), @DATE,102), '.', '-')

Here's an overview of the date formats in SQL Server.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
John Woo
  • 258,903
  • 69
  • 498
  • 492
0

mysql you can use select CURDATE();

it returns the current date (yyyy-mm-dd)

Mateus Nogueira
  • 139
  • 6
  • Curdate() returns the current date, its fine but it doesn't answer the question. Try to `convert` the date to the desired format – boyukbas Aug 22 '22 at 20:33