Question SQL DateTime ....

[r3plica]

I hate the datetime datatype. I always seem to run into issues with it.
Some times it works and other times I have to piss around with converting to strings and then formatting it manually into a format that my sql server will allow.

Here is my issue, surely there is an easy way (in sql) to fix this....

in vb.net I have some code that gets a date from a date picker and stores that date as string.
If I step through the code, I get this:

"03/05/2010"

In SQL I have a stored procedure that accepts a parameter which is a datetime data type.
For most days this was working fine, until the 03/05/2010 when it didnt work.
For the days after, it was fine again.

Stepping through the stored procedure I can see that the text is displayed as this:

2010-05-03 00:00:00.000

so for some reason it is not getting understood by SQL properly.
Does anyone have an easy fix to this. I know how to fix it by casting as a string and swapping the month/day/year around, but surely there must be an easier way?

 

[InertiaM]

Surely there must be an easier way?

There is Dont convert it to a string at all, keep the DateTimePicker value as a date and pass that straight to the stored procedure. Simples

 

[jmcilhinney]

The datetime data type is absolutely no issue at all. Issues only arise with format when converting to or from a string. If you never convert to a string, which you never should except for display or serialisation purposes, you never have an issue. As has been suggested, there is absolutely no reason to be using strings to work with dates in databases. You should be using parameters at all times. Follow the Blog link in my signature and check out my post on ADO.NET parameters.

 

[r3plica]

I see what you are saying, but I always get an issue if I keep the date format as datetime in code.
The reason (I think) is to do with regional settings, because in .Net the format shows as:

2010-05-03 00:00:00.000

but in SQL I think it expects 2010-03-05 instead....

 

[jmcilhinney]

I see what you are saying, but I always get an issue if I keep the date format as datetime in code.
The reason (I think) is to do with regional settings, because in .Net the format shows as:

2010-05-03 00:00:00.000

but in SQL I think it expects 2010-03-05 instead....

You're missing the point. Dates have no format. It is ONLY if you convert between date and text or vice versa that format can possibly be an issue, so if you're having format issues then you must be converting between date and text. Have you read my blog post? All should be clear after doing so.

 

[r3plica]

I did read your blog (John McIlhinney's .NET Developer Blog: Search results for ado parameters) and it didnt really explain anything about dates.
I get your point now though and I actually solved the issue. I was using the DATETIME datatype in code and in SQL and the actual error was due to my SQL statement which was:

	SELECT @PeriodDateID = ID
	FROM dbo.PeriodDate
	WHERE DateFrom < @Date
	AND DateTo > @Date

and should have been

	SELECT @PeriodDateID = ID
	FROM dbo.PeriodDate
	WHERE DateFrom <= @Date
	AND DateTo >= @Date

A silly mistake I know but the question was a general question because I have had issues in the past.
I didnt know that formatting issues only occur if turning dates into strings, etc and that they do not exist if you use the datetime datatype.
That makes things a lot simpler so cheers for that.

 

[jmcilhinney]

I did read your blog (John McIlhinney's .NET Developer Blog: Search results for ado parameters) and it didnt really explain anything about dates.

I'm not quite sure how you can say that, given that every code example except the last on includes DateOfBirth, which is a date. Given that your issue had nothing to do with formatting anyway though, I guess the point is moot.