SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to query with this date format ?


How to query with this date format ?

Author
Message
corgi_77
corgi_77
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 36
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
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165480 Visits: 21618
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
Exploring Recursive CTEs by Example Dwain Camps
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214433 Visits: 24734
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ZZartin
ZZartin
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25592 Visits: 17268
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
ScottPletcher
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84272 Visits: 9522
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
corgi_77
corgi_77
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 36
The format written in the data dictionary DATE. No format stated.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391033 Visits: 42820
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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
corgi_77
corgi_77
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 36
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
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165480 Visits: 21618
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search