sql date format

When you set the variable the format doesn't come into play as far as I know. You populate it with a valid datetime value and you can then display it in any way you like. Why does the format matter when you set it?

For the update of the @endDate, you gave the answer yourself:

Select @endDate = DateAdd(year, 1, @startDate)

Select allOpen in new window

When you set the variable the format doesn't come into play as far as I know. You populate it with a valid datetime value and you can then display it in any way you like. Why does the format matter when you set it?

For the update of the @endDate, you gave the answer yourself:

Select @endDate = DateAdd(year, 1, @startDate)

Select allOpen in new window

When you set the variable the format doesn't come into play as far as I know. You populate it with a valid datetime value and you can then display it in any way you like. Why does the format matter when you set it?

For the update of the @endDate, you gave the answer yourself:

Select @endDate = DateAdd(year, 1, @startDate)

Select allOpen in new window

cstruwig has a good option there, but keep in mind that this changes the datatype from datetime to varchar, so if you wish to link this variable to another datetime field you'll have to convert it back to datetime first, or convert the other field to varchar. Hence why I don't understand the need to convert the variable in the first place.

cstruwig has a good option there, but keep in mind that this changes the datatype from datetime to varchar, so if you wish to link this variable to another datetime field you'll have to convert it back to datetime first, or convert the other field to varchar. Hence why I don't understand the need to convert the variable in the first place.

cstruwig has a good option there, but keep in mind that this changes the datatype from datetime to varchar, so if you wish to link this variable to another datetime field you'll have to convert it back to datetime first, or convert the other field to varchar. Hence why I don't understand the need to convert the variable in the first place.

Those bloody triple posts......

thanks all it was a simple thing i left in the 'AS  [YYYY-MM-DD]' which was causing the problem

please ERASE from your mind any notion that the display of a datetime is relevant here

datetime is NOT STORED in a "format"

you are specifically requesting that 2 variables be set aside as datetime values
so, AVOID using varchar functions and stick with datetime functions
and
It seems you want to get today's date, but without the time portion.
There are several ways to do this, the most generic and still the fastest is:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = dateadd(day, datediff(day,0, getDate()), 0)

SET @endDate = DATEADD(year, 1, @startDate)

suggested reading: ""The ultimate guide to the datetime datatypes"

A common misconception is that SQL Server stores these datatypes in some particular readable format. That is not the case. SQL Server stores these values in an internal format (for instance two integers for datetime and smalldatetime).

While this may get a little confusing, but there are "input formats" for setting datetime values that SQL Server will intuitively understand - and the very best of these has no separator and is the unambiguous YYYYMMDD sequence. So, IF you wanted to set @startdate to some particular day instead of 'today', you could use this:

SET @startDate = '20130701' -- SQL Server implicitly converts this to datetime

further reading about this 'format' topic:
"Bad habits to kick : mis-handling date / range queries"

Glad to see i'm not the only one who was wondering about this question Paul.

:) :) :)
i.e. smileX3 - pleased to see the x3 posting mystery remains - it's almost your signature

Just a side note on date formatting:

If for what ever reason you have to store a date as a string, I found the dd-mmm-yyyy date format to be the most useful. A date like 11-10-12 (dd-mm-yy) can easily get messed up by default date formats and client regional settings.

The dd-mmm-yyyy can never be misinterpreted no matter what the date default format setting may be.

This is absolutely critical when transporting date information between different languages and data types.

Would still be interested to hear from the original poster why the input string has to be set in this specific format. I can't think of a valid reason.

?dd-mmm-yyyy can never be misinterpreted
ne comprend pas le 'Feb'... Qu'est-ce que ça ?
¿¿'Feb'¿'¿¿¿¿¿¿¿

YYYYMMDD is the only trustworthy "date string" IMHO (& no language dependencies)
or
YYYYMMDD as int

but I'd not recommend either!
best solution is to store date/time in date/time data types in my view

(Japanese courtesy of translate.google.com)
{+edit: except that E-E just killed it - oh well - the point was language dependency}

Far from it. YYYYMMDD is not any more trustworthy than ddmmyyy, mmddyyyy or yyyyddmm.

Example. 20130203
regional setting yyyymmdd : Web browser and vbscript shows 2013 02 03 or 2013 Feb 03
regional setting yyyyddmm : Web browser and vbscript shows 2013 03 02 or 2013 Mar 02

Here's the explanation:
  2013 cannot be interpreted as dd or mm because it has 4 chars.
  02 CAN be interpreted as either dd or mm because of a different regional setting an two                 characters.
  same with 03.

With yyyymmmdd you cannot mistake 2013 for mm or dd
nor could you mistake FEB for dd or yyyy
nor could you mistake 03 for yyyy or mmm

4 Char year fits into 4 char yyyy =2013
3 Char month fits into 3 char mmm = FEB
2 Char day fits into 2 char dd = 03

As said before by others - this is not ideal but necessary when interfacing with legacy systems, regional settings and languages that use typeless variables.

if considering systems outside sql server - maybe - my comments are in respect to sql server alone & regardless: MMM is language dependent.

inside sql server, the best choice remains using date/time data types IMHO - from these you may output in yyyymmmdd if you wish, in multiple languages.

thanks for all your replies they are very helpful

Kvwielink:
Would still be interested to hear from the original poster why the input string has to be set in this specific format. I can't think of a valid reason.

Kvwielink: I am wanting the date variables in this format because thats how they are stored in the DB table so for consistency i want to use the same format. Not sure if this format is ideal [YYYY-MM-DD] but will have to do for now.

:(
>>thats how they are stored in the DB table

Fields of "datetime" type are NOT stored in ANY "format"

Your db may be setup to DISPLAY datetime as YYYY-MM-DD by default - but that has nothing whatever to to do with the method of storage.

A datetime field is actually stored as 2 integers (one for whole days, and the other for time within the day)

This is similar to Excel (but not exactly the same)
add this number into any Excel cell

41456.7269328704

now format that cell, using 'custom' and use d/mm/yyyy h:mm:ss
that number will then be represented as:
1/07/2013  5:26:47 PM

the cell really holds (stores) the number, but displays a date/time

same is true in SQL Server: datetime information is stored as numbers in your db.

IF, however a field is a varchar, but that field holds information that "looks like" a date - then the data IS stored in a "format".

so:
datetime = numeric, format is only relevant for display purposes
varchar = what you see is what you store, format is relevant for storage

note not a single varchar is used as input, and the inputs are all numbers, and format is not relevant when setting the datetime variable.

-- Excel example studied in SQL Server
-- 41456.7269328704

declare @wholedays as int
set @wholedays = 41456 - 2 -- (there is a 2 day difference in Excel and SQL Server)

declare @secs_in_day as int
set @secs_in_day = (17*60)*60+(26*60)+47 -- 17:26:47


declare @isdatetime as datetime

set @isdatetime = dateadd(second, @secs_in_day ,  dateadd(day,0,@wholedays) )

select
      @isdatetime                             as default_display_format
    , convert(varchar,@isdatetime,111)        as non_default_display_format_111
    , convert(varchar,@isdatetime,121)        as specify_display_format_121
    , datediff(day,0,@isdatetime)             as number_whole_days
    , datediff(second,@wholedays,@isdatetime) as number_of_seconds
;

Select allOpen in new window

{+ edit, typo, sorry}