locked
How do I get just the date in sql from a datetime? RRS feed

  • Question

  • User-858993849 posted

    I can get the year by doing the following to a datetime year(@date).  How can I get just the date from a datetime in sql?

    Saturday, December 31, 2016 1:34 AM

Answers

  • User-746821919 posted

    I assume that you are asking to just get only day.  If so, use below

    SELECT DATEPART(DD,GETDATE())
    
    

    If you wanted to get only date, use below 

    SELECT CAST(GETDATE() AS DATE)

    Refer below link to get part of date.

    http://sqlhints.com/2014/02/03/how-to-get-day-month-year-and-time-part-from-datetime-in-sql-server/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 31, 2016 6:57 AM
  • User364663285 posted

    Try

    select convert(varchar,getdate(),105)
    

    to show only date part, like 31-12-2016.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 31, 2016 1:48 PM
  • User753101303 posted

    Hi,

    Good, it's always best to know what you are trying to do.

    For this my personal preference would be to select rows >=january 1st and <january first of the next year. The point is that an index on the ExpeDate could be still then used while using the year function will prevent using the index.

    Else for the particular error you have I would use sp_executesql parameters (it's only @TableName for which you have to build a string, you can still use parameters for the other values and then you don't have to deal with converting back and forth to and from actual types to a string which is likely your issue.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 1, 2017 12:41 PM
  • User-2057865890 posted

    Hi JAYHAWKER,

    DECLARE @PeriodEnding DATETIME 
    
    SET @PeriodEnding = '2001-01-01' 
    
    DECLARE @sql NVARCHAR(max) 
    
    SET @sql = 'select year(@PeriodEnding)' 
    
    EXECUTE Sp_executesql 
      @sql, 
      N'@PeriodEnding nvarchar(75)', 
      @PeriodEnding = @PeriodEnding 

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 2:44 AM
  • User753101303 posted

    Is this your actual code ? I don"t see @AssetID anywhere in the code you are showing. I would keep @PeriodEnding as a datetime (rather than as nvarchar(75)).

    For the date criteria I would do something such as :

    DECLARE @YearStart DATETIME
    SET @YearStart=CAST(YEAR(@PeriodEnding) AS VARCHAR)+'0101' -- January 1st for the same year

    And then something such as :

    Set @sql = 'select * 
    FROM ' + @TableName + 
    ' 
    WHERE (ID = @ID)
    AND nueDate >= @YearStart 
    AND nueDate <= @PeriodEnding
    '
    exec sp_executesql @sql,
    N'@YearStart DATETIME',
    N'@PeriodEnding DATETIME', 
    N'@ID INT',
    @YearStart=@YearStart,
    @PeriodEnding = @PeriodEnding,
    @ID = @ID
    
    End

    It should be more efficient if you have an index on the nuedate column (maybe a typo for duedate ?)

    Edit: you don't pass @AssetID as a table name parameter ???!!!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 5:52 PM
  • User753101303 posted

    Unclear. So your intent is that @ID is a column name ??? In your parameter list it is an INT so you shoudln't be able IMO to pass a string??

    What is your intent? In addition to the table name you would need to pass the name of the Id column (and maybe add an additional parameter for the actual id value for which you want to retrieve data ???)

    Edit: is your intent to do something such as :

    DECLARE @TableName VARCHAR(64)
    DECLARE @IdName VARCHAR(64)
    DECLARE @SQL NVARCHAR(max)
    DECLARE @IdValue INT 
    
    SET @TableName='Information_Schema.Columns'
    SET @IdName='Ordinal_Position'
    SET @IdValue=5
    SET @Sql='SELECT * FROM '+@TableName+' WHERE '+@IdName+'=@IdValue'
    PRINT @Sql -- Check if this is what you expect
    
    EXEC sp_executesql @SQL,N'@IdValue INT',@IdValue=@IdValue

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 7:53 PM
  • User753101303 posted

    So try my earlier response. My understanding is that you want to pass a column name as well and that you should add a parameter for the actual column value. You would generate then a SQL statement with a table name and column name and other values are still passed as parameters.

    Make sure also it's worth. At some point it seems it would be jsut easier to directly pass the correct SQL statement from your C# code rather than to pass parameters to a SP to just build a SQL statement and then run this on the server side... ie on the C# side you could just use something such as :

    MyHelper.GetReader("SELECT * FROM ActualTableName WHERE ActualColumnName=@ID",IdValue); and be done with that..

    Rather than MyHelper.GetReader("ActualTableName","ActualColumnName",IdValue);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 8:07 PM

All replies

  • User-746821919 posted

    I assume that you are asking to just get only day.  If so, use below

    SELECT DATEPART(DD,GETDATE())
    
    

    If you wanted to get only date, use below 

    SELECT CAST(GETDATE() AS DATE)

    Refer below link to get part of date.

    http://sqlhints.com/2014/02/03/how-to-get-day-month-year-and-time-part-from-datetime-in-sql-server/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 31, 2016 6:57 AM
  • User364663285 posted

    Try

    select convert(varchar,getdate(),105)
    

    to show only date part, like 31-12-2016.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 31, 2016 1:48 PM
  • User-858993849 posted

    Thanks for the replies.

    By date I am actually meaning the "date" component of a date time such as 11/01/2001 or 2001-01-01

    If I have a variable @PeriodEnding that I passed to a stored procedure, and the value is a datetime, I want to extract only the date part in the stored procedure. 

    For example, the following works fine with a hard coded date (2013-01-01), as in the following:

    (
    @TableName varchar(50),
    @ID varchar(50),
    @PeriodEnding datetime
    )
    As
    Begin
    declare @sql nvarchar(max)
    Set @sql = 'select *
    From ' + @TableName +
    ' Where (year(ExpDate) = year("2013-01-01"))

    And (ID = ' + @ID + ')'

    exec sp_executesql @sql
    End

    but trying to feed the datetime variable @PeriodEnding in as follows:

    (
    @TableName varchar(50),
    @ID varchar(50),
    @PeriodEnding datetime
    )
    As
    Begin
    declare @sql nvarchar(max)
    Set @sql = 'select *
    From ' + @TableName +
    ' Where (year(ExpeDate) = year(' + @PeriodEnding + '))

    And (ID = ' + @ID + ')'

    exec sp_executesql @sql
    End

    gives me the error:

    Conversion failed when converting date and/or time from character string.

    Saturday, December 31, 2016 3:49 PM
  • User753101303 posted

    Hi,

    Good, it's always best to know what you are trying to do.

    For this my personal preference would be to select rows >=january 1st and <january first of the next year. The point is that an index on the ExpeDate could be still then used while using the year function will prevent using the index.

    Else for the particular error you have I would use sp_executesql parameters (it's only @TableName for which you have to build a string, you can still use parameters for the other values and then you don't have to deal with converting back and forth to and from actual types to a string which is likely your issue.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 1, 2017 12:41 PM
  • User-858993849 posted

    Thanks again for the reply. I like your idea.  Can you give me the exact SQL Code to use for that?

    Sunday, January 1, 2017 2:08 PM
  • User-2057865890 posted

    Hi JAYHAWKER,

    DECLARE @PeriodEnding DATETIME 
    
    SET @PeriodEnding = '2001-01-01' 
    
    DECLARE @sql NVARCHAR(max) 
    
    SET @sql = 'select year(@PeriodEnding)' 
    
    EXECUTE Sp_executesql 
      @sql, 
      N'@PeriodEnding nvarchar(75)', 
      @PeriodEnding = @PeriodEnding 

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 2:44 AM
  • User-858993849 posted

    Chris, thank you so much.  That was really helpful. One more related question.

    In trying the following:

    (
    @TableName varchar(50),
    @ID int,
    @PeriodEnding DATETIME
    )
    As
    Begin

    DECLARE @sql nvarchar(max)
    Set @sql = 'select *
    FROM ' + @TableName +
    '
    WHERE (ID = @ID)
    AND (year(nueDate) = year(@PeriodEnding))
    AND (nueDate <= @PeriodEnding)
    '
    exec sp_executesql @sql,
    N'@PeriodEnding nvarchar(75)',
    N'@ID nvarchar(75)',
    @PeriodEnding = @PeriodEnding,
    @ID = @ID

    End

    I get the error:

     Must declare the scalar variable "@AssetID".

    Tuesday, January 3, 2017 5:34 PM
  • User753101303 posted

    Is this your actual code ? I don"t see @AssetID anywhere in the code you are showing. I would keep @PeriodEnding as a datetime (rather than as nvarchar(75)).

    For the date criteria I would do something such as :

    DECLARE @YearStart DATETIME
    SET @YearStart=CAST(YEAR(@PeriodEnding) AS VARCHAR)+'0101' -- January 1st for the same year

    And then something such as :

    Set @sql = 'select * 
    FROM ' + @TableName + 
    ' 
    WHERE (ID = @ID)
    AND nueDate >= @YearStart 
    AND nueDate <= @PeriodEnding
    '
    exec sp_executesql @sql,
    N'@YearStart DATETIME',
    N'@PeriodEnding DATETIME', 
    N'@ID INT',
    @YearStart=@YearStart,
    @PeriodEnding = @PeriodEnding,
    @ID = @ID
    
    End

    It should be more efficient if you have an index on the nuedate column (maybe a typo for duedate ?)

    Edit: you don't pass @AssetID as a table name parameter ???!!!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 5:52 PM
  • User-858993849 posted

    Yes, sorry about that.  ID is "AssetID".  I still get the same error using the above recommendation.

    Thanks for your efforts.

    Tuesday, January 3, 2017 7:20 PM
  • User753101303 posted

    Unclear. So your intent is that @ID is a column name ??? In your parameter list it is an INT so you shoudln't be able IMO to pass a string??

    What is your intent? In addition to the table name you would need to pass the name of the Id column (and maybe add an additional parameter for the actual id value for which you want to retrieve data ???)

    Edit: is your intent to do something such as :

    DECLARE @TableName VARCHAR(64)
    DECLARE @IdName VARCHAR(64)
    DECLARE @SQL NVARCHAR(max)
    DECLARE @IdValue INT 
    
    SET @TableName='Information_Schema.Columns'
    SET @IdName='Ordinal_Position'
    SET @IdValue=5
    SET @Sql='SELECT * FROM '+@TableName+' WHERE '+@IdName+'=@IdValue'
    PRINT @Sql -- Check if this is what you expect
    
    EXEC sp_executesql @SQL,N'@IdValue INT',@IdValue=@IdValue

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 7:53 PM
  • User-858993849 posted

    @ID is actually @AssetID, not the primary column, but a column in the table that is set as an integer type.  

    Tuesday, January 3, 2017 7:57 PM
  • User753101303 posted

    So try my earlier response. My understanding is that you want to pass a column name as well and that you should add a parameter for the actual column value. You would generate then a SQL statement with a table name and column name and other values are still passed as parameters.

    Make sure also it's worth. At some point it seems it would be jsut easier to directly pass the correct SQL statement from your C# code rather than to pass parameters to a SP to just build a SQL statement and then run this on the server side... ie on the C# side you could just use something such as :

    MyHelper.GetReader("SELECT * FROM ActualTableName WHERE ActualColumnName=@ID",IdValue); and be done with that..

    Rather than MyHelper.GetReader("ActualTableName","ActualColumnName",IdValue);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2017 8:07 PM