Convert string to date SQL Server

Question

I have a query when I need to convert a string to datetime, I use

CONVERT(DATETIME, '')

but it's still not working. My column fechac is of type Datetime.

If I pass in for example '2019-11-20 00:03:56.120', the query works but I don't need the time because is a parameter from an application web and I send the date example '2019-11-20'. Thanks!

This is my query:

SELECT * 
FROM table 
WHERE cb.fechac = CONVERT(DATETIME, '2019-11-20');

Use the format yyyyMMdd. With the datetime datatype yyyy-MM-dd is ambiguous. — Thom A
– Thom A ♦, Commented Nov 25, 2019 at 18:31

Salman Arshad · Accepted Answer · 2019-11-26 07:20:29Z

2

Use the format yyyyMMdd. With the datetime datatype yyyy-MM-dd is ambiguous. Otherwise, use a style code:

CONVERT(datetime,'2019-11-20',126);
CONVERT(datetime,'20191120')

Reading a lot through the lines in the comments here, however, are you actually after..?:

WHERE fechac >= '20191120'
  AND fechac < '20191121'

Final reading through the lines. it appears the OP is actuaklly passing a parameter (I assume of the data type date), therefore...

WHERE fechac >= @fechac
  AND fechac < DATEADD(DAY, 1, @fechac)

edited Nov 26, 2019 at 7:20

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

answered Nov 25, 2019 at 18:32

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Rodrigo Over a year ago

Thank you, the query sintaxis is fine but give me 0 rows when I execute the query

Thom A Over a year ago

Then that would mean there are no rows where the time is exactly 2019-11-20T00:00:00.000 @Rodrigo .

Rodrigo Over a year ago

You're right there isn't, in that case how could I do it? I need the field fechac to be datetime but in this query I don't need the time because I use the time for auditory

Thom A Over a year ago

"Because I need the time but i don't need for this query" That doesn't make sense. You either do, or you don't.

Salman Arshad Over a year ago

The part OP is actually after is buried inside the question!

|

Gordon Linoff · Accepted Answer · 2019-11-25 18:31:47Z

0

This should work on any system, regardless of internationalization settings:

WHERE cb.fechac=convert(DATE,'20191120');

answered Nov 25, 2019 at 18:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

dougp · Accepted Answer · 2019-11-25 19:14:35Z

-1

SELECT *
FROM table cb
WHERE cast(cb.fechac as date) = convert(date, '2019-11-20', 23)

answered Nov 25, 2019 at 19:14

dougp

3,1631 gold badge14 silver badges48 bronze badges

4 Comments

Rodrigo Over a year ago

Thank you! That is the solution

Thom A Over a year ago

This isn't the best idea at all... This can have signicant performances impacts, @Rodrigo . Use proper date logic with >= and <. Cast to date is sargable but is it a good idea?

dougp Over a year ago

@Larnu,

where cb.fechac between convert(datetime, '2019-11-20T00:00:00.000', 126) and convert(datetime, '2019-11-20T23:59:59.999', 126)

will also work. It looks like this will involve half as many reads (so twice as fast). I suppose it will come down to how often the code needs to be run and how clean the code should look for the next person who must support it.

Thom A Over a year ago

Generally, using BETWEEN is just a bad idea when using dates that have a time component, @dougp . The >= AND < logic is much more concise. Especially as you can easily write things like DateColumn >= @Date and DateColumn < DATEADD(DAY, 1, @Date), rather than DateColumn BETWEEN @Date AND DATEADD(MILLISECOND, -3, DATEADD(DAY, 1, @Date)) (assuming DateColumn and @date is a datetime)

Collectives™ on Stack Overflow

Convert string to date SQL Server

3 Answers 3

13 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related