Вопрос по database, sql-server – Сравните строку даты с датой и временем в SQL Server?

39

Error: User Rate Limit ExceededDATETIMEError: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

'14 AUG 2008 14:23:019'

Error: User Rate Limit ExceededbestError: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

DECLARE  @p_date DATETIME
SET      @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT *
FROM   table1
WHERE  column_datetime = @p_date

Note: Given this site is also about jotting down notes and techniques you pick up and then forget, I'm going to post my own answer to this question as DATETIME stuff in MSSQL is probably the topic I lookup most in SQLBOL.

UpdateError: User Rate Limit Exceeded

EditError: User Rate Limit Exceeded

Error: User Rate Limit ExceededWHERE (date>'20080813' AND date<'20080815')Error: User Rate Limit Exceeded

Error: User Rate Limit ExceededClose, but no cigar!Error: User Rate Limit Exceeded

Ваш Ответ

18   ответов
4

Error: User Rate Limit Exceeded

SELECT  *
FROM    table1
WHERE   convert(varchar, column_datetime, 111) = '2008/08/14'
1

Error: User Rate Limit Exceeded

SELECT *
FROM (SELECT *, DATEPART(yy, column_dateTime) as Year, 
      DATEPART(mm, column_dateTime) as Month, 
      DATEPART(dd, column_dateTime) as Day 
      FROM table1)
WHERE Year = '2008'
AND Month = '8'
AND Day = '14'
-1

Error: User Rate Limit Exceeded

DECLARE @p_date DATE

SELECT * 
FROM table1
WHERE [email protected]_date

Error: User Rate Limit Exceeded

0

SELECT * 
FROM table1
WHERE DATE(column_datetime) = @p_date;
0

SELECT CONVERT(VARCHAR(2),DATEPART("dd",doj)) + 
    '/' + CONVERT(VARCHAR(2),DATEPART("mm",doj)) + 
    '/' + CONVERT(VARCHAR(4),DATEPART("yy",doj)) FROM emp
0

Error: User Rate Limit ExceededError: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

SELECT * FROM tablename where CONVERT(datetime,columnname,103) 
    between '2016-03-01' and '2016-03-03'
In CONVERT(DATETIME,COLUMNNAME,103) "103" SPECIFIES THE DATE FORMAT as dd/mm/yyyy
4

Error: User Rate Limit Exceeded

Error: User Rate Limit ExceededDATEError: User Rate Limit ExceededDATE ranges oftenError: User Rate Limit ExceededDATEError: User Rate Limit Exceeded

ALTER TABLE "table1" 
    ADD "column_date" AS CONVERT(DATE, "column_datetime") PERSISTED

Error: User Rate Limit Exceeded

CREATE NONCLUSTERED INDEX "table1_column_date_nu_nci"
ON  "table1" ( "column_date" ASC )
GO

Error: User Rate Limit Exceeded

DECLARE  @p_date DATE
SET      @p_date = CONVERT( DATE, '14 AUG 2008', 106 )

SELECT   *
FROM     table1
WHERE    column_date = @p_date
Error: User Rate Limit ExceededYYYY-MM-DD
3

How to get the DATE portion of a DATETIME field in MS SQL Server:

Error: User Rate Limit Exceeded

DATEADD(dd, DATEDIFF( dd, 0, @DAY ), 0)

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Get the date of the first day of the month

DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0)

Get the date rfom 1 year ago

DATEADD(m,-12,DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0))
1

SELECT  *
FROM    table1
WHERE   CONVERT(varchar(10),columnDatetime,121) = 
        CONVERT(varchar(10),CONVERT('14 AUG 2008' ,smalldatetime),121)

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

16

Error: User Rate Limit Exceeded

DECLARE @pDate DATE='2008-08-14'  

SELECT colA, colB
FROM table1
WHERE convert(date, colDateTime) = @pDate  

Error: User Rate Limit ExceededError: User Rate Limit ExceededError: User Rate Limit Exceeded

Error: User Rate Limit ExceededError: User Rate Limit Exceeded:

8

Error: User Rate Limit Exceeded

Declare @DateToSearch DateTime
Set @DateToSearch = '14 AUG 2008'

SELECT * 
FROM table1
WHERE Year(column_datetime) = Year(@DateToSearch)
      AND Month(column_datetime) = Month(@DateToSearch)
      AND Day(column_datetime) = Day(@DateToSearch)
1

DECLARE @Dat

SELECT * 
FROM Jai
WHERE                                                                                                          
CONVERT(VARCHAR(2),DATEPART("dd",Date)) +'/'+                                                              
             CONVERT(VARCHAR(2),DATEPART("mm",Date)) +'/'+              
                     CONVERT(VARCHAR(4), DATEPART("yy",Date)) = @Dat
3

Error: User Rate Limit ExceededCast(Floor(Cast(GetDate() As Float)) As DateTime)Error: User Rate Limit Exceeded

Select
*
Table1
Where
Cast(Floor(Cast(Column_DateTime As Float)) As DateTime) = '14-AUG-2008'

Error: User Rate Limit Exceeded

DECLARE  @p_date DATETIME
SET      @p_date = Cast('14 AUG 2008' as DateTime)

SELECT   *
FROM     table1
WHERE    Cast(Floor(Cast(column_datetime As Float)) As DateTime) = @p_date
Error: User Rate Limit Exceeded
0

SELECT * FROM tablename
WHERE CAST(FLOOR(CAST(column_datetime AS FLOAT))AS DATETIME) = '30 jan 2012'
4

Error: User Rate Limit Exceeded

DECLARE @p_date DATETIME
SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT  *
FROM    table1
WHERE   DATEDIFF( d, column_datetime, @p_date ) = 0

Error: User Rate Limit Exceeded

3

Error: User Rate Limit Exceeded

SELECT 'Same Date' WHERE CAST(getDate() as date) = cast('2/24/2012 2:23 PM' as date)

Error: User Rate Limit Exceeded

SELECT 'Same Date' WHERE DATEDIFF(dd, cast(getDate() as date), cast('2/24/2012 2:23 PM' as date)) = 0
1

Error: User Rate Limit Exceeded

DECLARE @strDate VARCHAR(15)
SET @strDate ='07-12-2010'


SELECT * FROM table
WHERE CONVERT(VARCHAR(15),dtInvoice, 112)>= CONVERT(VARCHAR(15),@strDate , 112)
36

Error: User Rate Limit Exceeded

 DECLARE @p_date DATETIME
 SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

 SELECT  *
 FROM    table1
 WHERE   column_datetime >= @p_date
 AND     column_datetime < DATEADD(d, 1, @p_date)

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceededtechnet.microsoft.com/en-us/library/ms187922.aspxError: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded

Похожие вопросы