0

Lots of stuff on the web about this one but i just cant get anything to work correct.

I need to find everything from a particular table where the date from my parameter is found in the 'timestamp'datetime on this table

So lets say. 

Select g.* 
From egtable..headerinfo as g
WHERE g.istatus BETWEEN 5 AND 10 AND
REPLACE(LEFT(CONVERT (varchar, timestamp, 101),10),' ','-') = convert(datetime,'<%parameterDate%>')

The timestamp displays 'as yyyy-mm-dd hh:mm:ss' my parameter date would 'be mm/dd/yyyy'

My goal is to ultimately strip out the time, convert the date and then compare to parameters. Any help on this would be greatly appreciated. I've tried countless combinations and nothing seems to work :(

Thanks B

Szymon
  • 42,577
  • 16
  • 96
  • 114
Na'te
  • 29
  • 7
  • There is no need to convert both, its best to convert the incoming data (parameter) to the SQL format, and not the other way around as the indexes used and the search used by SQL would be faster – Noam Rathaus Nov 25 '13 at 11:49
  • 1
    Is your code expected to work on MS SQL ? – Noam Rathaus Nov 25 '13 at 11:49
  • Yes. I never really thought of it that way. So i can convert the parameter to my date + 23:59:59.. If anyone can advise how to do that I would be grateful. Only starting out with SQL and its a bit of a learning curve from VBA. – Na'te Nov 25 '13 at 11:55
  • I don't think you need to convert, as I said below, most SQL servers will take a date-only field and add midnight to it, or the other way, take a datetime field and drop the time portion if verified against a only-date field – Noam Rathaus Nov 25 '13 at 11:56
  • If you're wanting some date with `23:59:59` added to it, it's almost always a sign that you should be switching to using an *exclusive* endpoint for a time period (use `<` rather than `<=` or `BETWEEN`, and just add a whole day on) since you usually *don't* want to exclude things that occur at `23:59:59.437` – Damien_The_Unbeliever Nov 25 '13 at 11:57

2 Answers2

0

Assuming you are using MySQL, since you didn't state what you used you can use this:

Select g.* 
From egtable..headerinfo as g
WHERE g.istatus BETWEEN 5 AND 10 AND
 timestamp = MAKEDATE(SUBSTRING('<%parameterDate%>', 4, 2), 
                      SUBSTRING('<%parameterDate%>', 1, 2), 
                      SUBSTRING('<%parameterDate%>', 6, 4))

MAKEDATE in MySQL creates a date field for you based on receiving day, month, year input

Noam Rathaus
  • 5,405
  • 2
  • 28
  • 37
0

If you're using SQL Server (your query looks like that) and your SQL Server version number is 2008 and higher, you can easily get rid of the time part of datetime by casting it to date

cast(datetimecolumn as date)

In your case it will be:

Select g.* 
From egtable..headerinfo as g
WHERE g.istatus BETWEEN 5 AND 10 AND
cast([timestamp] as date) = cast('<%parameterDate%>' as date)
Szymon
  • 42,577
  • 16
  • 96
  • 114
  • Most SQL don't require that case prior to comparison, they do it on their own, doesn't MS SQL handle it "automatically"? – Noam Rathaus Nov 25 '13 at 11:53
  • @nrathaus If you're comparing `datetime` types, you always compare both date and time. Casting to `date` is an easy way to compare just date. – Szymon Nov 25 '13 at 11:57
  • Sorry for confusion, I'm using SQL Server. How do I add the cast part into the code. I just had a quick try and obviously im not doing it correctly. – Na'te Nov 25 '13 at 12:02
  • 1
    Much appreciate your feedback. I was so closse yet so far with this the other day. Seems to be working spot on now though. Thanks – Na'te Nov 25 '13 at 12:20
  • One more quick question on this. Does the date format of my parameter not count at all? It doesn't appear too but i just want to be sure? – Na'te Nov 25 '13 at 12:34
  • It has to be in a format that SQL Server will be able to convert to `date`. Or best would be to use parameters in your query (also to avoid SQL injection). – Szymon Nov 25 '13 at 12:35