date problem

[Simon]

I have date field in my table:
2003-06-29 02:21:19.373

Now I would like to get the records from my table:

SELECT * FROM table WHERE date='06-29-2003'

but it doesn't work.

I get the result only if I write the select like this:


SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003'

Is there any easier solution?
I have table with more then a million records and if I use dateadd(date-1)
and dateadd(date+1),
the execution would be slower.

Thank you,
Simon

[David Portas]

The second SELECT statement you posted will include dates from 2003-06-28. I
think you wanted just one day:

SELECT * FROM table WHERE date>='20030629' AND date<'20030630'

Always use ISO (YYYYMMDD) format for dates to avoid dependencies on regional
settings

As you point out, other solutions to extract just the date portion of the
DATETIME will be slower because they cannot benefit from indexing on the
column. Above is the best method.

--
David Portas
------------
Please reply only to the newsgroup
--

[Falik Sher]

select * from table where convert(varchar(8),date,112)='20030629'
HTH
Falik


"Simon" <simon.zupan@stud-moderna.si> wrote in message
news:3f0a6be6$1@news.s5.net...

> I have date field in my table:
> 2003-06-29 02:21:19.373
>
> Now I would like to get the records from my table:
>
> SELECT * FROM table WHERE date='06-29-2003'
>
> but it doesn't work.
>
> I get the result only if I write the select like this:
>
>
> SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003'
>
> Is there any easier solution?
> I have table with more then a million records and if I use dateadd(date-1)
> and dateadd(date+1),
> the execution would be slower.
>
> Thank you,
> Simon
>
>

[johan olofsson]

"Simon" <simon.zupan@stud-moderna.si> wrote in message
news:3f0a6be6$1@news.s5.net...

> I have date field in my table:
> 2003-06-29 02:21:19.373
>
> Now I would like to get the records from my table:
>
> SELECT * FROM table WHERE date='06-29-2003'
>
> but it doesn't work.
>
> I get the result only if I write the select like this:
>
>
> SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003'
>
> Is there any easier solution?
> I have table with more then a million records and if I use dateadd(date-1)
> and dateadd(date+1),
> the execution would be slower.
>
> Thank you,
> Simon
>
>

try this:

select * from table where convert(char(10), date, 120 ) = '2003-06-29'

/johan

[Erland Sommarskog]

[posted and mailed, please reply in news]

Falik Sher (faliks@hotmail.com) writes:

> select * from table where convert(varchar(8),date,112)='20030629'

While this gives the desired result, it has the drawback that if there
is an index on the date column, SQL Server will not use the index on
the column, since you use it in an expression.


--
Erland Sommarskog, SQL Server MVP, [email]sommar@algonet.se[/email]

Books Online for SQL Server SP3 at
[url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]

[Shaun]

Hi,

Why does the following code print '00', surely it should print '08', I'm
baffled!

date("H", mktime(8, 0, 0, 0, 0, 0));

Thanks for your help

[John]

For me, on Windows, it won't work because Windows won't do anything prior to
1970.

On linux, I get 17 as the result. If I change the year to 2000, then I get
08 on both.

John


"Shaun" <shaun@mania.plus.com> wrote in message
news:20030924160719.11919.qmail@pb1.pair.com...

> Hi,
>
> Why does the following code print '00', surely it should print '08', I'm
> baffled!
>
> date("H", mktime(8, 0, 0, 0, 0, 0));
>
> Thanks for your help

[trycold]

I have a field in database where I am inputting the value as
<cfset tday = #DateFormat(CreateODBCDate(now()), "mm-dd-yyyy")#>

Now I need to query so that i can get the data b/w any two dates. How should i
query it.
I was doing
select * from table1 where tdate > 'somedate1' and tdate <'somedate2'

it keeps on giving me 0 records

[babyeatingreptile]

It could be you need to create actual date objects rather than strings of dates
- for example your message shows you using dateformat for the date entered into
the database, this would give you a string rather than a date.

[kyle969]

It depends on the database, in Oracle you would:
SELECT *
FROM table_t
WHERE tdate between '#DateFormat(date1, "dd-mm-yy")#'
and '#DateFormat(date2, "dd-mm-yy")#'

you should probably use either DateFormat or CreateODBCDate, not both.
Also, you dont need to use the #'s inside cf tags, it creates an extra
processing step.

[Ginga2050]

Hi,

I'm being plagued by a date problem. I use an access database to store my site
data in and have had no problem on the whole, but the one problem I do get it
is with storing dates.

At first I was okay and the dates went in without problem. Then all of a
sudden the dates reversed i.e. 10/5/2005 became 05/10/2005

I got round this by moving from the cfinsert tag to using

<cfquery datasource="tbsdata" name="updatenews">
UPDATE news
SET
dateadded='#dateformat(dateadded,"dd/mm/yyyy")#',title='#title#',body='#body#',t
ype='#type#',username='#username#'
WHERE id = #id#
</cfquery>

now this has suddenly started to reverse the code. for the time being I'm
using #dateformat(dateadded,"mm/dd/yyyy")# to solve the problem but it's a poor
solution.

Does anyone have any ideas why ? The table has the dateadded feild setup as a
date/time type. Would making the type text be a better idea or would this
course problems?

Cheers,

Craig.

[Stressed_Simon]

You do not want to use dates in this manner. Application wide you should use
dates in the ODBC Date format, which is how the date looks when you output the
Now() function.

Only use DateFormat() to display dates in the browser.

To make an ODBC compliant date from its parts use CreateDate(year, month, day).
To make a date that is complete use CreateODBCDate().

NEVER insert a date that is not in this format into your database as you are
asking for trouble. I hope that helps, if you want to know more look and the
date and time functions on LiveDocs:-

[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/functio6.htm#wp1098968[/url]

[Ginga2050]

Cheers. I get what you mean but I only went down that route to stop the
americanized dates. Will the methos that you suggest stop this. The problem is
now affecting to seperate servers that have been setup with UK locales etc.

[Stressed_Simon]

Well I am not a DBA but I am pretty sure that Databases store dates identically
regardless of the resional settings, it is just how they display if you view
them that is defined by the regional settings. If you use date format to show
the dates how you want in the browser, their should not be a problem. You may
want to set you locale though as this unlocks all the LS functions.

[Ginga2050]

Tried your suggestion and same problem.

With my method I am using I am getting the full date / time stamp as I would
with outputting #now()#

It look to be a problem when the data actually hits the database. I
thinkAccess might be messing the date up.

[OldCFer]

All CF date functions, including CreateODBCDate() expect months first strings,
and will create
date objects accordingly. What you need to do is this:

<cfset TheDAte = "1/2/2005"> **** 2 Feb 2005 in UK ****
<cfset x = SetLocale("English (UK)")>
<cfset UKDate = LSPArseDAteTime(TheDate)>
<cfoutput>#UKDate#</cfoutput>

Now put UKDate into the DB directly because it's a date object. Don't try
to reformat it unless it's for display only.

[Mattastic]

Hi,

I have a form that registers users on my system, but I need them to enter
their Date of birth.

I would like to populate a set of drop downs, so users can enter their day,
month and year of birth. Could anyone tell me how to get the current date and
use this to populate the lists?

I would also like to make it as fool proof as possible. How can I check to see
if they enter a valid date?

Hope you can help

[jdeline]

You can obtain the current date using the Now() function.

[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=p art_cfm.htm

Use the IsDate() function to check on the validity of a date.

[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=p art_cfm.htm

[Mattastic]

Thanks

[Dan Bracuk]

Remember to check that it is not only a valid date, but that it is not in the future.