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
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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 (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)

Group: General Forum Members
Points: 129951 Visits: 21239
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 (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)

Group: General Forum Members
Points: 170169 Visits: 23517
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18052 Visits: 15675
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 (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63849 Visits: 8706
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
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

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

Group: General Forum Members
Points: 300799 Visits: 41483
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
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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 (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)

Group: General Forum Members
Points: 129951 Visits: 21239
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