How to query with this date format ?

Question

How to query with this date format ?

corgi_77

SSC Enthusiast

Points: 123
More actions
February 8, 2018 at 6:46 am

#327238

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

corgi_77 - Thursday, February 8, 2018 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

What is the data type of the column, in the table DDL?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Phil Parkin SSC Guru Points: 247158 More actions · Answer 2

corgi_77 - Thursday, February 8, 2018 6:46 AM
I 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.

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

corgi_77 SSC Enthusiast Points: 123 More actions · Answer 5

The format written in the data dictionary DATE. No format stated.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

corgi_77 - Thursday, February 8, 2018 5:26 PM
The 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?

corgi_77 SSC Enthusiast Points: 123 More actions · Answer 7

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

corgi_77 - Friday, February 9, 2018 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.

Have you tried posting your question in an oracle forum section?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

How to query with this date format ?

Cookies on SQLServerCentral