Re: Tree questions about DATETIME

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/26/04 

Date: Sun, 26 Dec 2004 23:57:49 +0100

On Sat, 25 Dec 2004 17:32:14 +0400, David wrote:

>Hello.
>
>I have tree questions about dates:
>1. How can I determine how many days are in some month?
>2. How can I get first day of the some date? like if there is "12.15.2004" I
>must get "12.01.2004"?
>3. How can I build date from it's parts? like if there is: month 12, day 25
>and year 2004 (no meter is these string or int type data), I must get
>"12.25.2004" as DATETIME.

Hi David,

First, the answer to question 2. The trick is to pick a base date - any
date will do, as long as it's the first day of any month. Calculate the
number of months between the base date and your date, then add that number
of months to the base date. In SQL:

declare @MyDate datetime
select @MyDate = '20041103'

select dateadd(month, datediff(month, '20000101', @MyDate), '20000101')

We can use this as starting point for question 1. The number of days in
any month is the number of days from the start of the month too the start
of the next month. The start of the next month can be calculated the same
way as the start of the current month: get the number of month between
base date and now, but this tame add it to one month after the base date.
The formula for number of days in a month is:

select datediff(day,
                dateadd(month, datediff(month, '20000101', @MyDate),
'20000101'),
                dateadd(month, datediff(month, '20000101', @MyDate),
'20000201')) -- NOTE: last date constant is one month later!

Finally, the answer to question 3. If the date parts are character, just
paste them together to get the non-ambiguous YYYYMMDD format and cast that
to datetime; if the date parts are numeric, cast them to char first, then
proceed as per above:

declare @yy char(4), @mm char(2), @dd char(2)
select @yy = '2004', @mm = '12', @dd = '26'
select cast(@yy+@mm+@dd as datetime)
go
declare @yy int, @mm int, @dd int
select @yy = 2004, @mm = 12, @dd = 26
select cast(cast(@yy as varchar)
          + cast(@mm as varchar)
          + cast(@dd as varchar) as datetime)

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Generic method which returns generic type
    ... I find this interesting because the compiler seems to give the code the ... cast may fail. ... return 1; //return int ... return DateTime.Now; //return DateTime ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Cast and Convert
    ... So the format is, as integer, YYYYMMDD. ... is one of the formats which is language independent regarding datetime conversions from string to ... I.e., cast int to string, then string to datetime, then datetime to string (using a conversion ... The question is, of course, why you store dates as int instead of datetime... ...
    (microsoft.public.sqlserver.server)
  • Re: Cursors... whats the alternative?
    ... Exit Sub ... Dim oCmd As Command, param As Parameter ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie question about malloc
    ... "Implicit int" no longer exists in C. ... It's generally recommended to NOT cast the return from malloc. ... %d can never be the correct format specifier for a size_t. ... C99 introduced %zu for this purpose, ...
    (comp.lang.c)
  • Re: try...catch execution issue
    ... declare @ii_LinkedServerID int = 1 ... declare @ii_LinkedServerID int = null ... CAST(0x00009C5D00904037 AS DateTime), N'dcaz\couchj', CAST(0x00009C6100ADA6DB ... This procedure is used to insert/update a ServerOption to ...
    (microsoft.public.sqlserver.programming)