February 8, 2018 at 6:46 am
Hi ,
I have to query a column with this date format : 16-Nov-2017 00:000. The 00.00 refers to time stamp that has been default to zeros. I need to query date between 16 Nov and 17 Nov.
I tried several ways and in the end do the like clause ‘%2017%’ but there are too many data. I do not need to covert date format.
Thank you for any advice
February 8, 2018 at 6:58 am
corgi_77 - Thursday, February 8, 2018 6:46 AMHi ,I have to query a column with this date format : 16-Nov-2017 00:000. The 00.00 refers to time stamp that has been default to zeros. I need to query date between 16 Nov and 17 Nov. I tried several ways and in the end do the like clause ‘%2017%’ but there are too many data. I do not need to covert date format. Thank you for any advice
What is the data type of the column, in the table DDL?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2018 at 7:27 am
corgi_77 - Thursday, February 8, 2018 6:46 AMI do not need to covert date format.
I assume you meant convert.
Unless you have dates in date format, or in some YYYYMMDD (string or integer) format that facilitates sorting, you will need to perform a conversion before you can do any 'between' queries.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 8, 2018 at 7:51 am
You'll need to format the string into something SQL Server can parse, https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql it looks like you can use type 107 to convert into a date and do your date calcs from there.
February 8, 2018 at 10:32 am
WHERE column_name LIKE '1[67]-Nov-2017%' /* or '%1[67]-Nov-2017%' if the column doesn't start with the day */
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 8, 2018 at 5:26 pm
The format written in the data dictionary DATE. No format stated.
February 8, 2018 at 5:54 pm
corgi_77 - Thursday, February 8, 2018 5:26 PMThe format written in the data dictionary DATE. No format stated.
Data dictionary? Is this the data type of the column when you script the CREATE TABLE statement for the table?
February 9, 2018 at 3:43 am
Hi
I have tried the above solutions and the syntax provided. It drew a blank result.
Try this also :
Select b.deliverydate
From ordertable as b
Where to_char (b.deliverydate,’DD-Mon-YYYY’) = ‘16-Nov-2017’
- no results also
- data dictionary is like a dictionary created by own with variables’ definitions.
February 9, 2018 at 3:53 am
corgi_77 - Friday, February 9, 2018 3:43 AMHi I have tried the above solutions and the syntax provided. It drew a blank result.Try this also : Select b.deliverydate From ordertable as b Where to_char (b.deliverydate,’DD-Mon-YYYY’) = ‘16-Nov-2017’ - no results also - data dictionary is like a dictionary created by own with variables’ definitions.
Have you tried posting your question in an oracle forum section?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply