Extracting the date from a datetime value

Hi,
Is there a single command to extract the whole date part (day, month and
year) from a datetime value?
I have come across the Datepart function, which allows to extract, the day
or month or year depending on the parameters passed to it. Converting all of
the above to string, concatenating all the strings and converting to datetim
e
datatype seems to be a long procedure. Is there any other way to do this?
Thanks.
kdkd
SELECT CONVERT(CHAR(10),GETDATE(),121)
Note : take a look at 'style' the third parameter of the function
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F330C878-4B5A-406C-B17B-7757CA603668@.microsoft.com...
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to
datetime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Hi
Just go through this. This might be helpful
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Here you go.
SELECT CONVERT(NVARCHAR, GETDATE(), 103)
Style 103 is the british/french output stype which gives you the following
output:
03/05/2005
Now to do this in the mm/dd/yy output you would do it as followed:
SELECT CONVERT(NVARCHAR, GETDATE(), 101)
Which gives you the output of:
05/03/2005
Just open books online and in search box type "convert date style" which
will give you the first returned results of "cast and convert" in transact
sql ,this shows you serveral was to convert the date into specific output
formats. Just pick one that is easiest for you to work with. The first one
I
listed above gives you exactly what you were asking for.
Hope this helps.
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||This will give the date part of @.dt as a datetime value, without
requiring any conversions to character formats.
dateadd(day,datediff(day,0,@.dt),0)
Steve Kass
Drew University
kd wrote:

>Hi,
>Is there a single command to extract the whole date part (day, month and
>year) from a datetime value?
>I have come across the Datepart function, which allows to extract, the day
>or month or year depending on the parameters passed to it. Converting all o
f
>the above to string, concatenating all the strings and converting to dateti
me
>datatype seems to be a long procedure. Is there any other way to do this?
>Thanks.
>kd
>|||The convert() solutiions presented all convert the datetime value to a
string, and truncate the characters representing the time portion...
Another option to add to your bag of tricks relies on the fact that
DateTimes are stored internally as two integers, which present as a decimal,
the integer portion of which is the date, and the fractional portion of whic
h
is the time. So if you just strip off the fractional portion, you have a
date by itself.
Set NoCount On
Declare @.D DateTime Set @.D = '20050503 14:23:12'
Select @.D OrigDate
Set @.D = Floor(Cast(@.D As Float))
Select @.D ChangedDate"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||While your solution works, it is not based on the internal
representation of datetime (and that's probably a good thing).
Converting datetime to float and back is a lot of work, and
your example converts 0x0000964900ED15C0 to
0x40E2C9332EA61D95, then applies floor to yield
0x40E2C92000000000, then converts back to the internal
datetime value 0x0000964900000000.
The basis for your solution is the interpretation SQL Server
provides to the user who is asking for a numerical value for
a datetime - SQL Server presents and understands numbers
as dates in terms of number of days from January 1, 1900.
That's part of the abstraction seen by users, and has nothing
to do with the implemenation. Your solution only needs
the cast from datetime to float and vice versa to work as
documented, which it should regardless of the internals.
A (less wise) solution that does depend on the 8-byte
(calling it two-integer is a stretch - bytes are bytes)
implementation of datetime values could use
Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
In any case, I think it's best to avoid implicit and explicit
conversions altogether, except for the one that converts
something typed in the query into a datetime value. I think
it was Gert-Jan who did some testing and found that this
is faster, too.
dateadd(d,datediff(d,0,@.D),0)
SK
CBretana wrote:
>The convert() solutiions presented all convert the datetime value to a
>string, and truncate the characters representing the time portion...
>Another option to add to your bag of tricks relies on the fact that
>DateTimes are stored internally as two integers, which present as a decimal
,
>the integer portion of which is the date, and the fractional portion of whi
ch
>is the time. So if you just strip off the fractional portion, you have a
>date by itself.
>Set NoCount On
>Declare @.D DateTime Set @.D = '20050503 14:23:12'
>Select @.D OrigDate
>Set @.D = Floor(Cast(@.D As Float))
>Select @.D ChangedDate"kd" wrote:
>
>|||Actually it sets the time to midnight that day. And depending on the
appliation you are using to view it it may shoul 12:00 PM in additon to the
date.
KD the real question is what do you intend to do with it? Is it for use in a
query or for presentation, if for presentation and you have mixed locals
where the standard date format i different then it is many times better to d
o
on the client side fo distributed apps. If web based then you need to take
into their local the proper format or opt for a specific format that you can
enforce.
"Steve Kass" wrote:

> This will give the date part of @.dt as a datetime value, without
> requiring any conversions to character formats.
> dateadd(day,datediff(day,0,@.dt),0)
> Steve Kass
> Drew University
> kd wrote:
>
>|||Steve,
I never thought to directly read the binary values and see what they
were... Thisis interesting... The binary value of '20050503 14:23:12' is
0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, but
no matter what I do, I can't figure out what SQL is doing with the low 4
bytes...
0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
24 hour day (86,400,000 millesconds) and it should be 51,791,971
(0.599444111110 of a 24 hr day).
The only thing I'm not clear on is why Casting to a Float should be
"expensive"... Is it because the system has to convert the binary data into
a
binary IEEE Mantissa/Exponent structure? I would have thought that would be
fairly performant, because it's a binary represeentation, which I "imagine"
all computers would somehow be internally optimized for, outside the scope o
f
whatever software was initiating this task. Not so? Why Not? (if you know
!)
Anyway,
"Steve Kass" wrote:

> While your solution works, it is not based on the internal
> representation of datetime (and that's probably a good thing).
> Converting datetime to float and back is a lot of work, and
> your example converts 0x0000964900ED15C0 to
> 0x40E2C9332EA61D95, then applies floor to yield
> 0x40E2C92000000000, then converts back to the internal
> datetime value 0x0000964900000000.
> The basis for your solution is the interpretation SQL Server
> provides to the user who is asking for a numerical value for
> a datetime - SQL Server presents and understands numbers
> as dates in terms of number of days from January 1, 1900.
> That's part of the abstraction seen by users, and has nothing
> to do with the implemenation. Your solution only needs
> the cast from datetime to float and vice versa to work as
> documented, which it should regardless of the internals.
> A (less wise) solution that does depend on the 8-byte
> (calling it two-integer is a stretch - bytes are bytes)
> implementation of datetime values could use
> Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
> In any case, I think it's best to avoid implicit and explicit
> conversions altogether, except for the one that converts
> something typed in the query into a datetime value. I think
> it was Gert-Jan who did some testing and found that this
> is faster, too.
> dateadd(d,datediff(d,0,@.D),0)
> SK
> CBretana wrote:
>
>|||I think this is the thread that Steve is referring to:
http://groups.google.nl/groups?hl=n...>
dy.nl&rnum=4
(url may wrap)
HTH,
Gert-Jan
CBretana wrote:
> Steve,
> I never thought to directly read the binary values and see what they
> were... Thisis interesting... The binary value of '20050503 14:23:12' is
> 0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
> If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
> ( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, bu
t
> no matter what I do, I can't figure out what SQL is doing with the low 4
> bytes...
> 0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
> 24 hour day (86,400,000 millesconds) and it should be 51,791,971
> (0.599444111110 of a 24 hr day).
> The only thing I'm not clear on is why Casting to a Float should be
> "expensive"... Is it because the system has to convert the binary data int
o a
> binary IEEE Mantissa/Exponent structure? I would have thought that would
be
> fairly performant, because it's a binary represeentation, which I "imagine
"
> all computers would somehow be internally optimized for, outside the scope
of
> whatever software was initiating this task. Not so? Why Not? (if you kn
ow!)
> Anyway,
> "Steve Kass" wrote:
>