date time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date time

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-06-21 : 00:33:57

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-21 : 00:50:35

SELECT eid, CAST( checktime as DATE) date, CAST( checktime AS Time) Time
FROM Table

--
Chandu

immad
Posting Yak Master

230 Posts

Posted - 2013-06-21 : 01:02:43

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-21 : 01:08:56

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-21 : 02:30:34

sql server 2005

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:41:20

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-06-21 : 02:49:59

thanks

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:55:37

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-21 : 03:46:56

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 03:56:59

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-21 : 04:08:10

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 04:17:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources