CODEWITHSUNDEEP
sql-serverdatetime
Matias Masso
Matias Masso

Reputation: 1970

Build a SQL query with a DateTime parameter

I can successfully use a Query with a Date parameter in string format as

SELECT * FROM ORDERS WHERE [DATE]='20160209'

but I haven't seen any sample of a Query specifying a DateTime parameter in string format. Next samples are rejected by Microsoft SQL Server Management Studio:

SELECT * FROM ORDERS WHERE [DATE]='20130523T153500000Z'
SELECT * FROM ORDERS WHERE [DATE]='2013-05-23T15:35:00:000Z'

I know this is not a good practice and I should pass DateTime values rather than strings, but sometimes it is useful for debugging.

What is the right format to include a string formatted datetime on a SQL query?

Upvotes: 1

Views: 3159

Answers (3)

user2065377
user2065377

Reputation: 458

No so sure where you've got those date formats... This one '2013-05-23T15:35:00:000Z' just doesn't seem to be right. I haven't seen that nanoseconds were delimited by a ':' character. It is usually a decimal of a second, so '2013-05-23T15:35:00.000Z' is a better format and it works:

select convert(DateTime,'2013-05-23T15:35:00.000Z')

As for the other, you might need to do the parsing yourself:

select CONVERT(DATETIME,LEFT('20130523T153500000Z',4)+SUBSTRING('20130523T153500000Z',5,2)+SUBSTRING('20130523T153500000Z',7,2))

hope this helps.

Upvotes: 1

Dan Hogan
Dan Hogan

Reputation: 468

Can you just do something like this?

SELECT *
FROM ORDERS
WHERE [DATE] = CONVERT(DATETIME,'20130523T153500000Z')

As long as the string is in a workable format.

Upvotes: 1

CaptainMarvel
CaptainMarvel

Reputation: 427

If it's just for debugging, you might do something like:

DECLARE @val VARCHAR(25)

-- Easily swapped out with different testing values
SET @val = '20130523T153500000Z'

SELECT * 
FROM Orders
WHERE [DATE] = CAST(@val AS DATETIME)
-- You could also use CONVERT

Upvotes: 0

Related Questions