Date format retrieval

Hello!
I have two dates each with the datetime format.
However in one, only the date portion is being stored and in the other
the time with the date of 1/1/1900 is being stored.
For eg. datefield1 = '2/5/2007 00:00:00'
datefield2 = '1/1/1900 09:35:00'
How can I get only the date part in 'Datefield1' and the time part in
'Datefield2'
the resultant should be a date format as I want to compare it with another
datetime field.
Thank you very much for your help!
-MeSuggestion #1 is to store these values together. What are you gaining by
splitting them apart?
Suggestion #2 I'll demonstrate with local variables:
DECLARE
@.datefield1 SMALLDATETIME,
@.datefield2 SMALLDATETIME;
SELECT
@.datefield1 = '20070205',
@.datefield2 = '09:35';
SELECT
@.datefield1,
@.datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
"Me" <Me@.discussions.microsoft.com> wrote in message
news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
> Hello!
> I have two dates each with the datetime format.
> However in one, only the date portion is being stored and in the other
> the time with the date of 1/1/1900 is being stored.
> For eg. datefield1 = '2/5/2007 00:00:00'
> datefield2 = '1/1/1900 09:35:00'
> How can I get only the date part in 'Datefield1' and the time part in
> 'Datefield2'
> the resultant should be a date format as I want to compare it with another
> datetime field.
> Thank you very much for your help!
> -Me|||Hi Aaron,
Thanks for the reply!
However, this doesn't address my issue.
'cause I already have 'datefiedl1' and 'datefield2' in datetime format in a
table.
Therefore, if I issue a select on these fields even as smalldatetime, I get
the date in date+time format, all I want is to segregate the datetime into
date and time.
Please let me know if you have other ideas.
-Me
"Aaron Bertrand [SQL Server MVP]" wrote:
> Suggestion #1 is to store these values together. What are you gaining by
> splitting them apart?
> Suggestion #2 I'll demonstrate with local variables:
> DECLARE
> @.datefield1 SMALLDATETIME,
> @.datefield2 SMALLDATETIME;
> SELECT
> @.datefield1 = '20070205',
> @.datefield2 = '09:35';
> SELECT
> @.datefield1,
> @.datefield2,
> Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
>
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
> > Hello!
> >
> > I have two dates each with the datetime format.
> > However in one, only the date portion is being stored and in the other
> > the time with the date of 1/1/1900 is being stored.
> >
> > For eg. datefield1 = '2/5/2007 00:00:00'
> > datefield2 = '1/1/1900 09:35:00'
> >
> > How can I get only the date part in 'Datefield1' and the time part in
> > 'Datefield2'
> >
> > the resultant should be a date format as I want to compare it with another
> > datetime field.
> >
> > Thank you very much for your help!
> > -Me
>
>|||Sorry, I thought you would be able to adapt the example to your own table.
Let's try from start to finish? The eventual query only changes by dropping
the @. signs and adding a FROM.
USE Tempdb;
GO
CREATE TABLE dbo.foo
(
datefield1 DATETIME,
datefield2 DATETIME
);
GO
SET NOCOUNT ON;
INSERT
dbo.foo
SELECT
'20070205 00:00',
'19000101 09:35';
SELECT
datefield1,
datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,datefield1), datefield2)
FROM
dbo.foo;
GO
DROP TABLE dbo.foo;
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F850AD2F-A629-4A41-9C5B-69DFE1BB9D0C@.microsoft.com...
> Hi Aaron,
> Thanks for the reply!
> However, this doesn't address my issue.
> 'cause I already have 'datefiedl1' and 'datefield2' in datetime format in
> a
> table.
> Therefore, if I issue a select on these fields even as smalldatetime, I
> get
> the date in date+time format, all I want is to segregate the datetime into
> date and time.
> Please let me know if you have other ideas.
> -Me
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Suggestion #1 is to store these values together. What are you gaining by
>> splitting them apart?
>> Suggestion #2 I'll demonstrate with local variables:
>> DECLARE
>> @.datefield1 SMALLDATETIME,
>> @.datefield2 SMALLDATETIME;
>> SELECT
>> @.datefield1 = '20070205',
>> @.datefield2 = '09:35';
>> SELECT
>> @.datefield1,
>> @.datefield2,
>> Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
>>
>> "Me" <Me@.discussions.microsoft.com> wrote in message
>> news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
>> > Hello!
>> >
>> > I have two dates each with the datetime format.
>> > However in one, only the date portion is being stored and in the other
>> > the time with the date of 1/1/1900 is being stored.
>> >
>> > For eg. datefield1 = '2/5/2007 00:00:00'
>> > datefield2 = '1/1/1900 09:35:00'
>> >
>> > How can I get only the date part in 'Datefield1' and the time part in
>> > 'Datefield2'
>> >
>> > the resultant should be a date format as I want to compare it with
>> > another
>> > datetime field.
>> >
>> > Thank you very much for your help!
>> > -Me
>>