how to convert varchar(max) of daetime field

Question

Hi

i have a query i post here. please correct it. 

when i use this query i got convert error came. iam using 2008 r2.  Both fields having a datatype is  varchar(max) in the table.

                string date = "CONVERT(datetime, FourthContent)";
                string tdate= "Convert(datetime, EleventhContent)";
               // order = "ORDER BY CASE WHEN (" + date + " < GetDate()) THEN 1 ELSE 0 END,(CONVERT(datetime, FourthContent))DESC  ";
                order = "ORDER BY CASE WHEN (" + tdate + " == null) THEN CONVERT(" + date + " < GetDate()) ELSE CONVERT("+ tdate +" < GetDate) END";
            

 i want result like that.

2012-08-18 00:00:00.000

2012-08-30 00:00:00.000

2012-09-25 00:00:00.000

2012-11-13 00:00:00.000

2012-08-17 00:00:00.000

Answer 1

You need to specify the style of the date/time literal stored in your VARCHAR(MAX) column.

Answer 2

Hi

After my own testing, I found you need to test the whole the query made from Application in SQL Server Management Studio or etc.

The last ORDER BY clause may have convert error, I  thought.

I hope that helps

---

Best Regards, Jungsun Kim

Answer 3

First of all, instead of == null it should be IS NULL. Secondly, what you'll get as ORDER after you expand it? It looks like you already have CONVERT, so why you're doing an extra convert?

Can you post your full final query string and also the structure of your tables, some input data and desired output?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

What are u trying to compare in case condition whteger the date is less than Getdate()??

---

Please have look on the comment

Answer 5

Hi

i have a query i post here. please correct it. 

when i use this query i got convert error came. iam using 2008 r2.  Both fields having a datatype is  varchar(max) in the table.

                string date = "CONVERT(datetime, FourthContent)";
                string tdate= "Convert(datetime, EleventhContent)";
               // order = "ORDER BY CASE WHEN (" + date + " < GetDate()) THEN 1 ELSE 0 END,(CONVERT(datetime, FourthContent))DESC  ";
                order = "ORDER BY CASE WHEN (" + tdate + " == null) THEN CONVERT(" + date + " < GetDate()) ELSE CONVERT("+ tdate +" < GetDate) END";
            

 i want result like that.

2012-08-18 00:00:00.000

2012-08-30 00:00:00.000

2012-09-25 00:00:00.000

2012-11-13 00:00:00.000

2012-08-17 00:00:00.000

your order by clause seems to be wrong, you can not use less than in order by. You can make your own cases based on your need and use date, tdate variables wherever you want :

ORDER BY CASE	
WHEN ( Convert(datetime, EleventhContent) IS null ) THEN CONVERT(datetime, FourthContent) // or whatever
WHEN ( CONVERT(datetime, FourthContent) < GetDate() ) THEN CONVERT(datetime, FourthContent) // or whatever
WHEN ( Convert(datetime, EleventhContent) < GetDate() ) THEN CONVERT(datetime, EleventhContent) // or whatever
ELSE CONVERT(datetime, EleventhContent) // or whatever

regards

joon

Answer 6

i executed your query. i got error.

i show my query for your reference.

select FourthContent,EleventhContent from  Atten_Table  where ModuleId= 77 and flag=1 and status =1 order by CASE
 WHEN ( Convert(datetime, EleventhContent) IS null ) THEN CONVERT(datetime, FourthContent) 
 WHEN ( CONVERT(datetime, FourthContent) < GetDate() ) THEN CONVERT(datetime, FourthContent)
 WHEN ( Convert(datetime, EleventhContent) < GetDate() ) THEN CONVERT(datetime, EleventhContent)
 ELSE CONVERT(datetime, EleventhContent)

 i metion  this information for your reference.  FourthContent and EleventhContent  data type is varchar(max). 

 please give me a solution for this problem. Past 5 days i struggle with this issue.... i cant change my table structure. please guide me...........

Answer 7

You're missing END keyword for the case statement, e.g. the query should be

select FourthContent,EleventhContent from  Atten_Table  where ModuleId= 77 and flag=1 and status =1 order by CASE
 WHEN ( Convert(datetime, EleventhContent) IS null ) THEN CONVERT(datetime, FourthContent) 
 WHEN ( CONVERT(datetime, FourthContent) < GetDate() ) THEN CONVERT(datetime, FourthContent)
 WHEN ( Convert(datetime, EleventhContent) < GetDate() ) THEN CONVERT(datetime, EleventhContent)
 ELSE CONVERT(datetime, EleventhContent) END

Also, your last two cases can be combined into one ELSE (e.g. you don't need the last check as your ELSE is the same anyway).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Try the below and let us know the error incase you have:

select FourthContent,EleventhContent from  Atten_Table  where ModuleId= 77 and flag=1 and status =1 
order by CASE
 WHEN ( Convert(datetime, EleventhContent) IS null ) THEN CONVERT(datetime, FourthContent) 
 WHEN ( CONVERT(datetime, FourthContent) < GetDate() ) THEN CONVERT(datetime, FourthContent)
 WHEN ( Convert(datetime, EleventhContent) < GetDate() ) THEN CONVERT(datetime, EleventhContent)
 ELSE CONVERT(datetime, EleventhContent) End

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 9

Hi 

Above query is executed. i got below result. but my request is  completed date is goes to last.

FourthContent             EleventhContent

 8/21/2012                   8/21/2012 ------------->this date is  completed. show i want to display in last.

 8/22/2012                    8/23/2012
 8/28/2012
10/20/2012
12/29/2012

i want this format how can  i do ?

 FourthContent                         EleventhContent
 8/22/2012                                 8/23/2012
 8/28/2012                          
10/20/2012                            
12/29/2012                             

 8/21/2012                                8/21/2012

Answer 10

May be

ORDER BY case when FourthContent < CURRENT_TIMESTAMP OR EleventhContent < CURRENT_TIMESTAMP THEN 2 else 1 END,

FourthContent, EleventhContent

-------------

If these columns are not date (datetime already), you may want to convert them to datetime first.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Hi 

Above query is executed. i got below result. but my request is  completed date is goes to last.

FourthContent             EleventhContent

 8/21/2012                   8/21/2012 ------------->this date is  completed. show i want to display in last.

 8/22/2012                    8/23/2012
 8/28/2012
10/20/2012
12/29/2012

i want this format how can  i do ?

 FourthContent                         EleventhContent
 8/22/2012                                 8/23/2012
 8/28/2012                          
10/20/2012                            
12/29/2012                             

 8/21/2012                                8/21/2012

yes, that is what you should use as per your requirement, above query is just for syntax purpose. You can replace your own logic for sorting as per your need.

regards

joon

Answer 12

Its working. but i got this result.

FourthContent

8/22/2012
10/20/2012
12/29/2012
8/20/2012
8/21/2012
8/21/2012
8/28/2012 ---> This entry is does not completed. this entry is upcomming date . 

Answer 13

In this case remove the second part and only check

WHEN FourthContent < CURRENT_TIMESTAMP then 2 else 1 end, ...

I assumed you wanted to check both of the columns for completion.

You should know your business logic better so you should adjust the idea I showed for your scenario.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 14

select FourthContent,EleventhContent from  Atten_Table  where ModuleId= 77 and flag=1 and status =1 order by CASE
 WHEN ( Convert(datetime, EleventhContent) IS null ) THEN CONVERT(datetime, FourthContent) 
 WHEN ( CONVERT(datetime, FourthContent) < GetDate() ) THEN CONVERT(datetime, FourthContent)
 WHEN ( Convert(datetime, EleventhContent) < GetDate() ) THEN CONVERT(datetime, EleventhContent)
 ELSE CONVERT(datetime, EleventhContent) END

 above  query is  my exact solution. but i want to show the user this format result.  yes i agree with  WHEN FourthContent < CURRENT_TIMESTAMP then 2 else 1 this case only check. then how i do order by......

 for your reference i found one site www.tngf.in\tournament.aspx

 FourthContent                         EleventhContent
 8/22/2012                                 8/23/2012
 8/28/2012                          
10/20/2012                            
12/29/2012                             

 8/21/2012                                8/21/2012

Answer 15

Try:

select FourthContent,EleventhContent from  Atten_Table  where ModuleId= 77 and flag=1 and status =1 order by CASE
 WHEN CONVERT(datetime, FourthContent) < GetDate()  THEN 2 else 1 END, 
 CONVERT(datetime, FourthContent),
 CONVERT(datetime, EleventhContent) 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 16

Its working. but i got this result.

FourthContent

8/22/2012
10/20/2012
12/29/2012
8/20/2012
8/21/2012
8/21/2012
8/28/2012 ---> This entry is does not completed. this entry is upcomming date . 

Answer 17

How did you get this using the latest code? Can you post your query?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 18

select FourthContent,EleventhContent from   where ModuleId= 77 and flag=1 and status =1 order by CASE
 WHEN CONVERT(datetime, FourthContent) < GetDate()  THEN 2 else 1 END, 
 CONVERT(datetime, FourthContent), CONVERT(datetime, EleventhContent) 

Answer 19

Ok, using this query all future dates will be first, then passed dates for FourthContent column. So, this query is OK.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 20

okay. i got result like that.   but  last date come to first

 FourthContent                         EleventhContent
 8/28/2012                          
10/20/2012                            
12/29/2012                             

 8/21/2012                                8/21/2012

8/22/2012                                 8/23/2012 -------------->but this  entry is come to bottom. my main aim is  this entry come to first. my condition is when iith content  is null  to take 4th content is current date and  do order by. if 11 th content have a date mean it ll take current date for 11 th content  and do order by

Answer 21

If so, use this condition for ORDER BY

COALESCE(convert(datetime, EleventhContent), CONVERT(Datetime, FourthContent)) < CURRENT_TIMESTAMP then 2 ELSE 1 end, EleventhContent, FourthContent

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 22

select FourthContent,EleventhContent from  att_report where ModuleId= 77 and flag=1 and status =1order by CASE COALESCE(convert(datetime, EleventhContent), CONVERT(Datetime, FourthContent)) < CURRENT_TIMESTAMP THEN 2 ELSE 1 end, EleventhContent, FourthContent

 getting error for 

Incorrect syntax near '<'.

Answer 23

You're missing WHEN now, e.g.

select FourthContent,EleventhContent from  att_report where ModuleId= 77 and flag=1 and status =1
order by CASE WHEN COALESCE(convert(datetime, EleventhContent), CONVERT(Datetime, FourthContent)) < CURRENT_TIMESTAMP 
THEN 2 ELSE 1 end, EleventhContent, FourthContent

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 24

Sorry i got this  out put

FourthContent EleventhContent

8/24/2012 NULL
8/20/2012 8/21/2012
8/21/2012 8/21/2012

8/21/2012 8/22/2012
10/20/2012 10/20/2012
12/29/2012 12/29/2012
8/22/2012 8/23/2012
8/28/2012 8/28/2012

 i want  below out put

FourthContent	   EleventhContent
8/22/2012	     8/23/2012
8/24/2012        
8/28/2012	     8/28/2012
10/20/2012          10/20/2012
12/29/2012          12/29/2012	
8/20/2012	     8/21/2012
8/21/2012	     8/21/2012
8/21/2012	     8/22/2012

Answer 25

Can you post your input and desired output based on that input? Also, based on the ideas I presented to you so far can you make a minimal effort and use the condition that suits your business needs?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 26

 my input is 

 text box1 = 8/24/2012 

textbox2="empty"

while insert its save to db in Fourth content, Eleventh Content. textbox1 goes to 4th content column, textbox2= 11 th content column.

other case both text box have value store in db. while i retrive the data current date column is fourth content. based on current date i ll order by and i display. its working. when 11 th content hava data mean it ll compare  both date . 11 th content is greater  then 4 th content. so this sequence only 11 th content is activate on current date. 

my expected output is

FourthContent	   EleventhContent
8/22/2012	     8/23/2012
8/24/2012        
8/28/2012	     8/28/2012
10/20/2012          10/20/2012
12/29/2012          12/29/2012	
8/20/2012	     8/21/2012
8/21/2012	     8/21/2012
8/21/2012	     8/22/2012

Today date is 22 , tomorrow date is 23 so this case show in the top. when 23 is current date mean the drop will come to bottom. this my required. please guide me....... 

Answer 27

This condition

order by CASE WHEN COALESCE(convert(datetime, EleventhContent), CONVERT(Datetime, FourthContent)) < CURRENT_TIMESTAMP 
THEN 2 ELSE 1 end

Should work fine and in case we have EleventhContent and it is not null and less that today's date, it goes to the bottom, otherwise to the top.

If it is null, I apply the same logic towards FourthContent.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 28

i got below out put for above your query

FourthContent EleventhContent
10/20/2012 10/20/2012
12/29/2012 12/29/2012
8/22/2012 8/23/2012
8/28/2012 8/28/2012
8/21/2012 8/22/2012
8/24/2012
8/20/2012 8/21/2012
8/21/2012 8/21/2012

please refer my before post. Thank you so much.......... your cooperation.  

Answer 29

I don't know how are you getting this. Are you running the queries in SSMS? The ORDER BY clause I suggested is correct and should put all future dates on top and passed dates on bottom. Also, I hope you do keep ordering by ElventhContent and FourthContent after separating future and passed, so the dates are sorted properly.

Also, what is the type of these columns in the table?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 30

Yes u are correct.....

varchar(max) in the column. i want future date on top  and passed date on bottom. 

Answer 31

It is bad that you're using varchar(max) for the date columns. If possible, I suggest to fix the structure of the table and use datetime (or date in SQL 2008 and up) for the date columns. 

Also, are you running this script above in SQL Server Management Studio?

Your order by should be

order by CASE WHEN COALESCE(convert(datetime, EleventhContent), CONVERT(Datetime, FourthContent)) < CURRENT_TIMESTAMP 

THEN 2 ELSE 1 end, convert(datetime,EleventhContent), convert(datetime,FourthContent)

I don't see a reason why the above condition will not work. It first picks up not null column (either EleventhContent or FourthContent) and then compare it with the current date and if it's passed, it uses 2, if it's a future date, it uses 1, so future dates should be on top and passed on bottom.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 32

Hi 

i can't change my table  structure. This is  a main draw back. thats y i struggle with this... can you tell me how to get future date in order by. as well as passed date. then i want to know how to merge those two query and order by. 

Answer 33

The ORDER BY I posted in the previous reply is exactly what you should use, once again:

select FourthContent,EleventhContent from  Atten_Table  where ModuleId= 77 and flag=1 and status =1
order by CASE WHEN COALESCE(convert(datetime, EleventhContent), 
CONVERT(Datetime, FourthContent)) < CURRENT_TIMESTAMP 

THEN 2 ELSE 1 end, convert(datetime,EleventhContent), convert(datetime,FourthContent)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 34

i had a one doudt.

 first i get data in my condition based and store it one place. again i going to order by current date time order in stored data. Is possible. Is Possible mean  can you give me a example how to store the data.

Answer 35

I am not sure I understand your new question. If your original question is answered, please mark correct solution (s) and start a new thread with a new question providing as much info as possible.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog