Extracting Date from Datetime (Datetype)

Question

Hi all,

I wrote the below sql to extract the date from datetime column but because I am converting it to string the problem rises when i put it in order by clause. I am not getting the dates in the right right order, here is sql I am using,

SELECT

CONVERT(VARCHAR(2), DATEPART(day, Time_))+ '/' +

CONVERT(VARCHAR(2), DATEPART(month, Time_))+ '/' +

CONVERT(VARCHAR(4), DATEPART(year,Time_)),

field1,

field2

FROM

NETBACKUP

GROUP BY

CONVERT(VARCHAR(2), DATEPART(day, Time_))+ '/' +

CONVERT(VARCHAR(2), DATEPART(month, Time_))+ '/' +

CONVERT(VARCHAR(4), DATEPART(year,Time_))

ORDER BY

CONVERT(VARCHAR(2), DATEPART(day, Time_))+ '/' +

CONVERT(VARCHAR(2), DATEPART(month, Time_))+ '/' +

CONVERT(VARCHAR(4), DATEPART(year,Time_))

Can any one please help me out with it?


Regards,

Sameer.

Answer 1

SELECT GetDate() As [datetime]

, DateAdd(dd, DateDiff(dd, 0, GetDate()), 0) As [date_portion_only]

---

George

Answer 2

Hi George,

I am actually extracting it from a datetime column. Time in my code above is database field.

Answer 3

Replace GetDate() with your column.

---

George

Answer 4

 Hi,

It converts the time part to 0z but I me not getting the exact sum in other fields. For example, in my code the sum of field2 one sohuld be around 14 but I m getting 2. The result without converting is shown below,

DateTime                    Field1            Field2

2005-06-04 20:07:09.000 0.03364017419   1

2005-06-04 20:07:10.000 0.02788367588   1

2005-06-04 20:07:11.000 0.02174269501   1

2005-06-04 21:48:12.000 0.00894339289   1

2005-06-04 21:58:03.000 0.15656192600   1

2005-06-04 22:08:33.000 0.03459529951   1

2005-06-04 22:14:06.000 0.03242414724   1

2005-06-04 23:07:10.000 0.00506256427   1

2005-06-04 23:07:11.000 0.03534864913   1

2005-06-04 23:46:08.000 0.00057122111   1

2005-06-04 23:51:51.000 0.00682458933   1

2005-06-04 23:57:02.000 0.00025699567   1

But after converion, insted of getting 12 in field2 i get,

DateTime                                    Field1                    Field2
2005-06-04 00:00:00.000       0.36385533023         2

All I am trying to do is have sum of filed1 one and field2 for the particular date. I guess i should be able to achieve it using group by clause but for some reason it is not working.

Can you please help? (the sql is show above in question)

Answer 5

There is a CONVERT format for dd/mm/ccyy.  Why are you not using CONVERT(varchar(10),Time_,103).

If you want it to order by the datetime correctly, you need to order by the datetime, not the string value.

If you want a sum of field1, then use GROUP BY CONVERT(varchar(10),Time_,103), this will remove the time from the datetime.

Answer 6

 that is the issue. I do not want to gourp it on Time_ bases. I want to gourp it on the derieved Date bases.

Answer 7

I cant order by the Time_ unless i have it in the selest clause the group by clause. Is there any other way of having it in the right order?

Answer 8

SELECT DateAdd(dd, DateDiff(dd, 0, [time]), 0) As [date_only]

, field1

, field2

FROM   NETBACKUP

GROUP

BY DateAdd(dd, DateDiff(dd, 0, [time]), 0)

ORDER

BY [time]

---

George

Answer 9

 It is incorrect sql syntax. You can not order by Time_ unless you have it in select and group by clause. :/

Answer 10

Sorry, another silly mistake:

ORDER 
    BY [date_only]

---

George

Answer 11

ya but thats exactly what i tried when i poted,


Hi,

It converts the time part to 0z but I me not getting the exact sum in other fields. For example, in my code the sum of field2 one sohuld be around 14 but I m getting 2. The result without converting is shown below,

DateTime                    Field1            Field2

2005-06-04 20:07:09.000 0.03364017419   1

2005-06-04 20:07:10.000 0.02788367588   1

2005-06-04 20:07:11.000 0.02174269501   1

2005-06-04 21:48:12.000 0.00894339289   1

2005-06-04 21:58:03.000 0.15656192600   1

2005-06-04 22:08:33.000 0.03459529951   1

2005-06-04 22:14:06.000 0.03242414724   1

2005-06-04 23:07:10.000 0.00506256427   1

2005-06-04 23:07:11.000 0.03534864913   1

2005-06-04 23:46:08.000 0.00057122111   1

2005-06-04 23:51:51.000 0.00682458933   1

2005-06-04 23:57:02.000 0.00025699567   1

But after converion, insted of getting 12 in field2 i get,

DateTime                                    Field1                    Field2
2005-06-04 00:00:00.000       0.36385533023         2

All I am trying to do is have sum of filed1 one and field2 for the particular date. I guess i should be able to achieve it using group by clause but for some reason it is not working.

Can you please help? (the sql is show above in question)

Report post as abusive

:/

Answer 12

Can you post your actual query please? Are you grouping by "field1" as well?

---

George

Answer 13

SELECT

DateAdd(dd, DateDiff(dd, 0,[time] ), 0) as date ,

field1,

field2

FROM

NETBACKUP

GROUP BY

DateAdd(dd, DateDiff(dd, 0, [time]), 0)

ORDER BY

date

No. My query looks exactly like you posted but it does not gives me the desired results which it should coz i dont see anything wrong with it. I dont think we can change it but just dont understand why it does not give me the exact total?

results are posted above.

Answer 14

 its ok mate it just works fine.

Answer 15

That query won't run; field1 and field2 are not in the group by clause or aggregated.
This is why I ask for the actual query.

The result you posted above is correct if you are only grouping by the date_only. If you remove field1 from the select clause or wrap that in an aggregate function too, then you should get the result you're looking for.

---

George

Answer 16

SameerMughal said:

 its ok mate it just works fine.

I should really refresh before posting :p

---

George

Answer 17

Hi all,

I tried using the same(kind of) sql to retrieve records from My sql db but got an error. I am not sure about the error but i guess it is the syntax difference between Ms sql and My sql. Probebly DateDiff is not a valid funcation in My sql. Please have a look at below sql,

select

DateAdd(dd, DateDiff(dd, 0,Time_ ), 0) as date1,

min(field1) as f1,

min(field2) as f2,

min(field3) as f3

FROM table1

WHERE  (ser_server="back1" or ser_server="back2")

group by DateAdd(dd, DateDiff(dd, 0,Time_ ), 0) as date1

all i am looking for is valid syntax to convert datetime to date which would give me the same output as highlighted line gives in MS Ms sql.


Regards,

Sameer.

Answer 18

Hi All,

The output of the derived field DateAdd(dd, DateDiff(dd, 0,Time_ ), 0) as date1  looks like,

Date1
2005-06-04 00:00:00.000

Can any plase tell me that how do I convert this field to just date i.e remove the time fields such that output looks like,


2005-06-04        insted of         2005-06-04 00:00:00.000.


Thanks,

Sameer.

Answer 19

That's a presentation issue that should be handled by your aplication front-end. The only way to achieve it in SQL is to convert it to a character data type, which I doubt you want to do.

---

George

Answer 20

can we compare the datetime fileds with date in the crystal reports? any idea?

e.g   date.table1 == datetime(time fileds static 0z).table2?