CODEWITHSUNDEEP
sqlasp-classic
Spanky
Spanky

Reputation: 709

Change date format in SQL for ASP

I have the following code:

"SELECT top 1 * FROM CensusFacility_Records WHERE Division_Program = 'Division 1' AND JMS_UpdateDateTime = '" & date & "'"

The date format in column JMS_UpdateDateTime is: 8/22/2013 12:00:07 AM How can I make sure that my "date" in the query is converted to the correct time format?

My date variable in my SQL query is a real/date time. I would like for it to match the format within the JMS_UpdateDateTime field.

Upvotes: 0

Views: 1500

Answers (2)

Steve Chambers
Steve Chambers

Reputation: 39424

The format of your SQL DateTime is actually a bit of a red herring here - it can be displayed in any way the front end (e.g. Management Studio) chooses. The important thing here is that your date variable is in an unambiguous format. With this in mind I'd recommend using the ISO 8601 date/time format, e.g. date.ToString("o") (assuming date is a DateTime).

Upvotes: 1

Joe Enos
Joe Enos

Reputation: 40393

If your database table is small enough, you can cast the value to an actual datetime inside your query, and pass the value in as a datetime parameter, so you're comparing dates instead of comparing strings.

If your table has a decent amount of records, don't do this, because it will be a performance hog.

SELECT top 1 *
FROM CensusFacility_Records
WHERE Division_Program = 'Division 1' 
AND cast(JMS_UpdateDateTime as datetime) = @dateParam

I believe SQL Server will be able to read the string that's in your database and automatically cast it properly, assuming your server settings are standard.

But in any case, use parameterized SQL instead of passing in a string like you've got.

Upvotes: 1

Related Questions