在SQL Server和MS Access上格式化（）DateTime到Date的CAST

问题描述

我有一个可以使用SQL Server或MS Access作为数据存储的应用程序。

在一个表中，我有一个DATETIME列。我想将值作为DATE值检索（时间部分被删除）。

我可以在SQLServer中使用CAST（）和MS Access中格式（）。理想情况下，我想要一个单一的SQL查询可以应用于任一数据库，而不是向每个数据库发送略有不同的查询。有人知道这样做吗？

解决方案

使用DATEADD / DATEDIFF

Sql Server

  SELECT DATEADD（d，DATEDIFF（d，0，getdate（）），0 ）as someDateOnly 
  

MS Access

  SELECT DATEADD（d，DATEDIFF（d，0，Now（）），0）AS someDateOnly; 
  

只需使用字段名称代替 GetDate（）和 Now（）

例如

  SELECT DATEADD（d，DATEDIFF（d，0，[somefield]），0）AS someDateOnly; 
  

作为旁边DateAdd / DateDiff是你想去的方式，如果你想剥离时间无论如何，2008年之前的版本。

I have an application that can use either SQL Server or MS Access as the data store.

In one table, I have a DATETIME column. I would like to retrieve the value as a DATE value (with the time part stripped off).

I can do that in SQLServer with CAST() and in MS Access with Format(). Ideally, I would like a single SQL query that could be applied against either database instead of sending a slightly different query to each database. Does anyone know a trick to do this?

解决方案

Use DATEADD/DATEDIFF

Sql Server

SELECT DATEADD("d",  DATEDIFF("d", 0, getdate()), 0) as someDateOnly

MS Access

SELECT DATEADD("d",  DATEDIFF("d", 0, Now()),0) AS someDateOnly;

Just use a field name in place of GetDate() and Now()

E.g.

 SELECT DATEADD("d",  DATEDIFF("d", 0, [somefield]),0) AS someDateOnly;

as an aside DateAdd/DateDiff is the way you want to go if you want to strip the time in versions prior to 2008 anyway.

这篇关于在SQL Server和MS Access上格式化（）DateTime到Date的CAST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！