query database table: date

Showing posts with label date. Show all posts

Friday, March 30, 2012

How to remove time from date?

Hello,
I have a table (T1) that has a field that holds a date with a time. In
another table (T2) I have records related to T1 on an ID field (one T1 to
many T2). T2 has a date field also, but this field does not have time (ie
the time part is midnight). I want to join the two tables on the ID and
Date but the time portions of the dates are not equal so they do not match.
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND T1.Date = T2.Date
Does anyone know how I can set the time portion of T1.Date to midnight in
the query (not in the table) so the join will work.
I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
want the time part dropped or midnight.
Thanks
EdmundHi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry|||I don't know why I did not think of this. It was staring right at me.
Thanks Barry. I've gone for CONVERT(CHAR(10),T1.Date, 120)) as it will
implicitly convert to DATETIME with date as midnight.
Edmund.
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1149517628.941905.154220@.h76g2000cwa.googlegroups.com...
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry
>|||try this:
CONVERT(VARCHAR(10), T1.date, 126)
Barry wrote:
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry|||Edmund,
You might want to chk this article :: http://www.aspfaq.com/show.asp?id=2460
Extract from that article:
--
For example, to get today's date in YYYYMMDD format, you currently need to
call the following:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
What does the 112 mean? Nothing. It's just an arbitrary number representing
this specific format.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Edmund" wrote:

> Hello,
> I have a table (T1) that has a field that holds a date with a time. In
> another table (T2) I have records related to T1 on an ID field (one T1 to
> many T2). T2 has a date field also, but this field does not have time (ie
> the time part is midnight). I want to join the two tables on the ID and
> Date but the time portions of the dates are not equal so they do not match
.
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND T1.Date = T2.Date
> Does anyone know how I can set the time portion of T1.Date to midnight in
> the query (not in the table) so the join will work.
> I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
> want the time part dropped or midnight.
> Thanks
> Edmund
>
>|||Edmund,
To truly remove time from datetime and not just "hide" it:
SELECT CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME)
Returns:
2006-06-06 00:00:00.000
Mark|||A slightly better way to do it than using the CONVERT() function calls
would be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d,
DATEDIFF(d, 0, T2.Date), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web
article about datetime data including a short section on stripping time
info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
*mike hodgson*
http://sqlnerd.blogspot.com
Barry wrote:

>Hi,
>Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
>(Varchar(13), T2.Date, 112)
>
>HTH
>Barry
>
>|||Mike, this is very good.
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:u4N4C8PiGHA.1612@.
TK2MSFTNGP04.phx.gbl...
A slightly better way to do it than using the CONVERT() function calls would
be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d, DATEDIFF(d, 0, T2.Da
te), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web article a
bout datetime data including a short section on stripping time info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
mike hodgson
http://sqlnerd.blogspot.com
Barry wrote:
Hi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry

Friday, February 24, 2012

How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.

How to query on a date range, display matching records but also display companies with no matchi

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

UNION ALL

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham

How to query on a date range, display matching records but also display companies with no ma

Jambi,

you can basically write an Union Sql query

UNION ALL

WHERE some 7 other company exist

The union statement will return your date range and the other 7 company data if they exist.

Ham

Sunday, February 19, 2012

how to query for a column value that contains dashes

Hi,

I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:

2007-10-02 09:54:00.000

The table is called 'profile' and the column 'msgdate'

I want to return only rows that match a specific date. So far I have
the following query working:

select * from profile where msgdate like '%2007%'

This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02

I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.

JamesWhat exactly is the data type of the column msgdate? You mention it
is a 'datetime' column. If it is the datatype of datetime then forget
about strings, it is stored internally as a couple of numbers. With a
non-zero time the standard way to test for a specific date is:

WHERE msgdate >= '20071002' AND msgdate < '20071003'

Note that the second date is the next day. This approach allows use
of an index is one is available and otherwise makes sense.

Roy Harvey
Beacon Falls, CT

On Fri, 12 Oct 2007 10:01:32 -0700, jdrake@.living-dead.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a large table with a 'datetime' column that has date and time
>values in it. The data is in this format:
>
>2007-10-02 09:54:00.000
>
>The table is called 'profile' and the column 'msgdate'
>
>I want to return only rows that match a specific date. So far I have
>the following query working:
>
>select * from profile where msgdate like '%2007%'
>
>This returns all rows that start with '2007'. However I cannot seem to
>ge the syntax that will allow me to return a specific date, e.g.
>2007-10-02
>
>I have researched this, trying all sorts of queries with escape
>characters/sequences because of the dash character, but I cannot get
>it to return anything. Most of my queries have ran without error, its
>just that no data is returned.
>
>
>James

|||Something like this (untested, but you see the point):

select <col list>
from profile
where msgdate >= @.date and msgdate < dateadd(dd,1,@.date)

date would be a parameter...

<jdrake@.living-dead.netwrote in message
news:1192208492.248548.49030@.e34g2000pro.googlegro ups.com...

Quote:

Originally Posted by

Hi,
>
I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:
>
2007-10-02 09:54:00.000
>
The table is called 'profile' and the column 'msgdate'
>
I want to return only rows that match a specific date. So far I have
the following query working:
>
select * from profile where msgdate like '%2007%'
>
This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02
>
I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.
>
>
James
>

How to query dates

How can I query date only in a where clause and not include the time part? When I use the where below if the time part of the date entry has past then the rows are not returned.

WHERE A.Selected=1 AND A.EndDate Is Null OR A.Enddate >= GETDATE()

Thanks,there are convert functions with codes like 410,411...etc am not xactly sure which one it is..may be if yo google around you can get the snippet of code...
another way you can also try


WHERE A.Selected=1 AND A.EndDate Is Null OR
(
datepart(m,A.Enddate) >= datepart(m,GETDATE()) and
datepart(d,A.Enddate) >= datepart(d,GETDATE()) and
datepart(y,A.Enddate) >= datepart(y,GETDATE())
)

hth