[SQL] - Datum query

[jimbo123]

Om de vervaldatum van een factuur te bepalen zou ik graag de factuurdatum nemen, en het aantal dagen van de betaaltermijn daarbij optellen.
Alle datums store ik als een varchar(8 ) als YYYYMMDD. (in dit voorbeeld omdat het makkelijk is).

Wanneer ik nu doe:
SELECT [FactuurDatum] + [BetalingsTermijn] from tabel

dan verwacht ik bijvoorbeeld te krijgen: "20080307" + "14" = "20080321"
Nu is dat natuurlijk ook correct.. maar een datum kan maximaal 20xxxx31 zijn, en soms 20xxxx30 en soms zelfs xx28 of xx29 (februari en schrikkeljaar).

Mijn probleem is dus dat op bovenstaande manier de datum ook "20080399" zou kunnen worden.

Iemand enig idee hoe ik nu toch dagen op zou kunnen tellen bij een YYYYMMDD formaat?

(geef aub niet een datetime of smalldatetime veldje gebruiken als oplossing, ik wil het echt op deze manier doen).

[ Voor 6% gewijzigd door jimbo123 op 07-03-2008 13:05 ]

[Acid__Burn]

SET DATEPART(DATUMVELD, DAY) + XX al geprobeerd? Dan update je namelijk een specifiek deel van het DATETIME veld.

[TeeDee]

vreselijke inkopper, maar gebruik een datetime veld

Je zou eventueel naar Convert kunnen kijken om het alsnog in datetime format te kunnen krijgen. Daarnaast kan je kijken of er iets met Datepart te regelen is.

[ Voor 16% gewijzigd door TeeDee op 07-03-2008 13:08 ]

[Verwijderd]

Ik zou zeggen converteren naar een echte datum, dagen erbij optellen en weer omzetten naar je string.
Waarom wil je persé geen datum type gebruiken? Je kan het tonen zoals je wilt.

[Acid__Burn]

Ik zit je verhaal nog eens na te kijken, maar bedoel je niet gewoon een SQL Statement met DATEDIFF? Dan krijg je namelijk het verschil tussen 2 DATETIME velden

[CodeCaster]

Ik zou een dateadd en een convert gebruiken, maar mijn SQL-kennis is niet dermate uitgebreid dat ik weet hoe ik een dergelijk probleem zou moeten tackelen. De code die ik zou gebruiken:

SQL:

DATEADD(day, 14, CONVERT(DateNow, DATETIME, 112)

Neemt niet weg dat ik een ander datatype zou gebruiken om m'n datums op te slaan.

[Verwijderd]

Wikipedia: Modular arithmetic

Beetje met substrings werken en dan moet je daar ook wel wat mee kunnen.

[Bitage]

Uhm, ik weet niet in welke omgeving je werkt, maar in PHP zou ik het zo doen:

PHP:

$months['01'] = "January";
$months['02'] = "February";
$months['03'] = "March";
$months['04'] = "April";
$months['05'] = "May";
// etc. etc.

// Eventueel even query opvangen bij $datum_from en/of $plus_days
$datum_from = 20080212;
$plus_days  = 21;

$year  = substr($datum_from,0,4);
$month = substr($datum_from,4,2);
$days  = substr($datum_from,6,2);

$timestamp = strtotime($days." ".$months[$month]." ".$year);
$new_time  = strtotime("+".$plus_days." days",$timestamp);

$to_yyyymmdd = strftime("%Y%m%d",$new_time);

echo $to_yyyymmdd; // Geeft 20080304

@TeeDee, sja, alleen in MySQL dan gaat dit natuurlijk niet werken.

[ Voor 12% gewijzigd door Bitage op 07-03-2008 13:28 ]

[TeeDee]

Bitage: SQL mijn waarde. SQL

[sig69]

jimbo123 schreef op vrijdag 07 maart 2008 @ 13:03:
...
(geef aub niet een datetime of smalldatetime veldje gebruiken als oplossing, ik wil het echt op deze manier doen).

Waarom, in godsnaam?

[pedorus]

Ik mag hopen dat dit een vroege 32 maartgrap is... Je kan dit werkend krijgen, maar dan nog is het een vreselijke oplossing...

[Knutselsmurf]

De oplossing blijft erg simpel. Als je kiest voor een datum-type kun je de database voor je laten rekenen. Wil je dat niet, dan moet je het zelf doen. En voor dat zelf doen biedt SQL weinig mogelijkheden. Het is immers geen volledige programmeertaal.

Gouden tip: gebruik het gereedschap waar het voor bedoeld is. Sla datums dus op in een datum-type.

[Acid__Burn]

Knutselsmurf schreef op vrijdag 07 maart 2008 @ 13:58:
En voor dat zelf doen biedt SQL weinig mogelijkheden. Het is immers geen volledige programmeertaal.

Dat is natuurlijk niet geheel waar. Ik werk dagelijks met SQL (soort van DBA), en je kunt erg veel met SQL. Alleen is het daar niet voor bedoeld (ben ik zeker met je eens). Maar SQL blijft een redelijk sterke taal.

Je kunt bijvoorbeeld je string zelf samenstellen (DATEPART), en daarna de LEFT 2 char's pakken, en daar iets bij optellen. Daarna die terugzetten in je string, en dan ben je er ook. Maar zoals eerder gemeld: hier is SQL niet voor gemaakt, en daarom is het zo traag.

[Verwijderd]

jimbo123 schreef op vrijdag 07 maart 2008 @ 13:03:
(geef aub niet een datetime of smalldatetime veldje gebruiken als oplossing, ik wil het echt op deze manier doen).

Ook ik ben heel benieuwd waarom je geen datetime veld hiervoor gebruikt.

[jimbo123]

Wanneer ik opsla in een smalldatetime veld dan kan ik inderdaad met een select convert query de datum op de gewenste manier in bijvoorbeeld een databound textbox zetten. (DD-MM-YYYY)

Het probleem is echter, dat wanneer ik de datum in de textbox aanpas, (van 06-03-2008 naar 06-03-2007 bijvoorbeeld) hij hem niet terug in de database kan zetten (foutmelding iets in de richting van "failed to convert nvarchar to smalldatetime")
Ik probeer hem dan gewoon via een update query terug te zetten. Zelfs via een soort van omgekeerde convert query lukt het dan niet meer.
Ik heb ongeveer 2 dagen zitten proberen om dat op te lossen en heb toen besloten om een varchar8 te gebruiken. Afgezien van het rekenen met dagen werkt het nu heel erg simpel.

De oplossing die Bitage geeft lijkt mij wel prettig.
Heb alleen geen idee hoe ik dat om kan zetten naar asp.net/vb.

[jimbo123]

Verwijderd schreef op vrijdag 07 maart 2008 @ 13:08:
Ik zou zeggen converteren naar een echte datum, dagen erbij optellen en weer omzetten naar je string.
Waarom wil je persé geen datum type gebruiken? Je kan het tonen zoals je wilt.

Hem converteren naar een echte datum en dagen erbij optellen krijg ik wel werkend.
Het terug zetten naar mijn varchar8 daarna echter niet.

Kun je een stukje code geven hoe je dit zou doen?

[TeeDee]

Ho... wacht even.

Je gebruikt ook asp.net?

Parameterized querys en het gebruik van het juiste datatype (in je .net en je DB) zorgt ervoor dat je helemaal geen probleem krijgt/hebt.

[Verwijderd]

Je kan het beste wel een datetime column type gebruiken voor je database. In je code ga je als volgt te werk.

code:

DateTime dateTimeVariable = (DateTime)textbox1.text;

SqlCommand commandInsert = new SqlCommand("insert into Data (DateTimeColumnName) values (@dateTimeValue)", _con);

commandInsert.Parameters.Add("@dateTimeValue", SqlDbType.DateTime).Value = dateTimeVariable;

Wat nog vééél makkelijker is: gebruik geen textbox op je form op de datum in te voeren, maar een DateTimePicker (staat ook in de toolbox). In dat geval is het volgende genoeg.

code:

SqlCommand commandInsert = new SqlCommand("insert into Data (DateTimeColumnName) values (@dateTimeValue)", _con);

commandInsert.Parameters.Add("@dateTimeValue", SqlDbType.DateTime).Value = dateTimePicker1.Value;

[Verwijderd]

Nou als je echt geen datetime-veld-achtig iets wilt gebruiken zou dit de oplossing zijn:

Maak een datum van je varchar veld dmv substr() en concat() en gebruik dat vervolgens als datum om mee te rekenen (deze datum zet je bijv in date_add() om de betalingstermijn erbij op te tellen).

jimbo123 schreef op zaterdag 08 maart 2008 @ 12:29:
[...]


Hem converteren naar een echte datum en dagen erbij optellen krijg ik wel werkend.
Het terug zetten naar mijn varchar8 daarna echter niet.

Kun je een stukje code geven hoe je dit zou doen?

date_format()

[ Voor 37% gewijzigd door Verwijderd op 08-03-2008 14:23 ]

[Verwijderd]

Tip: maak gebruik van functies die er al zijn. Dus begin niet opnieuw het wiel uit te vinden.

Een pure SQL oplossing beschrijf ik hieronder:

Eerst convert je het naar het date formaat (STR_TO_DATE), tel er dagen bij op (DATE_ADD) en converteer het weer terug naar het oorspronkelijke formaat: (DATE_FORMAT)

Het volgende werkt voor MYSQL 5:
SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('20080403','%Y%m%d'), INTERVAL 33 DAY),'%Y%m%d');

SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE( factuurdatum ,'%Y%m%d'), INTERVAL termijn DAY),'%Y%m%d') FROM table

Ik weet niet of dit standaard SQL functies zijn, dus in andere SQL databases zal je misschien andere functies moeten gebruiken.

Martijn,

[Acid__Burn]

Verwijderd schreef op zaterdag 08 maart 2008 @ 15:30:
Tip: maak gebruik van functies die er al zijn. Dus begin niet opnieuw het wiel uit te vinden.

Een pure SQL oplossing beschrijf ik hieronder:

Eerst convert je het naar het date formaat (STR_TO_DATE), tel er dagen bij op (DATE_ADD) en converteer het weer terug naar het oorspronkelijke formaat: (DATE_FORMAT)

Het volgende werkt voor MYSQL 5:
SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('20080403','%Y%m%d'), INTERVAL 33 DAY),'%Y%m%d');

SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE( factuurdatum ,'%Y%m%d'), INTERVAL termijn DAY),'%Y%m%d') FROM table

Ik weet niet of dit standaard SQL functies zijn, dus in andere SQL databases zal je misschien andere functies moeten gebruiken.

Martijn,

STR_TO_DATE is geen standaard MS-SQL statement. Die zal je dus zelf moeten maken met CAST en DATEPART:

SQL:

SELECT CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)
+ '-' +
CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)
+ '-' +
CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)

Zo krijg je de datum als string, en die kan je ook weer zo inserten in een datetime kolom. Je kan namelijk een datum ook inserten/updaten als string. Heb ik in dit topic ook al terug zien komen dat dit niet mogelijk zou zijn...?

[Janoz]

jimbo123 schreef op zaterdag 08 maart 2008 @ 12:26:
Ik heb ongeveer 2 dagen zitten proberen om dat op te lossen en heb toen besloten om een varchar8 te gebruiken. Afgezien van het rekenen met dagen werkt het nu heel erg simpel.

Tja, dan is dit dus eigenlijk je probleem. Los dat dan fatsoenlijk op. Nu freubel je een prust oplossing in elkaar omdat je je insert en update acties niet fatsoenlijk geïmplementeerd krijgt, en vervolgens ga je alle problemen die je daardoor veroorzaakt stuk voor stuk los proberen op te lossen. Ik verzeker je dat dit dagen probleem niet het laatste probleem zal zijn dat je tegen gaat komen dor deze brakke ontwerp beslissing...

[whoami]

jimbo123 schreef op vrijdag 07 maart 2008 @ 13:03:


(geef aub niet een datetime of smalldatetime veldje gebruiken als oplossing, ik wil het echt op deze manier doen).

waarom ?
Waarom wil je dit op een verkeerde manier oplossen ?
Waarom wil je bewust het verkeerde datatype gebruiken om een bepaald gegeven in op te slaan ?

Nu het volledige topic gelezen: met Janoz, en met de mensen die zeggen dat je parametrized queries moet gebruiken. Dit is niet alleen veiliger, maar lost ook je datum problemen nog eens op.
Dat je een error krijgt 'cannot convert nvarchar to datetime' is logisch als je een datetime veld gebruikt om je datum in op te slaan, en je de string die in een textbox staat, wilt gebruiken in je query door die string (die eigenlijk een datum is) gewoon als string in je query te plakken. Dan moet je idd je datum als YYYYMMDD in je query plakken, of, nog beter, gebruik maken van parameters, zoals het voorbeeld dat JMfx toont.

[ Voor 44% gewijzigd door whoami op 10-03-2008 09:16 ]

[sig69]

Verwijderd schreef op zaterdag 08 maart 2008 @ 14:11:
Je kan het beste wel een datetime column type gebruiken voor je database. In je code ga je als volgt te werk.

code:

DateTime dateTimeVariable = (DateTime)textbox1.text;
...

DateTime.TryParse() of DateTime.TryParseExact() lijkt me wat netter dan een string direct casten naar een DateTime.