如何在sqlserver中将dd / mm / yyyy格式的日期与mm / dd / yyyy进行比较 [英] how to compare date in dd/mm/yyyy format with mm/dd/yyyy in sqlserver

问题描述

大家好



i有一张桌子

<前lang =SQL> table_a
字段
id int
name varchar
日期 varchar

此处将日期保存在 dd / mm / yyyy format

i希望在两个日期之间从此表中获取数据

 选择 * 来自 table_a 其中（（日期 > =  @ StartingDate ）或（ @ StartingDate     null ））
 和（（日期< =  @ endDate ）或（ @ endDate     null ））

但是没有返回任何行，因为日期保存在 dd / mm / yyyy 中如何清除此问题


谢谢和问候

Amritha

解决方案

尝试转换 [ ^ ]：

  SELECT  * 
  FROM  table_a 
  WHERE   CONVERT （ DATETIME ，[ date ]， 112 ） BETWEEN   @ startDate   AND   @ endDate  

请，阅读：日期行值总和 [ ^ ]到知道糟糕的桌子设计会造成什么。

amritha444 - 17分钟前写道：

这里有一些列值

 24/5/2013 
 24/5/2013 
 2/7/2013 
 2/7/2013 
 25/6/2013 
 25/6/2013 
 6/6/2013 
 27/5/2013 

好​​的，尝试添加SET DATEFORMAT指令：

  SET   DATEFORMAT  dmy; 
 
  DECLARE   @ tmp   TABLE （dateval  VARCHAR （ 30 ））
 
  INSERT   INTO   @ tmp （dateval）
  SELECT  '  24/5/2013' 
  UNION   ALL   SELECT  < span class =code-string>'  24/5/2013' 
  UNION   ALL   SELECT  '  2/7/2013' 
  UNION   ALL   SELECT  '  2/7/2013' 
  UNION   ALL   SELECT  '  25/6/2013' 
  UNION   ALL   SELECT  '  25/6/2013' 
  UNION   ALL   SELECT  '  6/6/2013' 
 < span class =code-keyword> UNION   ALL   SELECT  '  27/5/2013' 
  UNION   ALL   SELECT  '  27/5/2013' 
 < span class =code-keyword> UNION   ALL   SELECT   NULL  
  UNION   ALL   SELECT  '  7/13/2113' 
  UNION   ALL   SELECT  '  12/12/2215' 
 
 
  SELECT  dateval， CONVERT （ INT ， LEFT （dateval，CHARINDEX（'  /'，dateval）-1）） AS  [ d]，
  CONVERT （ INT ，SUBSTRING（dateval，CHARINDEX（'  /'，dateval）+ 1，CHARINDEX（' < span class =code-string> /'，dateval，CHARINDEX（'  /'，dateval）+1） -  CHARINDEX（'  /'，dateval）-1）） AS  [m]，
  CONVERT （ INT ， RIGHT （dateval， 4 ）） AS  [y] 
  FROM   @ tmp  
  WHERE  CHARINDEX（'  /'，dateval）> 0 

结果：

 24/5/2013 24 5 2013 
 24/5/2013 24 5 2013 
 2/7/2013 2 7 2013 
 2 / 7/2013 2 7 2013 
 25/6/2013 25 6 2013 
 25/6/2013 25 6 2013 
 6/6/2013 6 6 2013 
 27/5 / 2013 27 5 2013 
 27/5/2013 27 5 2013 
 7/13/2113 7 13 2113 
 12/12/2215 12 12 2215 

[/ EDIT]

Hi all

i have a table

table_a
fields
id  int
name varchar
date varchar

here saving date in dd/mm/yyyy format
i want to fetch data from this table between two dates

select * from table_a  where ((Date >= @StartingDate ) or (@StartingDate is null)) 
and ((Date <= @endDate ) or (@endDate is null))

but didnt returned any row as date saved in dd/mm/yyyy fromat how to clear this issue

thanks and regards
Amritha

解决方案

Try to convert[^]:

SELECT *
FROM table_a
WHERE CONVERT(DATETIME, [date],112) BETWEEN @startDate AND @endDate

And please, read this: sum of row value date wise[^] to know what bad table design can cause.


[EDIT]

amritha444 - 17 mins ago wrote:

here is some of column values

24/5/2013
24/5/2013
2/7/2013
2/7/2013
25/6/2013
25/6/2013
6/6/2013
27/5/2013

OK, try add SET DATEFORMAT instruction:

SET DATEFORMAT dmy;
 
DECLARE @tmp TABLE (dateval VARCHAR(30))
 
INSERT INTO @tmp (dateval)
SELECT '24/5/2013'
UNION ALL SELECT '24/5/2013'
UNION ALL SELECT '2/7/2013'
UNION ALL SELECT '2/7/2013'
UNION ALL SELECT '25/6/2013'
UNION ALL SELECT '25/6/2013'
UNION ALL SELECT '6/6/2013'
UNION ALL SELECT '27/5/2013'
UNION ALL SELECT '27/5/2013'
UNION ALL SELECT NULL
UNION ALL SELECT '7/13/2113'
UNION ALL SELECT '12/12/2215'


SELECT dateval, CONVERT(INT, LEFT(dateval, CHARINDEX('/',dateval)-1)) AS [d],
		CONVERT(INT, SUBSTRING(dateval, CHARINDEX('/',dateval)+1,CHARINDEX('/',dateval, CHARINDEX('/',dateval)+1) - CHARINDEX('/',dateval)-1)) AS [m],
		CONVERT(INT, RIGHT(dateval,4)) AS [y]
FROM @tmp
WHERE CHARINDEX('/',dateval)>0

Result:

24/5/2013   24  5   2013
24/5/2013   24  5   2013
2/7/2013    2   7   2013
2/7/2013    2   7   2013
25/6/2013   25  6   2013
25/6/2013   25  6   2013
6/6/2013    6   6   2013
27/5/2013   27  5   2013
27/5/2013   27  5   2013
7/13/2113   7   13  2113
12/12/2215  12  12  2215

[/EDIT]

这篇关于如何在sqlserver中将dd / mm / yyyy格式的日期与mm / dd / yyyy进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！