如何从SQL数据库中以YYYY-MM-DD格式检索date类型日期？

Got it, let's sort out this date format headache for you. The issue here is that even though your database stores the value as a proper DATE type, your client or database driver is converting it into that verbose timestamp string with timezone info when you fetch it. Here are the most practical ways to get that clean YYYY-MM-DD format you need, depending on your database and where you want to handle the formatting:

This is the simplest approach if you only need the formatted date string in your results. Different databases use different functions for this:

• MySQL/MariaDB: Use the DATE_FORMAT() function to specify your desired format:

  SELECT DATE_FORMAT(your_date_column, '%Y-%m-%d') AS formatted_date
  FROM your_table;
  

  %Y gives a 4-digit year, %m a 2-digit month, and %d a 2-digit day—exactly the YYYY-MM-DD structure you want.

• PostgreSQL: Use the TO_CHAR() function to convert the date to a string in your target format:

  SELECT TO_CHAR(your_date_column, 'YYYY-MM-DD') AS formatted_date
  FROM your_table;
  

  The format string is straightforward here, matching exactly what you need to output.

• SQL Server: You have two options—CONVERT() (fast, good for large datasets) or FORMAT() (more readable, slightly slower):

  -- Using CONVERT with style code 23 (maps to YYYY-MM-DD)
  SELECT CONVERT(VARCHAR, your_date_column, 23) AS formatted_date
  FROM your_table;
  
  -- Using FORMAT for clearer syntax
  SELECT FORMAT(your_date_column, 'yyyy-MM-dd') AS formatted_date
  FROM your_table;
  

• SQLite: SQLite doesn’t have a native DATE type, but if you’re storing dates as valid date values (text, real, or integer), the DATE() function will return them as YYYY-MM-DD:

  SELECT DATE(your_date_column) AS formatted_date
  FROM your_table;
  

If you want to keep the original date object for other uses but display it in YYYY-MM-DD format, handle the formatting in your app. Here are examples for common languages:

• JavaScript: If your driver returns a Date object, you can extract the formatted string easily:

  const rawDate = new Date('Thu Feb 21 1985 00:00:00 GMT-0800 (PST)');
  // Quick and dirty method
  const formattedDate = rawDate.toISOString().split('T')[0];
  // Returns "1985-02-21"
  
  // Or use Intl for more localization control
  const formattedDate = new Intl.DateTimeFormat('en-CA', {
    year: 'numeric',
    month: '2-digit',
    day: '2-digit'
  }).format(rawDate);
  

• Python: If you’re working with datetime objects (most database libraries return these directly), use strftime():

  from datetime import datetime
  # If you're parsing the raw string (skip if your driver gives a datetime object)
  raw_date = datetime.strptime('Thu Feb 21 1985 00:00:00 GMT-0800 (PST)', '%a %b %d %Y %H:%M:%S GMT%z (%Z)')
  formatted_date = raw_date.strftime('%Y-%m-%d')
  # Outputs "1985-02-21"
  
  # If your driver returns a date object directly, just convert to string
  formatted_date = str(raw_date)
  

Some drivers let you configure the default date format so you don’t have to format manually. For example, in Node.js with the pg (PostgreSQL) driver, add dateStrings: true to your connection config—this will return dates as YYYY-MM-DD strings instead of Date objects right out of the box.

Pick the method that fits your workflow best: query-level formatting is great for one-off needs, while app-level formatting gives you flexibility, and driver settings can simplify repeated tasks.

内容的提问来源于stack exchange，提问作者jcadams285