date - Not accessing the value using between statement in sql -



date - Not accessing the value using between statement in sql -

i storing date varchar in database. when trying access info using between operator in sql, values between dates, not giving proper output because stored date varchar. date in format (01/12/2013)(dd/mm/yyyy).

any thought access right info using between statement out changing date varchar date format?

thank you.

don't confused datatype - has nil problem. see if remove / in dates - real numbers: 20/02/2013 more 21 1000000 higher 01/02/2014

20/02/2013 = 20022013 01/02/2014 = 1022014

if varchar has numbers in order of date, month, year - must reorder them in field or in query right values, if in year month day, have varchar sort datetime.

solution 1: if must leave way in table, must this:

database table:

create table dates ( date_varchar varchar(15) default null, date_datetime datetime default null ) engine=myisam default charset=utf8

data:

date_varchar date_datetime 01/02/2013 2013-02-01 00:00:00 02/02/2013 2013-02-02 00:00:00 03/02/2013 2013-02-03 00:00:00 03/02/2014 2014-02-03 00:00:00 03/03/2013 2013-03-03 00:00:00 04/02/2013 2013-02-04 00:00:00 05/02/2013 2013-02-05 00:00:00

the query:

select * dates str_to_date(date_varchar,'%d/%m/%y') between '2013-02-03' , '2013-02-04'

will homecoming 2 records:

date_varchar date_datetime 03/02/2013 2013-02-03 00:00:00 04/02/2013 2013-02-04 00:00:00

soluion 2:

to alter order in varchar year, month, date in field. se table info below:

date_varchar date_datetime 2013/02/01 2013-02-01 00:00:00 2013/02/02 2013-02-02 00:00:00 2013/02/03 2013-02-03 00:00:00 2013/02/04 2013-02-04 00:00:00 2013/02/05 2013-02-05 00:00:00 2013/03/03 2013-03-03 00:00:00 2014/02/03 2014-02-03 00:00:00

now can select :

select * dates date_varchar between '2013/02/02' , '2013/02/03'

now numbers ordered in same way datetime - can remove / , - see numbers. varchars sorted same date_time

the query homecoming same 2 rows:

date_varchar date_datetime 2013/02/02 2013-02-02 00:00:00 2013/02/03 2013-02-03 00:00:00

solution 3:

this not you, because don't want alter table, include create complete. changing datatype datetime forced set numbers in right order , utilize query range between dates:

select * dates date_datetime between '2013-02-03' , '2013-02-04'

the same 2 record:

date_varchar date_datetime 03/02/2013 2013-02-03 00:00:00 04/02/2013 2013-02-04 00:00:00

example of problem:

now selecting dates varchar returns - guessed - same 2 records first query and more, happening you:

data:

date_varchar date_datetime 01/02/2013 2013-02-01 00:00:00 02/02/2013 2013-02-02 00:00:00 03/02/2013 2013-02-03 00:00:00 03/02/2014 2014-02-03 00:00:00 03/03/2013 2013-03-03 00:00:00 04/02/2013 2013-02-04 00:00:00 05/02/2013 2013-02-05 00:00:00 select * dates date_varchar between '03/02/2013' , '04/02/2013' date_varchar date_datetime 03/02/2013 2013-02-05 00:00:00 03/02/2014 2014-02-03 00:00:00 03/03/2013 2013-02-05 00:00:00 04/02/2013 2013-02-03 00:00:00

sql date varchar between

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more