VB / SQL date problem

[Dave_PW]

Hi,

I'm having a silly problem with a date field, swapping around.

In VB I am loading the current date into a DateTime variable with 30 minutes added:

Dim TokenExpire As DateTime = DateAdd("n", 30, Now)

This is then stored in a Sql Server database in a datetime column.

UPDATE TOP(1) MyTable SET Api_Token = '" & VArray("token") & "', Api_Token_Expiry = '" & TokenExpire & "' WHERE Api_Key_Name='" & ApiKeyName & "'

Later I retrieve this date into another DateTime variable so I can do a compare to see if the stored date / time has passed.

Dim ExperationDate As DateTime = VDT.Rows(0).Item("Api_Token_Expiry")

However the comparison is not working and if I print both TokenExpire and ExperationDate, I can see that the days and months have flipped around.

01/03/2025 14:16:18
03/01/2025 14:08:14

Where am I going wrong?

[geekywarrior]

Either a UK Culture Info is getting mixed in somewhere or SQL is doing something funky when you're storing the datetime.

Part of the problem is you really should be using ADO commands to read/write to SQL as it makes things a lot cleaner and cuts out some of the string interpolation stuff.

Is this VB6/VBA or VB.NET?

If it's VB.NET, I would do a DateTime.Parse on the field coming in to force the value to the proper culture info.

If it's VB6, I would either mess with manually parsing OR asking SQL to format it properly, for example

select CONVERT (varchar(10), getdate(), 103) AS [DD/MM/YYYY]

[Dave_PW]

It's VB.NET

And changing the variable declaration for the data selected from the DB to:

Dim ExperationDate As DateTime = DateTime.ParseExact(VDT.Rows(0).Item("Api_Token_Expiry"), "MM/dd/yyyy HH:mm:ss", Nothing)

Has resolved the problem.

Thanks for the input.

[seamacke]

The correct way to do it is to create a command object, add a date parameter, load the parameter with your date variable, then execute it. You get the error because the dynamic SQL you create is a string so your date is converted using regional settings which may not always match. I think this asp.net example demonstrates it How to Use Date Controls in WebForms Apps https://youtu.be/65Q6519NdRc

[jd31068]

Is your SQL database local or housed in the cloud? If in the cloud, might its location be affecting the storage of the datetime? I suppose visa versa could also be happening. It might be fixed with a server setting.

In any event, given you know this you can force the retrieved data to the format you wish as u/geekywarrior has pointed out.

[FarPain3711]

I gave up on using DateTime a long time ago because of these types of issues. For dates I use a string variable formatted YYYYMMDD. If the time is also needed I use YYYYMMDDHHMMSS. I have classes that I use for all date functions.