Date/Time Question [closed]

Question

I am new to SQL and am having some difficulty manipulating date/time fields using sql. I have two columns: One is date/time formatted (2010-02-16 06:56:00.000) and the other is a text ID. I would like to make a new primary key field (text field) that is a combination of the ID & the date. Ideally I would like this in the following format: ID_dd/mm/yyyy hh:mm:ss (24hr).

I don't seem to see an style option for this time of date-time conversion. Does anyone know of a way to get this data into that format? Not sure if it's possible.

Answer 1

use string concatenation like if you have date and id columns:

SELECT id||CAST(date AS text) from your_table;

Answer 2

In Sql Server use

select 
CONVERT(varchar(6), Id) + '_' + 
CONVERT(varchar(10), your_datetime,103) + ' ' + 
CONVERT(varchar(10), your_datetime, 108)
from your_table

As an aside, creating a primary key based on this field calculated field seems a little odd, especially since you already have a (presumably) unique Id integer field, which for most cases, makes a good primary key. It's certainly not odd to create a normal (not primary) index on this calculated column for querying purposes.