Inserting only the Date of GetDate() and the Time of GetDate()? 1

[aspx]

Hi!
How to insert in table only the date-part of GetDate() and the time-part only of GetDate()?
Thanx for any help!

 

[AWithers]

depends what the data type of the destination field is, if it is datetime then you can only have the full date-time, if it is a varchar then you can use convert to format the date in the format you want.

You should be aware that in order to do any calculations on a varchar you need to convert them back to date-time

Andy

 

[aspx]

Unfortunately data type of destination fields is "datetime", not "varchar"...

Because I'll use PHP to insert these data - maybe is better go get current date and curent time with PHP, not by manipulating SQLServer?

But thank you for the help!

 

[MeanGreen]

There are ways to extract Date only and Time only from Getdate(), but MS SQL Server will only support DATETIME and SMALLDATETIME for datatypes. So even if you use your PHP to insert this data, the field within SQL Server will be storing it in DATETIME format. There is no TIME datatype like in Oracle. So, here is sample code for the two extractions in Varchar format:

select convert(varchar(10),getdate(),101) as [date]

select convert(varchar(8),getdate(),108) as [time]


Hope this helps.

 

[aspx]

Hi MeanGreen!
Can you show me please these ways of extracting only Date and Time part of GetDate()! (and inserting it in Datetime-column).
(I know that there is no Time-format and for me Datetime-format is just OK! )

 

[MeanGreen]

You will have to set up some rule for the two conditions:

For Date only, time will always be 12:00:00

select convert(datetime,convert(varchar(10),getdate(),101) + ' 12:00:00') as [date]

For Time only, date will always be '1/1/1950'

select convert(datetime,'1/1/1950 ' + convert(varchar(8),getdate(),108)) as [time]

Insert example:
For your table:
create table test
(idno integer identity(1,1),
dateonly datetime,
timeonly datetime)

insert into test
select convert(datetime,convert(varchar(10),getdate(),101) + ' 12:00:00'),
convert(datetime,'1/1/1950 ' + convert(varchar(8),getdate(),108))

Hope this helps.

 

[aspx]

Extremely useful!
Thanks a lot!!