none
Remove Time from DATETIME

    Question

  •  

    I have a function that accepts 2 DATETIME parameters.  When the user passes a time other than midnight, the function returns a different result.  Is there a simple way to strip the time from the variables?

     

    Here's the declaration:

     

    ALTER FUNCTION [dbo].[NumBusinessDays]

    (

    -- Add the parameters for the function here

    @StartDate DATETIME,

    @EndDate DATETIME

    )

    Thursday, March 27, 2008 8:59 PM

Answers

  • This will give you just the date portion:

    convert(varchar(10),@startdate,101)

     

    This link will help provide some more information:

    http://msdn2.microsoft.com/en-us/library/ms187928.aspx

    Thursday, March 27, 2008 9:14 PM
  • Another way to do this is to do:

     

    DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

     

    I like this way better because it is always working with date values, but either way is fine

     

    I am aso not sure I would use a function to count numBusinessDays, have you ever used a calendar table, but if not, counting the number of businessdays is a snap using one of those:

     

    http://sqlblog.com/blogs/louis_davidson/archive/2006/12/09/the-joys-of-a-calendar-table-ytd-month-and-quarter-summations.aspx

     

    And if you want to compare sales on weekends vs busness days.  Easy.  Just add an attribute to your calendar and call it businessDaysFlag (or something like it).  Then load it with your calendar and your holidays set to 0. The rest is amazingly simple.

     

    Thursday, March 27, 2008 9:25 PM
    Moderator
  • Assuming your LastModifedOn is a datetime, try

    select * from DonationDetails where LastModifiedOn >='20120320' and LastModifiedOn <'20120321'

    Please take a moment to read this blog post

    Bad habits to kick : mis-handling date / range queries

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 20, 2012 3:27 AM
    Moderator
  • select * from DonationDetails where LastModifiedOn=convert(varchar(10),'20/3/2012',101)

    The reason you got the error is due to your session (string) date setting of mdy, yielding month 20 out of range. If your setting is dmy, the above statement would execute OK. As indicated by Naomi, best to use 'YYYYMMDD' ANSI string date format.

    The date format setting is tied to the language setting with SET override:

    SELECT Language=name, English=alias, dateformat, datefirst
    FROM sys.syslanguages ORDER BY langid
    /*
    Language	English	dateformat	datefirst
    us_english	English	mdy	7
    Deutsch	German	dmy	1
    Français	French	dmy	1
    日本語	Japanese	ymd	7
    Dansk	Danish	dmy	1
    Español	Spanish	dmy	1
    Italiano	Italian	dmy	1
    Nederlands	Dutch	dmy	1
    Norsk	Norwegian	dmy	1
    Português	Portuguese	dmy	7
    Suomi	Finnish	dmy	1
    Svenska	Swedish	ymd	1
    čeština	Czech	dmy	1
    magyar	Hungarian	ymd	1
    polski	Polish	dmy	1
    română	Romanian	dmy	1
    hrvatski	Croatian	ymd	1
    slovenčina	Slovak	dmy	1
    slovenski	Slovenian	dmy	1
    ελληνικά	Greek	dmy	1
    български	Bulgarian	dmy	1
    русский	Russian	dmy	1
    Türkçe	Turkish	dmy	1
    British	British English	dmy	1
    eesti	Estonian	dmy	1
    latviešu	Latvian	ymd	1
    lietuvių	Lithuanian	ymd	1
    Português (Brasil)	Brazilian	dmy	7
    繁體中文	Traditional Chinese	ymd	7
    한국어	Korean	ymd	7
    简体中文	Simplified Chinese	ymd	7
    Arabic	Arabic	dmy	1
    ไทย	Thai	dmy	7 */

    More on string datetime conversions:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    Remove time from datetime (set time part 00:00, midnight start of day):

    http://www.sqlusa.com/bestpractices/date-without-time/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, March 20, 2012 4:43 AM
    Moderator

All replies

  • This will give you just the date portion:

    convert(varchar(10),@startdate,101)

     

    This link will help provide some more information:

    http://msdn2.microsoft.com/en-us/library/ms187928.aspx

    Thursday, March 27, 2008 9:14 PM
  • Another way to do this is to do:

     

    DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

     

    I like this way better because it is always working with date values, but either way is fine

     

    I am aso not sure I would use a function to count numBusinessDays, have you ever used a calendar table, but if not, counting the number of businessdays is a snap using one of those:

     

    http://sqlblog.com/blogs/louis_davidson/archive/2006/12/09/the-joys-of-a-calendar-table-ytd-month-and-quarter-summations.aspx

     

    And if you want to compare sales on weekends vs busness days.  Easy.  Just add an attribute to your calendar and call it businessDaysFlag (or something like it).  Then load it with your calendar and your holidays set to 0. The rest is amazingly simple.

     

    Thursday, March 27, 2008 9:25 PM
    Moderator
  • Hello, 

    I tried running your above method in my query,

    select * from DonationDetails where LastModifiedOn=convert(varchar(10),'20/3/2012',101)

    But it threw an error:

    Msg 242, Level 16, State 3, Line 1

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    Value stored in my field is :2012-03-20 07:47:37.000 and i Just want to compare date part, not time.

    Please help me.


    Aditya N B

    Tuesday, March 20, 2012 2:51 AM
  • Assuming your LastModifedOn is a datetime, try

    select * from DonationDetails where LastModifiedOn >='20120320' and LastModifiedOn <'20120321'

    Please take a moment to read this blog post

    Bad habits to kick : mis-handling date / range queries

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 20, 2012 3:27 AM
    Moderator
  • select * from DonationDetails where LastModifiedOn=convert(varchar(10),'20/3/2012',101)

    The reason you got the error is due to your session (string) date setting of mdy, yielding month 20 out of range. If your setting is dmy, the above statement would execute OK. As indicated by Naomi, best to use 'YYYYMMDD' ANSI string date format.

    The date format setting is tied to the language setting with SET override:

    SELECT Language=name, English=alias, dateformat, datefirst
    FROM sys.syslanguages ORDER BY langid
    /*
    Language	English	dateformat	datefirst
    us_english	English	mdy	7
    Deutsch	German	dmy	1
    Français	French	dmy	1
    日本語	Japanese	ymd	7
    Dansk	Danish	dmy	1
    Español	Spanish	dmy	1
    Italiano	Italian	dmy	1
    Nederlands	Dutch	dmy	1
    Norsk	Norwegian	dmy	1
    Português	Portuguese	dmy	7
    Suomi	Finnish	dmy	1
    Svenska	Swedish	ymd	1
    čeština	Czech	dmy	1
    magyar	Hungarian	ymd	1
    polski	Polish	dmy	1
    română	Romanian	dmy	1
    hrvatski	Croatian	ymd	1
    slovenčina	Slovak	dmy	1
    slovenski	Slovenian	dmy	1
    ελληνικά	Greek	dmy	1
    български	Bulgarian	dmy	1
    русский	Russian	dmy	1
    Türkçe	Turkish	dmy	1
    British	British English	dmy	1
    eesti	Estonian	dmy	1
    latviešu	Latvian	ymd	1
    lietuvių	Lithuanian	ymd	1
    Português (Brasil)	Brazilian	dmy	7
    繁體中文	Traditional Chinese	ymd	7
    한국어	Korean	ymd	7
    简体中文	Simplified Chinese	ymd	7
    Arabic	Arabic	dmy	1
    ไทย	Thai	dmy	7 */

    More on string datetime conversions:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    Remove time from datetime (set time part 00:00, midnight start of day):

    http://www.sqlusa.com/bestpractices/date-without-time/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, March 20, 2012 4:43 AM
    Moderator