sql server - Converting a string to a date time in SQL -

-

i'm importing data different system , datetime stored string in format:

20061105084755es

yyyymmddhhmmss(es/ed) es est , ed edt.

i have query table last 30 days. i'm using conversion query:

select convert(     datetime,     left(cdts, 4)+'-'+substring(cdts, 5,2)+'-'substring(cdts, 7,2)+' '+substring(cdts, 9,2) +':'+substring(cdts, 11,2)+':'+substring(cdts, 13,2) dt tb1 dt < getdate()-30

i'm looking more efficient query reduce time taken. table has around 90 million records , query runs forever.

no calculation @ runtime going speed query if performing calculation , need filter against result of calculation - sql server forced perform table scan. main problem you've chosen store dates string. variety of reasons, terrible decision. string column indexed @ least? if so, may data last 30 days:

declare @thirtydays char(8); set @thirtydays = convert(char(8),dateadd(day,datediff(day,0,getdate()),0)-30,112);  select ... cdts >= @thirtydays;

if need return data of history except past 30 days, isn't going either, because unless pulling data indexed column, efficient approach retrieving of data in table use clustered index scan. (if retrieving narrow set of columns, may opt index scan, if have covering index.) so, bottleneck in of these scenarios not formula can fix, rather time takes retrieve large volume of data, transmit on network, , render on client.

also, aside, can't this:

select + b c dbo.somewhere c > 10;

c doesn't exist in dbo.somewhere, expression derived in select list. select list parsed second last (right before order by), can't reference in where clause doesn't exist yet. typical workarounds repeat expression or use subquery / cte.


Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more