pass correct date in sql string, tried all the obvious

[leanpilar]

I normally put all constant in module so they can be used in all class module.
did you try your system cdate("8/17/2015 16:39:52") if it get the correct date?
because on my it get 17.08.2015. 16:39:52
and format(cdate("8/17/2015 16:39:52"),JetDateFmt ) I get #08/17/2015#

 

[dexcelle]

I don't get that far unfortunately:

insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2,  format(F1, JetDateFmt ), F3, Replace(F4,".",","), Replace(F5,".",","), F6, Replace(F7,".",","), F8 from AntroImport

Result is an errormessage (#3061): Too few parameters. expected 1. I do see though that the constant value is being applied.

 

[leanpilar]

if your is in F1 as string and not as date try replace format(F1, JetDateFmt )
with format(cdate(F1), JetDateFmt )

 

[dexcelle]

insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht,  Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2,   format(cdate(F1), JetDateFmt ), F3, Replace(F4,".",","),  Replace(F5,".",","), F6, Replace(F7,".",","), F8 from AntroImport

Same error

 

[leanpilar]

with insert into I think you have to remove the # from the constant like
format(cdate(F1),"dd/mm/yyyy")

 

[dexcelle]

I think I've sorted the insert value itself

"insert into dbo_Skilldata (SpelerID, Wiel, Datum, SkillID, AbsScore, SkillScore, Snapshot, TestPeriode) select CLng(F2), dbo_spelers.wiel, format(cdate(F1), ""mm/dd/yyyy""), " & VSkillid & " , " & vKolomscore & " , dbo_Normering.skillscore, format(now(), ""YYYYMMDD""), Periodenummer .....

Where it now fails is the where-clause, that has retrive a period number if the inserted data is between start and end of the period.
Fairly simple you would think, but not in the MS world. the usage of hashes and double quotes to build the string apparently require 10 years of study first. Sorry for this frustration, but the overcomplicated date shizzle is killing this project.

"insert into dbo_Skilldata (SpelerID, Wiel, Datum, SkillID, AbsScore, Snapshot, Testperiode) select F2, dbo_spelers.wiel, format(cdate(F1), ""mm/dd/yyyy""), " & VSkillid & " , " & vKolomscore & " , format(now(), 'YYYYMMDD'), periodenummer from " & importtbl & ", dbo_spelers, dbo_testperioden where dbo_spelers.spelerid = F2 and ""#"" format(cdate(F1), ""mm/dd/yyyy"") ""#"" between ""#"" format(cdate(dbo_testperioden.datumvan), ""mm/dd/yyyy"") ""#"" and ""#"" format(cdate(dbo_testperioden.datumtot), ""mm/dd/yyyy"")" & "#" & ""

bottom line question: how to make the Between statement work?? using the format only isn't enough as yet again it swaps month and day

 

[dexcelle]

[SOLVED] pass correct date in sql string, tried all the obvious

The insert of the date itself works fine now and for the retrieval of the period number I've found a workaround: a sql server stored procedure does an update using a simple, unformatted between statement. works perfect.

 

[Isaac]

Actually both posts about how sql server 'expects' dates are wrong. Sql server doesn't expect dates any one particular way - it depends on the setup of the database and region and locale. One of the best things you can do if you don't happen to know is to say, where ___ = cast('YYYY-MM-DD' as date)
not sure if this will help all your formatting dynamic sql problems or not.

lastly, if you are inserting INTO a sql server table, you don't want those # signs. those are for access only..............

 

[Minty]

@pisorsisaac@gmail.co You can use that same layout in access, just surround it with #2020-04-25# - it's my prefered way of formatting as I know exactly what I'm seeing.
'2020-04-25' Is what a SQL server passthrough would expect to see.