SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 date time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/21/2013 :  00:33:57  Show Profile  Reply with Quote


my data is like this

eid------------checktime-----
25516---4/20/2013 12:55:00 PM
26200---4/20/2013 12:46:00 PM

i want this type of data

eid ---------------date---------time
25516---------4/20/2013-----12:55:00 PM
26200---------4/20/2013-----12:46:00 PM




immad uddin ahmed

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/21/2013 :  00:50:35  Show Profile  Reply with Quote
SELECT eid, CAST( checktime as DATE) date, CAST( checktime AS Time) Time
FROM Table

--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/21/2013 :  01:02:43  Show Profile  Reply with Quote
giving me error
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type Time is not a defined system type.

immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/21/2013 :  01:08:56  Show Profile  Reply with Quote
quote:
Originally posted by immad

giving me error
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type Time is not a defined system type.

immad uddin ahmed


The above DATE / TIME types are available from SQL Server 2008 onwards...
Which version you have?

-- MSSQL 2005/2000

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly

--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/21/2013 :  02:30:34  Show Profile  Reply with Quote
sql server 2005


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  02:41:20  Show Profile  Reply with Quote

SELECT eid,DATEADD(dd,DATEDIFF(dd,0,checktime),0) AS [date],CONVERT(varchar(8),checktime,108) AS [time]
FROM table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/21/2013 :  02:49:59  Show Profile  Reply with Quote
thanks

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  02:55:37  Show Profile  Reply with Quote
welcome
i didnt understand the reason for doing this thought.
Ideally separating out date and time should be presentation which can be done in front end. Its always better to keep them together in a datetime field in table preserving its actual data type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/21/2013 :  03:46:56  Show Profile  Reply with Quote
actullay in my attendace project i want time in and time out becouse there is two types of attendance.card attendance and manual attendance.card attendance mean employee come swap his card and time in and time out insert in attend_log table then we make a report of it.2nd is manual attendance mean if employee forget his card then hr enter his timein and time out manally in manaul table.this table show data like this

eid------------checktime-----
25516---4/20/2013 12:55:00 PM
26200---4/20/2013 12:46:00 PM

and attendlog data show like this
eid-----date-------------------------timein--------------------timeout
1---2013-01-01 00:00:00.000---2013-06-19 09:09:00.000---2013-06-19 13:00:00.000


how i can merge them and show in report.so thats why i am breaking date and time



immad uddin ahmed

Edited by - immad on 06/21/2013 03:51:27
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  03:56:59  Show Profile  Reply with Quote
why is your date values in date and timein/timeout fields different? how can a person swipe card on 20130101 and get dates values of 2013-06-19 09:09:00.000 and 2013-06-19 13:00:00.000?

Also attendlog also has date and time stored in same field, so didnt understand why you should split it in manual table alone


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/21/2013 :  04:08:10  Show Profile  Reply with Quote
actullay i am making an updated software .first attendnace project develop in fox pro and now i am making this software in asp.net there is lots of idotic things done in foxpro :) related to this project . now its my task to make this software in dot net .u telling me right now what i do is i insert attend_log data and manual data in same table and show time in and time out.or may be use union and join tables may be its complicated.

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  04:17:00  Show Profile  Reply with Quote
you just need to keep it in current way IMHO. There's absolutely no need to separate out the date and time parts from checktime field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000