| |

VerySource

 Forgot password?
 Register
Search
View: 1036|Reply: 7

Find a sql statement?

[Copy link]

1

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-2-22 13:30:01
| Show all posts |Read mode
2016-12-14 08:26 |
| 2016-12-15 10:19 |
| 2016-12-20 08:50 |
| 2016-12-20 09:21 |
| 2016-12-20 10:13 |
| 2016-12-20 10:51 |
| 2016-12-21 14:15 |
| 2016-12-21 15:59 |
| 2016-12-25 09:37 |
| 2016-12-26 12:09 |
| 2016-12-27 11:44 |
| 2016-12-30 09:22 |
| 2016-12-30 09:48 |
| 2017-01-04 09:09 |
| 2017-01-04 11:00 |
| 2017-01-05 09:18


My field name is ntime type is varchar and I want to find two statements
One is to select all 2016 (from 2016-12-14 08:26 to 2016-12-30 09:48) and the other is to select all 2017 (from 2017-01-04 09:09 to 2017- 01-05 09:18) younger brother, beginners, etc.
Reply

Use magic Report

0

Threads

66

Posts

27.00

Credits

Newbie

Rank: 1

Credits
27.00

 China

Post time: 2020-5-3 14:45:01
| Show all posts
select * from t where left (ntime, 4) = '2016'
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-5-3 16:15:01
| Show all posts
select * from tablename where substring (ltrim (replace (ntime, '|', '')), 1,4) = 2016
select * from tablename where substring (ltrim (replace (ntime, '|', '')), 1,4) = 2017
Reply

Use magic Report

0

Threads

88

Posts

55.00

Credits

Newbie

Rank: 1

Credits
55.00

 China

Post time: 2020-5-3 16:45:01
| Show all posts
select * from tablename where ntime between '2016-12-14 08:26' and '2016-12-30 09:48'
select * from tablename where ntime between '2017-01-04 09:09' and '2017-01-05 09:18'
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-5-3 17:15:02
| Show all posts
select * from tablename where substring (ltrim (replace (ntime, '|', '')), 1,4) = '2016'
select * from tablename where substring (ltrim (replace (ntime, '|', '')), 1,4) = '2017'
Reply

Use magic Report

0

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-5-3 19:00:01
| Show all posts
select * from tablename where ColumnName between '2016-12-14 08:26' And '2016-12-30 09:48' Or ColumnName Between '2017-01-04 09:09' And '2017-01-05 09: 18 '
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-5-4 09:30:01
| Show all posts
Is the above data a record?
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-5-4 19:15:01
| Show all posts
create table T (ntime varchar (1000))
insert T select
'2016-12-14 08:26 |
| 2016-12-15 10:19 |
| 2016-12-20 08:50 |
| 2016-12-20 09:21 |
| 2016-12-20 10:13 |
| 2016-12-20 10:51 |
| 2016-12-21 14:15 |
| 2016-12-21 15:59 |
| 2016-12-25 09:37 |
| 2016-12-26 12:09 |
| 2016-12-27 11:44 |
| 2016-12-30 09:22 |
| 2016-12-30 09:48 |
| 2017-01-04 09:09 |
| 2017-01-04 11:00 |
| 2017-01-05 09:18 '



declare @sql varchar (8000)
select @ sql = 'select * from (select ntime =' ''
select @ sql = @ sql + replace (replace (replace (ntime, char (13), ''), char (10), ''), '||', '' 'union all select' '') from T
select @ sql = @ sql + '' ') a where year (ntime) = 2016'
exec (@sql)

--result
ntime
-----------------
2016-12-14 08:26
2016-12-15 10:19
2016-12-20 08:50
2016-12-20 09:21
2016-12-20 10:13
2016-12-20 10:51
2016-12-21 14:15
2016-12-21 15:59
2016-12-25 09:37
2016-12-26 12:09
2016-12-27 11:44
2016-12-30 09:22
2016-12-30 09:48
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list