none
DATEFROMPARTS:Issue when using BETWEEN RRS feed

  • Question

  • I have 06/22 , 01/22, 02/22 , 02/20, 01/20 :in Exp column

    Select DATEFROMPARTS(2000 + CAST(ExpYear AS INT), CAST(ExpMonth AS INT), 1) AS ExpDate
    to get Expdate  as 2022-06-01, 2022-01-01, 2022-02-01, 2020-01-01 
    then, 

    SELECT ExpDate,ID,name,date,phone
    from Table abc 
    2022-06-01  nick  2020-01-03 12345678900
    works fine , 

    but below when i only want people info between 2020-01-01  2020-02-01  i use datefromparts i get error:
    Msg 289, Level 16, State 1, Line 4
    Cannot construct data type date, some of the arguments have values which are not valid.






    SELECT ExpDate,ID,name,date,phone
    from Table abc :
    WHERE(ExpDate) BETWEEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND  DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    Msg 289, Level 16, State 1, Line 4
    Cannot construct data type date, some of the arguments have values which are not valid.

    I looked at all the year and month data and everything looks good but dont know why this is popping up : 
    I tried with isdate() function , didnt help.
    can someone please suggest a tip 
    1. is there a way to validate column data( expyear, Expmonth etc)?if my year and month is with in range or has any space, mixed or special charactes or any bad data?
     


    Friday, January 3, 2020 7:08 PM

Answers

  • Hi coolguy123SQL,

    Please check following script .

    drop table test 
    go
    create table test 
    (ExpDate  varchar(20))
    insert into test values 
    ('06/22'), ('01/22'), ('02/22'), ('02/20'), ('01/20')
    ;with cte as (
    Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate
    from test 
    )
    SELECT ExpDate
    from cte
    WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)
    
    /*
    ExpDate
    ----------
    2020-02-01
    2020-01-01
    */
    insert into test values ('13/22')
    ;with cte as (
    Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate
    from test 
    )
    SELECT ExpDate
    from cte
    WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)
    /*
    ExpDate
    ----------
    2020-02-01
    2020-01-01
    Msg 289, Level 16, State 1, Line 22
    Cannot construct data type date, some of the arguments have values which are not valid.
    */

    Best Regrds,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 6, 2020 3:35 AM

All replies

  • Post the scripts to CREATE TABLE ABC … and INSERT INTO ABC … with some sample data and tell what the outputs you expect.  

    A Fan of SSIS, SSRS and SSAS

    Friday, January 3, 2020 7:20 PM
  • Instead of trying to use DATEFROMPARTS - use this instead:

    WHERE ExpDate >= dateadd(month, datediff(month, 0, getdate()), 0)
    AND ExpDate < dateadd(month, datediff(month, 0, getdate()) + 1, 0)

    You should also be careful when using BETWEEN for dates - if the column ExpDate is a datetime data type and contains the time then you would exclude everything from 00:00:00.003 up to 23:59:59.997 on the end date specified.

    If you are sure the data type is a DATE data type then you could use BETWEEN with this:

    WHERE ExpDate Between dateadd(month, datediff(month, 0, getdate()), 0) AND EOMONTH(getdate())
    

    Or even this:

    WHERE ExpDate Between DATEADD(DAY, 1, EOMONTH(getdate(), -1) AND EOMONTH(getdate())

    The first method will work for all date data types - the other methods will only work correctly for the DATE data type.


    Jeff Williams

    Friday, January 3, 2020 7:49 PM
  • Hi coolguy123SQL,

    Please check following script .

    drop table test 
    go
    create table test 
    (ExpDate  varchar(20))
    insert into test values 
    ('06/22'), ('01/22'), ('02/22'), ('02/20'), ('01/20')
    ;with cte as (
    Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate
    from test 
    )
    SELECT ExpDate
    from cte
    WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)
    
    /*
    ExpDate
    ----------
    2020-02-01
    2020-01-01
    */
    insert into test values ('13/22')
    ;with cte as (
    Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate
    from test 
    )
    SELECT ExpDate
    from cte
    WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)
    /*
    ExpDate
    ----------
    2020-02-01
    2020-01-01
    Msg 289, Level 16, State 1, Line 22
    Cannot construct data type date, some of the arguments have values which are not valid.
    */

    Best Regrds,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 6, 2020 3:35 AM
  • Rachel, your suggestion works when you have dad data, But my table do not have bad data, confirmed with Excel, ISNUMERIC, Substring.

    Anyways i created temp tables instead of CTE and it worked for me .

    Tuesday, January 14, 2020 9:28 PM
  • create table test (Expdate varchar(5))
    insert into test values('06/22'),
    ('01/22'),
    ('02/22'),
    ('02/20'),
    ('01/20') 
    
    Select Convert(Date,'01/'+Expdate ,3) [Date yyyy-MM-dd]
    from test
    --
    
    drop table test

    Tuesday, January 14, 2020 10:12 PM
    Moderator