L'obtention de la "date" d'un champ DateTime dans SQL Server

il est appelé "le parquet datetime", de faire comme ceci pour supprimer juste le temps (c'est la méthode la plus rapide, plus rapide que d'utiliser la fonction CONVERT() ou CAST() piqûre de mise en forme):

DECLARE @datetime datetime;
SET @datetime = '2008-09-17 12:56:53.430';
SELECT DATEADD(day,DATEDIFF(day,0,@datetime),0)

De SORTIE:

-----------------------
2008-09-17 00:00:00.000

(1 row(s) affected)

ici est de savoir comment le faire pour les autres parties d'un datetime:

--Floor a datetime
DECLARE @datetime datetime;
SET @datetime = '2008-09-17 12:56:53.430';

SELECT '0 None',  @datetime                                                                   -- none    2008-09-17 12:56:53.430
UNION SELECT '1 Second',DATEADD(second,DATEDIFF(second,'2000-01-01',@datetime),'2000-01-01')  -- Second: 2008-09-17 12:56:53.000
UNION SELECT '2 Minute',DATEADD(minute,DATEDIFF(minute,0,@datetime),0)                        -- Minute: 2008-09-17 12:56:00.000
UNION SELECT '3 Hour',  DATEADD(hour,DATEDIFF(hour,0,@datetime),0)                            -- Hour:   2008-09-17 12:00:00.000
UNION SELECT '4 Day',   DATEADD(day,DATEDIFF(day,0,@datetime),0)                              -- Day:    2008-09-17 00:00:00.000
UNION SELECT '5 Month', DATEADD(month,DATEDIFF(month,0,@datetime),0)                          -- Month:  2008-09-01 00:00:00.000
UNION SELECT '6 Year',  DATEADD(year,DATEDIFF(year,0,@datetime),0)                            -- Year:   2008-01-01 00:00:00.000
ORDER BY 1
PRINT' '
PRINT 'Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor'
PRINT 'this always uses a date less than the given date, so there will be no arithmetic overflow'
SELECT '1 Second',DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,@datetime),0)-1,@datetime),DATEADD(day,DATEDIFF(day,0,@datetime),0)-1)  -- Second: 2008-09-17 12:56:53.000

De SORTIE:

-------- -----------------------
0 None   2008-09-17 12:56:53.430
1 Second 2008-09-17 12:56:53.000
2 Minute 2008-09-17 12:56:00.000
3 Hour   2008-09-17 12:00:00.000
4 Day    2008-09-17 00:00:00.000
5 Month  2008-09-01 00:00:00.000
6 Year   2008-01-01 00:00:00.000

(7 row(s) affected)


Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor
this always uses a date less than the given date, so there will be no arithmetic overflow

-------- -----------------------
1 Second 2008-09-17 12:56:53.000

(1 row(s) affected)

OriginalL'auteur KM.

Ici:

SELECT creation_date 
FROM risks
WHERE creation_date = GETDATE()

Ce sera le retour de tous les creation_date valeurs stockées dans la risks table qui sont exactement les mêmes que ce qui est retourné par la GETDATE() fonction. Je suis en supposant que le type de données de creation_date est Date.

OriginalL'auteur Oded

Vous pouvez toujours utiliser le format mois/jour/année de fonctions à retourner:

declare @date datetime
set @date = '1/1/10 12:00 PM'
select cast(month(@date) as varchar) + '/' + cast(day(@date) as varchar) + '/' + cast(year(@date) as varchar) as theDate

OriginalL'auteur Thyamine